This discussion is archived
7 Replies Latest reply: Jun 10, 2013 11:15 PM by Tarun.Oracle RSS

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

Tarun.Oracle Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
  • 7. Re: Dynamically assign values of any column of a row type variable
    Tarun.Oracle Newbie
    Currently Being Moderated

    Thanks for confirming.

    Tarun

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points