Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Wednesday, 6 April 2016

PL/SQL Record Type Variable Example

%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

   TYPE myrec_type IS RECORD (
      eno    emp.empno%TYPE,
      NAME   emp.ename%TYPE,
      esal   emp.sal%TYPE

   emp_record   myrec_type;
   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);
Share this article :


  1. Hello There,

    Seems 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

    Could you please help to resolve this?

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Kind Regards,

  2. Hello There,

    Great 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,

  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  4. Oracle 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 for Never Before Offers and Discount Coupon Codes.