8 Replies Latest reply: Feb 26, 2014 2:15 PM by Solomon Yakobson RSS

    PLSQL getting value of property in record dynamically

    ef2019c7-080c-4475-9cf4-2cf1b1057a41

      Hi,

       

      First of all I am really rusty on Oracle PLSQL, and I have seen several folks say this cannot be done and others that say it can, and I am just not able to make it happen. Any help would be greatly appreciated.

      I am trying to read the value of a column in a record type dynamically.

      I have a message with tokens and I need to replace the tokens with value from the record set.

      So the message looks like: [status] by [agent_name]

      I have another place where I am parsing out the tokens.

      In java script I know this can be accomplished with: (Will run in Console)

      ##################################################################

      var record = {

          status : "Open",

          agent_name : "John"

      };

       

      var record2 = {

          status : "Close",

          agent_name : "Joe"

      };

      var records = [record, record2];

       

      var token1 = "status";

      var token2 = "agent_name";

       

      for( var i=0; i<records.length; i++){

          console.log(records[i][token1] + " by " + records[i][token2]);

      }

       

      Results : Open by John

                    Close by Joe

      I want to do the same thing in PLSQL

       

      Here is my test PLSQL:

      ==================

       

         SET SERVEROUTPUT ON;

         declare

       

        TYPE my_record is RECORD

          (

            status         VARCHAR2(30),

            agent_name     varchar2(30)

          ); 

       

        TYPE my_record_array IS VARRAY(6) OF my_record;  

        v_records            my_record_array := my_record_array();

        v_current_rec        my_record;

        v_current_rec2        my_record;

        v_token               varchar2(50):= 'agent_name';

        v_token2              varchar2(50):= 'status';

       

      begin

       

        v_current_rec.status := 'Open';

        v_current_rec.agent_name := 'John';

        v_records.extend;

        v_records(1) := v_current_rec;

       

        v_current_rec2.status := 'Close';

        v_current_rec2.agent_name := 'Ron';

        v_records.extend;

        v_records(2) := v_current_rec2;

       

        FOR i IN 1..v_records.COUNT LOOP

            --Hard coded

            DBMS_OUTPUT.PUT_LINE(v_records(i).status ||  ' by ' || v_records(i).agent_name);

       

            --Substitution vars entering v_records(i).status and v_records(i).agent_name for the prompts.

            --How to do this without user interaction.

            DBMS_OUTPUT.PUT_LINE(&status ||  ' by ' || &agent_name);

       

            --Dynamically that doesn't work. How would this be accomplished

            DBMS_OUTPUT.PUT_LINE(v_records(i).v_token ||  ' by ' || v_records(i).v_token2);

        END LOOP;

      END;

      I tried using substitution variables, and that will work if I use:

       

      ########################################################################

       

      I tried using substitution variables, and that will work if I use:

      DBMS_OUTPUT.PUT_LINE(&agent_name) and entering v_records(i).agent_name when prompted. How do I accomplish this on the fly?

       

       

      Thanks.

        • 1. Re: PLSQL getting value of property in record dynamically
          Partha Sarathy S

          You can do it like this. Instead of a block you can use a procedure to pass your required values as parameters like this.

           

          -- create object type

          CREATE OR REPLACE TYPE typ_obj_status IS OBJECT(STATUS VARCHAR2(50),

                                                AGENT_NAME VARCHAR2(50));

            

          -- create object type nested table                                 

          CREATE OR REPLACE TYPE typ_nt_obj_status IS TABLE OF typ_obj_status;

           

          -- Pass the nested table type as parameter to a procedure

           

           

          CREATE OR REPLACE PROCEDURE sp_test_proc(p_status_agent typ_nt_obj_status)

          AS

          BEGIN

            FOR I IN 1..P_STATUS_AGENT.COUNT

            LOOP

              DBMS_OUTPUT.PUT_LINE(P_STATUS_AGENT(i).status||' by '|| P_STATUS_AGENT(i).agent_name);

            END LOOP;

          END sp_test_proc;

          /

           

          -- Invoke the procedure.

           

          -- For passing more than one value as parameter

          SQL> EXEC SP_TEST_PROC(TYP_NT_OBJ_STATUS(typ_obj_status('OPEN','AGENCY'),TYP_OBJ_STATUS('CLOSED','AGENCY2')));

           

          OPEN by AGENCY

          CLOSED by AGENCY2

           

           

           

          PL/SQL procedure successfully completed.

           

           

           

          -- For passing  one value as parameter

          SQL> EXEC SP_TEST_PROC(TYP_NT_OBJ_STATUS(typ_obj_status('OPEN','AGENCY')));


           

           

          OPEN by AGENCY


           

           

          PL/SQL procedure successfully completed.

           

           

          SQL>

          • 2. Re: PLSQL getting value of property in record dynamically
            Solomon Yakobson

            What you are trying to do requires dynamic SQL. But then it will bring another issue - you can only pass SQL type bind variables to dynamic SQL while record is PL/SQL type. One way of resolving it would be dynamic SQL + package:

             

            SQL> create or replace
              2    package pkg1
              3    is
              4    TYPE my_record is RECORD
              5      (
              6        status         VARCHAR2(30),
              7        agent_name     varchar2(30)
              8      );
              9    TYPE my_record_array IS VARRAY(6) OF my_record;
            10    g_records            my_record_array := my_record_array();
            11  end;
            12  /

            Package created.

            SQL> create or replace
              2    procedure p1(
              3                 p_token1 varchar2,
              4                 p_token2 varchar2
              5                )
              6    is
              7    v_current_rec        pkg1.my_record;
              8    v_current_rec2       pkg1.my_record;
              9  begin
            10    v_current_rec.status := 'Open';
            11    v_current_rec.agent_name := 'John';
            12    pkg1.g_records := pkg1.my_record_array();
            13    pkg1.g_records.extend;
            14    pkg1.g_records(1) := v_current_rec;
            15    v_current_rec2.status := 'Close';
            16    v_current_rec2.agent_name := 'Ron';
            17    pkg1.g_records.extend;
            18    pkg1.g_records(2) := v_current_rec2;
            19    FOR i IN 1..pkg1.g_records.COUNT LOOP
            20        EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(pkg1.g_records(:i).' || p_token1 ||
            21                          ' || '' by '' || pkg1.g_records(:i).' || p_token2 || '); END;'
            22          USING i;
            23    END LOOP;
            24  END;
            25  /

            Procedure created.

            SQL> exec p1('agent_name','status')
            John by Open
            Ron by Close

            PL/SQL procedure successfully completed.

            SQL> exec p1('status','agent_name')
            Open by John
            Close by Ron

            PL/SQL procedure successfully completed.

            SQL>

             

            SY.

            • 3. Re: PLSQL getting value of property in record dynamically
              ef2019c7-080c-4475-9cf4-2cf1b1057a41

              Thanks for the suggestion Parth272025.

               

              But, the call to the field is still hard coded in the DBMS statement, I want it to be dynamic.

              • 4. Re: PLSQL getting value of property in record dynamically
                Partha Sarathy S

                But you have only two fields in the type you created. . How do you want it to be dynamic then?

                • 5. Re: PLSQL getting value of property in record dynamically
                  ef2019c7-080c-4475-9cf4-2cf1b1057a41

                  Hi Solomon,

                   

                   

                  thanks for your suggestion.

                   

                   

                  Could you let me know what is wrong in below, I replaced below EXECUTE IMMEDIATE with the one you had in code.

                   

                   

                  EXECUTE IMMEDIATE 'select :v_records(:i).' || v_token2 || ' from dual'

                      into v_value USING i, v_records ;

                   

                  error:

                   

                   

                  PLS-00457: expressions have to be of SQL types

                   

                   

                  Thanks.

                  • 6. Re: PLSQL getting value of property in record dynamically
                    Solomon Yakobson

                    ef2019c7-080c-4475-9cf4-2cf1b1057a41 wrote:

                     

                    PLS-00457: expressions have to be of SQL types

                    Well, that's exactly what I said in my reply: "But then it will bring another issue - you can only pass SQL type bind variables to dynamic SQL while record is PL/SQL type". So you have 2 choices - stick to PL/SQL types and use package or create SQL types for nested table and object type instead of record type (as Parth272025 showed). Then:

                     

                    SQL> CREATE OR REPLACE
                      2    TYPE my_object IS OBJECT(
                      3                             status         VARCHAR2(30),
                      4                             agent_name     varchar2(30)
                      5                            )
                      6  /

                    Type created.

                    SQL> CREATE OR REPLACE
                      2    TYPE my_object_array
                      3      IS TABLE OF my_object
                      4  /

                    Type created.

                    SQL> create or replace
                      2    procedure p1(
                      3                 p_token1 varchar2,
                      4                 p_token2 varchar2
                      5                )
                      6    is
                      7    v_object  my_object;
                      8    v_objects my_object_array := my_object_array();
                      9  begin
                    10    v_object := my_object('Open','John');
                    11    v_objects.extend;
                    12    v_objects(1) := v_object;
                    13    v_object := my_object('Close','Ron');
                    14    v_objects.extend;
                    15    v_objects(2) := v_object;
                    16    FOR i IN 1..v_objects.COUNT LOOP
                    17        EXECUTE IMMEDIATE 'DECLARE
                    18                               v_object my_object;
                    19                           BEGIN
                    20                               v_object := :1;
                    21                               DBMS_OUTPUT.PUT_LINE(v_object.' || p_token1 ||
                    22                               ' || '' by '' || v_object.' || p_token2 || ');
                    23                           END;'
                    24          USING v_objects(i);
                    25    END LOOP;
                    26  END;
                    27  /

                    Procedure created.

                    SQL> exec p1('agent_name','status')
                    John by Open
                    Ron by Close

                    PL/SQL procedure successfully completed.

                    SQL> exec p1('status','agent_name')
                    Open by John
                    Close by Ron

                    PL/SQL procedure successfully completed.

                    SQL>

                     

                    SY.

                    • 7. Re: PLSQL getting value of property in record dynamically
                      ef2019c7-080c-4475-9cf4-2cf1b1057a41

                      Thanks Solomon.

                      I would stick to PLSQL Type and use package.

                      But, how to accomplish the EXECUTE IMMEDIATE statement.

                       

                      EXECUTE IMMEDIATE 'select :v_records(:i).' || v_token2 || ' from dual'

                          into v_value USING i, v_records ;

                      • 8. Re: PLSQL getting value of property in record dynamically
                        Solomon Yakobson

                        You can't. That's why you need package. By using package you avoid passing v_records (which is not of SQL type) to dynamic sql. You store v_records (g_records in my example) in the package and tell dynamic sql to get it from there.

                         

                        SY.