1 2 Previous Next 19 Replies Latest reply: Jul 24, 2012 6:41 AM by chris227 RSS

    Type Inspection

    Ric79
      Hi all,
      let us suppose
      CREATE OR REPLACE PACKAGE MYPACKAGE
      AS
         TYPE TAB_MYTABLE_TYP IS TABLE OF MYTABLE%ROWTYPE
            INDEX BY BINARY_INTEGER;
      
         MYTABLE   TAB_MYTABLE_TYP;
      
      ...
      Is there, in MYPACKAGE Body, a snippet for describing TAB_MYTABLE_TYP columns? Something like the USER_OBJECTS_ATTRS which is used for standrd TYPE

      Riccardo
        • 1. Re: Type Inspection
          Venkadesh Raja
          *removed

          Edited by: Venkadesh on Jul 23, 2012 6:27 AM
          • 2. Re: Type Inspection
            riedelme
            Ric79 wrote:
            Hi all,
            let us suppose
            CREATE OR REPLACE PACKAGE MYPACKAGE
            AS
            TYPE TAB_MYTABLE_TYP IS TABLE OF MYTABLE%ROWTYPE
            INDEX BY BINARY_INTEGER;
            
            MYTABLE   TAB_MYTABLE_TYP;
            
            ...
            Is there, in MYPACKAGE Body, a snippet for describing TAB_MYTABLE_TYP columns? Something like the USER_OBJECTS_ATTRS which is used for standrd TYPE

            Riccardo
            No :(
            • 3. Re: Type Inspection
              APC
              No. You would use the SQL*Plus DESCRIBE command or select the column data from ALL_TAB_COLUMNS where table_name = 'MYTABLE';

              I suspect where you want to go with this is someform of automated population of the nested table. If so I'm afraid you're out of luck. PL/SQL doesn't really do Java stype reflection or introspection (although sometimes it can be faked with a lot of gnarly dynamic SQL).

              Cheers, APC

              Edited by: APC on Jul 23, 2012 1:20 PM
              • 4. Re: Type Inspection
                Boneist
                Ric79 wrote:
                Hi all,
                let us suppose
                CREATE OR REPLACE PACKAGE MYPACKAGE
                AS
                TYPE TAB_MYTABLE_TYP IS TABLE OF MYTABLE%ROWTYPE
                INDEX BY BINARY_INTEGER;
                
                MYTABLE   TAB_MYTABLE_TYP;
                
                ...
                Is there, in MYPACKAGE Body, a snippet for describing TAB_MYTABLE_TYP columns? Something like the USER_OBJECTS_ATTRS which is used for standrd TYPE

                Riccardo
                No, but since you defined the type in PLSQL, you could always write a proc that returned the necessary info yourself.
                • 5. Re: Type Inspection
                  Ric79
                  You are right.

                  I want to dbms_output the PLSQL Table content.

                  There are several packages and I'm wondering if can factorize the code.

                  Riccardo
                  • 6. Re: Type Inspection
                    rp0428
                    >
                    I want to dbms_output the PLSQL Table content
                    >
                    You can use DBMS_SQL package to describe the columns of a cursor.

                    So you would

                    1. Create a query that uses the TYPE instance you are interested in
                    2. Create a cursor on the query
                    3. Use DBMS_SQL to describe the columns in the cursor

                    See these two threads for examples. The reply Posted: Jun 9, 2012 12:16 PM at
                    Re: Question on dynamic sql

                    And the reply Posted: Jun 7, 2012 8:32 AM at
                    Re: Dynamic Select

                    And the DBMS_SQL package at
                    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i997453
                    • 7. Re: Type Inspection
                      Solomon Yakobson
                      rp0428 wrote:
                      You can use DBMS_SQL package to describe the columns of a cursor.

                      So you would

                      1. Create a query that uses the TYPE instance you are interested in
                      2. Create a cursor on the query
                      3. Use DBMS_SQL to describe the columns in the cursor
                      Can you show me how would you create a query that uses <font color=red>PL/SQL record</font> TYPE instance?

                      SY.
                      • 8. Re: Type Inspection
                        rp0428
                        OP is using %ROWTYPE and this cursor is the equivalent of that
                        DECLARE
                          CURSOR c1 IS (SELECT * FROM emp);
                          TYPE typ_tbl IS TABLE OF c1%rowtype;
                          v typ_tbl;
                        BEGIN
                          OPEN c1;
                          LOOP                                                 --Loop added
                            FETCH c1 BULK COLLECT INTO v LIMIT 3; -- process 3 records at a time
                                -- process the first 3 max records
                               DBMS_OUTPUT.PUT_LINE('Processing ' || v.COUNT || ' records.');
                                FOR i IN v.first..v.last LOOP
                                    DBMS_OUTPUT.PUT_LINE(v(i).empno);
                                END LOOP;  
                            EXIT WHEN c1%NOTFOUND; 
                          END LOOP;
                          DBMS_OUTPUT.PUT_LINE('All done');
                        END;
                        OP can use that cursor in DBMS_SQL and describe the columns.
                        • 9. Re: Type Inspection
                          Solomon Yakobson
                          I understand example you posted. What I don't understand is what your example has to do with getting list of PL/SQL record/PL/SQL cursor column names and datatypes you said can be obtained from DBMS_SQL.DESCRIBE_COLUMNS.

                          SY.
                          • 10. Re: Type Inspection
                            Solomon Yakobson
                            Are you referring to 11g feature DBMS_SQL.TO_CURSOR_NUMBER? If so, AFAIK it only forks with REF CURSORs.

                            SY.
                            • 11. Re: Type Inspection
                              rp0428
                              Actually I was thinking about the example you gave for pipelined functions where Oracle creates the hidden types
                              Re: Pipe line function
                              CREATE OR REPLACE package SCOTT.pkg1
                                    as
                                      type emp_table_type
                                        is
                                          table of emp%rowtype;
                                      function get_emp(
                                                       p_deptno number
                                                      )
                                        return emp_table_type
                                        pipelined;
                                end;
                              /
                              The pipelined function causes the hidden types to be created and their attributes can then be gotten from the dictionary.
                              • 12. Re: Type Inspection
                                Solomon Yakobson
                                rp0428 wrote:
                                Actually I was thinking about the example you gave for pipelined functions where Oracle creates the hidden types
                                That would work, and solution doesn't need to search for hidden types, especially that in 11g they are not visible in DBA_TYPES and require select from data dictionary tables sys.type$ and sys.attribute$. You need to:

                                a) create package pipelined function
                                b) use DBMS_SQL.DESCRIBE_COLUMNS on SELECT * FROM TABLE(pipelined_function_name)

                                SY.
                                • 13. Re: Type Inspection
                                  Ric79
                                  Thanks!
                                  It worked!

                                  Riccardo
                                  • 14. Re: Type Inspection
                                    chris227
                                    Hi,

                                    i followed this thread and i wonder how the goal would be achieved without doing a select from the table defining the underlying rowtype of the collection.
                                    With doing it, it wouldnt be so difficult, but i wouldnt also be no real reflection on the collection any more, since you cant derive the select form it, or can you?
                                    May you give us an examplary extract of the code you finally used?
                                    I dont even see how it is possible to relate the types created in SY example in the other thread to the specific collection type.

                                    Thanks and regards
                                    1 2 Previous Next