1 2 3 Previous Next 33 Replies Latest reply: Feb 6, 2012 3:48 PM by 677184 RSS

    Define table type on runtime...Getting Error..

    677184
      My requirement is to
      I am trying to define table type on runtime. i.e. ortf_in_table_tbl{noformat}({noformat}i).field_position_rec.ortf_segment_field_name in below code.

      But when I am executing below code and getting below error:
      DECLARE
         CURSOR field_position_cur (p_table_name VARCHAR2)
         IS
            SELECT xosf.field_name, xosf.starting_position, xosf.field_length
              FROM record_types xort, record_segments xors, segment_fields xosf
             WHERE xort.record_type_id = xors.record_type_id
               AND xors.record_segment_id = xosf.record_segment_id
               AND xosf.table_name = p_table_name;
      
         CURSOR raw_data_cur
         IS
            SELECT *
              FROM raw_data;
      
         TYPE raw_data_typ IS TABLE OF raw_data_cur%ROWTYPE
            INDEX BY BINARY_INTEGER;
      
         TYPE table_typ IS TABLE OF emp%ROWTYPE
            INDEX BY BINARY_INTEGER;
      
         table_tbl      table_typ;
         raw_data_tbl   raw_data_typ;
      BEGIN
         OPEN raw_data_cur;
      
         LOOP
            FETCH raw_data_cur
            BULK COLLECT INTO raw_data_tbl;
      
            EXIT WHEN raw_data_tbl.COUNT = 0;
      
            FOR i IN raw_data_tbl.FIRST .. raw_data_tbl.LAST
            LOOP
               FOR field_position_rec IN field_position_cur ('EMP')
               LOOP
                  table_tbl (i).field_position_rec.field_name :=
                     SUBSTR (raw_data_tbl (i).raw_line_text,
                             field_position_rec.starting_position,
                             field_position_rec.field_length
                            );
                  DBMS_OUTPUT.put_line
                          (   'table_tbl (i).field_position_rec.field_name '
                           || table_tbl (i).field_position_rec.field_name
                          );
               END LOOP;
            END LOOP;
         END LOOP;
      
         CLOSE raw_data_cur;
      
      FORALL i IN table_tbl.FIRST .. table_tbl.LAST
            INSERT INTO emp 
                 VALUES (table_tbl (i)
                        );
         COMMIT;
      
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('OTHERS ' || SQLERRM);
      END;
      
      
      **ORA-06550: line 61, column 52:**
      **PLS-00302: component 'FIELD_POSITION_REC' must be declared**
      **ORA-06550: line 54, column 13:**
      **PL/SQL: Statement ignored**
      Here field_position_cur is giving me column name for the EMP table (i.e. field_position_rec.field_name) and field_position_cur is giving start and length to derive value of the that column
      i.e.
       SUBSTR (raw_data_tbl (i).raw_line_text,
                             field_position_rec.starting_position,
                             field_position_rec.field_length
                            )
      But it is giving me error. We are on 10g database.

      Please suggest the solution to it.

      Edited by: BluShadow on 12-Jan-2012 08:21
      added {noformat}
      {noformat} and other tags to make it readable.  Please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
        • 1. Re: Define table type on runtime...Getting Error..
          damorgan
          Cursor loops, such as the one you are trying to write, have been obsolete in Oracle for more than 12 years.

          Please toss this code and write using BULK COLLECT and FORALL statements.

          Demos here:
          http://www.morganslibrary.org/reference/array_processing.html

          Why fix something obsolete when you can get 50X the performance and write better code?
          • 2. Re: Define table type on runtime...Getting Error..
            rp0428
            The problem with your code, in addition to Mr. Morgan's on-point suggestion, is that you are trying to use static pl/sql to do dynamic operations. That will simply never work.

            The error message, as it usually does, points you to the problem:
            **ORA-06550: line 61, column 52:**
            **PLS-00302: component 'FIELD_POSITION_REC' must be declared**
            **ORA-06550: line 54, column 13:**
            **PL/SQL: Statement ignored**
            
            There is nothing called 'FIELD_POSITION_REC' in the table_tbl type.
            
            Your code at that point must be the line that has:
            
            table_tbl .field_position_rec.field_name := . . .
            
            As far as I can tell from your comment ('field_position_cur is giving me column name for the EMP table ') and the code you are trying to use the construct 'field_position_rec.field_name' to get the column name you want to use for the 'table_tbl' variable so if the value provided by 'field_position_rec.field_name' is 'DEPTNO' you want a result of 'table_tbl.DEPTNO'.
            You can't construct dynamic sql with that approach. You need to follow Mr. Morgan's advice and make your code modular. If you do use dynamic constructs create a separate block (or procedure) of code to construct the query and then use bulk queries and collections to do the actual processing.
            • 3. Re: Define table type on runtime...Getting Error..
              677184
              Thanks a lot first of all to both of you..


              @Mr. Morgan - I have seen your website link, but there are lots of examples, but sorry to say nothing is matching to my requirement. Can you please give me some rough example which you are trying to refer in my case, not wasting your much time?
              @rp0428 - Sorry. I didnt get your name. So calling you with profile name. Your understanding with my use of field_position_cur is correct. That is what I am trying to achieve.
              But I didn't understand your comments "You can't construct dynamic sql with that approach. You need to follow Mr. Morgan's advice and make your code modular. If you do use dynamic constructs create a separate block (or procedure) of code to construct the query and then use bulk queries and collections to do the actual processing."

              Can you please provide me some sample examples to my requirement, that would be definately helpful to understand easily?

              Edited by: piyushgupta on Jan 11, 2012 5:54 PM

              Edited by: piyushgupta on Jan 11, 2012 6:25 PM
              • 4. Re: Define table type on runtime...Getting Error..
                Billy~Verreynne
                piyushgupta wrote:
                My requirement is to
                I am trying to define table type on runtime.
                Please explain - it is not clear from the code what you are attempting.

                Also note that PL/SQL is strong typed language. This mean that you cannot define a variable using a weak type and at runtime change that into a formal data type.

                In Oracle, the abstract AnyData data type can be used for unknown data types - and methods exist that allows different data types to be stored and retrieved as AnyData types. Of course, there are overheads involved in using an abstract data type (implemented as an object class) like this.

                Another method is to use DBMS_SQL to create dynamic code (SQL or PL/SQL) - and perform dynamic binding (the bind determines the data type of the bind variable in the code). If the dynamic code is a SQL select, the describe interface enables you to determine the content (columns and data types) of the SQL projection.

                The final method that comes to mind is using a RTTI (Run Time Type Information) interface - something that is also supported by Oracle (and basically the backend implementation of answering a describe interface call). This is however a bit more complex and quite specialised and needs a very unique problem to justify it being used.
                • 5. Re: Define table type on runtime...Getting Error..
                  rp0428
                  There are tons of links for examples using dynamic sql.

                  Start with the Oracle docs: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#CHDIEFCJ

                  Chapter 7, Using dynamic SQL explains it all and includes examples.
                  • 6. Re: Define table type on runtime...Getting Error..
                    677184
                    Actually, I am looking for example which match to my requirement where you create table type on run time. I have used dynamic sql lot before, but in order to handle my situation, it looks like need different approach to write dynamic sql.
                    • 7. Re: Define table type on runtime...Getting Error..
                      sb92075
                      piyushgupta wrote:
                      My requirement is to
                      I am trying to define table type on runtime.
                      a REALLY, REALLY, Really, really bad idea.

                      dynamic tables can only ever be accessed by dynamic SQL.

                      such an approach scales as well as my goat can fly.

                      application tables should be STATIC between software version releases.

                      This "design" is seriously FLAWED!, in my opinion.
                      • 8. Re: Define table type on runtime...Getting Error..
                        677184
                        I am sorry, but sample example "*MATCHING TO MY REQUIREMENT*" would be really great.
                        • 9. Re: Define table type on runtime...Getting Error..
                          677184
                          Sure. Basically 'field_position_cur is giving me column name for the EMP table ' and the code trying to construct 'field_position_rec.field_name' to get the column name to use for the 'table_tbl' variable so if the value provided by 'field_position_rec.field_name' is 'DEPTNO' I want a result of 'table_tbl.DEPTNO'." It will be finally used to insert values into EMP table.

                          Let me know if you need more explaination.
                          • 10. Re: Define table type on runtime...Getting Error..
                            Billy~Verreynne
                            piyushgupta wrote:
                            Sure. Basically 'field_position_cur is giving me column name for the EMP table ' and the code trying to construct 'field_position_rec.field_name' to get the column name to use for the 'table_tbl' variable so if the value provided by 'field_position_rec.field_name' is 'DEPTNO' I want a result of 'table_tbl.DEPTNO'." It will be finally used to insert values into EMP table.
                            This type of dynamic SQL requires the use of the <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm">+DBMS_SQL+</a> package.

                            However, I'll add my voice to +sb92075+ - it is a *REALLY*, REALLY, Really, really bad idea.

                            Dealing with a data model dynamically - that is very much an exception and almost never make sense.
                            • 11. Re: Define table type on runtime...Getting Error..
                              rp0428
                              Aw common, Billy! Don't hold back on us. Tell us how you REALLY, REALLY, REALLY feel!
                              • 12. Re: Define table type on runtime...Getting Error..
                                677184
                                Thanks everyone, but now with all these suggestions it become more complicated for me to understand.

                                I got suggestions for BULK COLLECT AND FOR ALL, DBMS_SQL, ANYDATA to meet my requirement.

                                But now could you please summarize considering all pros and cons and let me know what would be the best approach to deal with this kind of design with sample example or code.

                                @rp0428 - As you suggested initally " If you do use dynamic constructs create a separate block (or procedure) of code to construct the query and then use bulk queries and collections to do the actual processing." For this can you please provide sample example or code.

                                Edited by: piyushgupta on Jan 12, 2012 6:18 PM
                                • 13. Re: Define table type on runtime...Getting Error..
                                  sb92075
                                  piyushgupta wrote:
                                  Thanks everyone, but now with all these suggestions it become more complicated for me to understand.

                                  I got suggestions for BULK COLLECT AND FOR ALL, DBMS_SQL, ANYDATA to meet my requirement.

                                  But now could you please summarize considering all pros and cons and let me know what would be the best approach to deal with this kind of design with sample example or code.

                                  @rp0428 - As you suggested initally " If you do use dynamic constructs create a separate block (or procedure) of code to construct the query and then use bulk queries and collections to do the actual processing." For this can you please provide sample example or code.

                                  Edited by: piyushgupta on Jan 12, 2012 6:18 PM
                                  What data is the input? (post CREATE TABLE statement(s))
                                  What requirements need to be met?
                                  What results are desired or expected?
                                  • 14. Re: Define table type on runtime...Getting Error..
                                    677184
                                    Plese look at my sample code posted initially in this thread.

                                    What data is the input? (post CREATE TABLE statement(s))
                                    --Cursor field_position_cur is input. It is having column name of EMP table and its position length.

                                    What requirements need to be met?
                                    --Want to fetch column name from Cursor field_position_cur and its respective value from cursor raw_data_cur and finally want to insert in EMP table.

                                    So in nut shell,

                                    'field_position_cur is giving me column name for the EMP table ' and the code trying to construct 'field_position_rec.field_name' to get the column name to use for the 'table_tbl' variable so if the value provided by 'field_position_rec.field_name' is 'DEPTNO' I want a result of 'table_tbl.DEPTNO'." It will be finally used to insert values into EMP table.

                                    It would be great if you provide toss my sample code to show me the example how to do it.

                                    Thanks for your concern!!!
                                    1 2 3 Previous Next