6 Replies Latest reply: Apr 10, 2013 3:07 AM by EV259 RSS

    %Rowtype

    EV259
      Hi All,

      I have declared a variable using %ROWTYPE in a procedure. I would like to know the current datatype and length of a column of the variable in the procedure.

      CREATE OR REPLACE PROCEDURE process_employee(empno_in IN emp.empno%TYPE)
      IS
      l_employee emp%ROWTYPE;
      L_LEN NUMBER;
      BEGIN
      SELECT data_length
      INTO L_LEN
      FROM user_tab_cols
      WHERE table_name='EMP'
      AND column_name ='ENAME';
      DBMS_OUTPUT.PUT_LINE('LENGTH: '||L_LEN);
      END;

      In the above code, the select stmt is giving me the length of a particular column from the data dictionary view. But I would like to know the length of variable
      l_employee.ename. Please help me.

      Thanks
        • 1. Re: %Rowtype
          Mahir M. Quluzade
          Hi,

          Do you want like this:
          CREATE OR REPLACE
          PROCEDURE process_employee(
              empno_in IN emp.empno%TYPE)
          IS
            l_employee emp%ROWTYPE;
            L_LEN NUMBER;
          BEGIN
            SELECT * INTO l_employee FROM emp WHERE empno = empno_in;
            dbms_output.put_line('LENGTH: '||LENGTH(l_employee.ename));
          END;
          /
          
          SELECT * FROM emp WHERE empno=7499;
          
          BEGIN
            process_employee(7499);
          END;
          Regards
          Mahir M. Quluzade
          • 2. Re: %Rowtype
            ShankarViji
            Hi User,
            /* Formatted on 2013/04/10 12:58 (Formatter Plus v4.7.0) */
            CREATE OR REPLACE PROCEDURE process_employee (empno_in IN EMP.empno%TYPE)
            IS
               l_employee   EMP%ROWTYPE;
               l_len        NUMBER;
            BEGIN
               SELECT data_length
                 INTO l_len
                 FROM USER_TAB_COLS
                WHERE table_name = 'EMP' AND column_name = 'ENAME';
            
               SELECT *
                 INTO l_employee
                 FROM EMP
                WHERE empno = empno_in;
            
               DBMS_OUTPUT.PUT_LINE ('Column Length: ' || l_len);
               DBMS_OUTPUT.PUT_LINE (' Value Length: ' || LENGTH (l_employee.ename));
            END;
            Output :
            SQL> set serveroutput on
            SQL> exec process_employee (8020)
            Column Length: 10
            Value Length: 5
            Thanks,
            Shankar M

            Edited by: Shankar Viji on Apr 10, 2013 12:26 AM

            Edited by: Shankar Viji on Apr 10, 2013 12:28 AM
            • 3. Re: %Rowtype
              EV259
              Thanks for the reply.

              Based on this statement "When this procedure is compiled, PL/SQL looks up the structure of the emp table and defines a record that has a field for each column in the table, with the same name and datatype.If the database administrator changes the maximum length of the last_name column to 200, for instance, this procedure’s status will be changed to INVALID".
              I would like to know the datatype and the length of the field of that record. Would you please help me.

              Thanks
              • 4. Re: %Rowtype
                EV259
                I have tried this. It will give me the datatype and its length from the data dictionary view.

                I have compiled the procedure first, the record will get created. If I changed the table structure, and I would like to know the datatype and the length of the field in that record.
                • 5. Re: %Rowtype
                  BluShadow
                  968217 wrote:
                  I have tried this. It will give me the datatype and its length from the data dictionary view.

                  I have compiled the procedure first, the record will get created. If I changed the table structure, and I would like to know the datatype and the length of the field in that record.
                  Well, querying the data dictionary will tell you that.
                  If the table structure is changed, the procedure will be marked as 'INVALID' and then automatically compiled internally again, the next time it is used, and assuming it compiles ok, it will just continue to execute with the new definition as it will then be a 'VALID' procedure.

                  The data dictionary is the place to look if you want to know details of database objects in your run-time code.
                  • 6. Re: %Rowtype
                    EV259
                    BluShadow wrote:
                    968217 wrote:
                    I have tried this. It will give me the datatype and its length from the data dictionary view.

                    I have compiled the procedure first, the record will get created. If I changed the table structure, and I would like to know the datatype and the length of the field in that record.
                    Well, querying the data dictionary will tell you that.
                    If the table structure is changed, the procedure will be marked as 'INVALID' and then automatically compiled internally again, the next time it is used, and assuming it compiles ok, it will just continue to execute with the new definition as it will then be a 'VALID' procedure.

                    The data dictionary is the place to look if you want to know details of database objects in your run-time code.
                    Thanks for the reply Blushadow. I got you. Data dictionary is the only place where we can see the details of database objects even in run-time.