%TYPE and %ROWTYPE work with single value and one complete record respectively
How do we create our own composite data type, with our own specified number of values to hold?
Let us consider a table of about 20 columns
We need to work with only seven of those columns
If we use %ROWTYPE, we get all 20 values unnecessarily
If we use seven %TYPE declarations it will be bit clumsy
A better way to solve this problem is by defining our own data type, which can hold seven values
PL/SQL Record Type Variable Example
DECLARE
TYPE myrec_type IS RECORD (
eno emp.empno%TYPE,
NAME emp.ename%TYPE,
esal emp.sal%TYPE
);
emp_record myrec_type;
BEGIN
SELECT empno, ename, sal
INTO emp_record.eno, emp_record.NAME, emp_record.esal
FROM emp
WHERE empno = 7839;
DBMS_OUTPUT.put_line('Empno :' || emp_record.eno);
DBMS_OUTPUT.put_line('Ename :' || emp_record.NAME);
DBMS_OUTPUT.put_line('Salary :' || emp_record.esal);
END;
Hello There,
ReplyDeleteSeems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on PL/SQL Record Type Variable Example
I have DMLStatements which contains some polish characters for POLAND country.
When I am executing that DML through the QL Developer I am able to see my Polishcharacters.
but when we gave the release through the SQLPLUS those characters are replacing with some special characters
like ? (Question Mark)
Data type of the column is : NCLOB
NLS_CHARACTERSET : AL32UTF8
NLS_NCHAR_CHARACTERSET : AL16UTF16
Could you please help to resolve this?
THANK YOU!! This saved my butt today, I’m immensely grateful.
Kind Regards,
Renina
Hello There,
ReplyDeleteGreat piece on PL/SQL Record Type Variable Example, I’m a fan of the ‘flowery’ style Looking forward to more long form articles ??
Please help with the below query.
Requirement: To track DML changes on multiple tables and to populate its consolidated records in one master table by having triggers on the MLOG tables.
Action : I have created triggers on MLOG tables, few of them works and few are failed.
Query: Need suggestion, Is it advisable to have triggers on MLOG$_TABLES (Materialized view log tables ), If yes , Is there any restriction to have so.
Please keep providing such valuable information.
Many Thanks,
Preethi.
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete