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

Pages

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

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

2 comments:

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

    ReplyDelete
  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,
    Preethi.

    ReplyDelete