7 Replies Latest reply: Jun 11, 2013 1:15 AM by Tarun.Oracle RSS

    {code}Dynamically assign values of any column of a row type variable

    Tarun.Oracle
      Hi is need to dynamically assign value of each column of a row type variable:
      The example is like that:

      Table "Student_list" is as follows:
      ---------
      COL1 VARCHAR2(50),
      COL2 VARCHAR2(50),
      COL3 VARCHAR2(50)


      Procedure
      ---------------
      Declare
          vROW    Student_list%rowtype;
          cVALUE  VARCHAR2(50);
      Begin
      
          For i in (select * from Old_Student) loop
               For  Z in 1..3 loop
                      cVALUE := MY_FUNCTION(Z);
                      COPY(cVALUE,'vROW.COL'||Z);
               end loop;
          INSERT INTO Student_list VALUES vROW;
          end loop;
          COMMIT;
      end;
      But the COPY(cVALUE,'vROW.COL'||Z); failed as because it is not global variable;
      Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
      Any working coding will be helpful for me.

      Regards,
      Tarun

      Edited by: Tarun.Oracle on Nov 12, 2012 10:54 AM

      Edited by: Tarun.Oracle on Nov 12, 2012 10:55 AM
        • 1. Re: Dynamically assign values of any column of a row type variable
          AlbertoFaenza
          Hi Tarun,

          When you put some code please enclose it between two lines starting with {noformat}
          {noformat}
          i.e.:
          {noformat}
          {noformat}
          SELECT ...
          {noformat}
          {noformat}
          
          Regarding your question. Is COPY a procedure that you have defined in your code? It is not a standard PL/SQL procedure.
          
          You cannot build dynamically a variable name like you want to do.
          You can execute dynamic SQL code with <a href="http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems017.htm">EXECUTE IMMEDIATE statement</a> or your can loop through columns with <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm">DBMS_SQL</a>.
          
          But I don't think you can do it with records and even in that case it looks quite complicated.
          
          Regards.
          Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Dynamically assign values of any column of a row type variable
            sb92075
            Handle:     Tarun.Oracle
            Status Level:     Newbie (10)
            Registered:     Jul 9, 2008
            Total Posts:     39
            Total Questions:     7 (5 unresolved)

            why so many unanswered questions?


            Tarun,
            please push back from the keyboard & reflect upon reality.

            the SQL language is separate & distinct from the PL/SQL language; which are processed by entirely different " engines".

            You can NOT freely intermix constructs from each of them into a single statement.
            The SQL engine knows NOTHING about PL/SQL datatypes or entities.
            • 3. Re: Dynamically assign values of any column of a row type variable
              Tarun.Oracle
              Ok, I shall take care the
               section in next post.
              
              The "COPY" actually a Form6i procedure.
              
              Is there any way to dynamically assign value against a ROW,COLUMN of a PL/SQL table or to some other object where the value of ROW,COLUMN should be dynamic and increase according to a LOOP ?
              
              Regards,
              Tarun                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: Dynamically assign values of any column of a row type variable
                sb92075
                Tarun.Oracle wrote:
                Ok, I shall take care the
                 section in next post.
                
                The "COPY" actually a Form6i procedure.
                
                Is there any way to dynamically assign value against a ROW,COLUMN of a PL/SQL table or to some other object where the value of ROW,COLUMN should be dynamic and increase according to a LOOP ?
                no
                • 5. Re: Dynamically assign values of any column of a row type variable
                  Tarun.Oracle
                  Thanks for pointing out. I have "Mark as answered" all the resolved question what i overlooked previously.

                  But your answer
                  no
                  keep another question unanswered.

                  Any one else? Any other way?

                  Regards,

                  Tarun
                  • 6. Re: Dynamically assign values of any column of a row type variable
                    AlbertoFaenza
                    Tarun.Oracle wrote:

                    Any one else? Any other way?
                    Based on the code you posted, which does not make sense to me, this will have the same effect:
                    Begin
                        INSERT INTO Student_list (col1, col2, col3)
                           SELECT MY_FUNCTION(1), MY_FUNCTION(2), MY_FUNCTION(3)
                             FROM Old_Student;
                        COMMIT;
                    end;
                    As confirmed by sb92075 you cannot dynamically assign variables in the way you want.

                    Regards.
                    Al

                    Edited by: Alberto Faenza on Nov 14, 2012 10:02 AM