Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Problem with accessing PL/SQL Table column value dynamically

    Is it possible to use a variable as a field name within a PL/SQL Table . E.g.: I have a variable columnName (type: VARCHAR2(30)) and I defined a table type for record which has 3 columns. Now, I want to use it this way: tbl(i).columnName. But when I try to execute it I get the following error: PLS-00302: 'columnName' must be declared. Has anybody come across this problem? Pointers to help out would be great. I tried DBMS_SQL and also EXECUTE IMMEDIATE and in both the cases it gives me a runtime error.

    Regards,
    obdev.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    post code please with error as it is shown onscreen.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    This is the test code. As you can see v_column_name is dynamic. The error is below the code.


    declare
    TYPE Rec_Type IS RECORD
    (A NUMBER,
    B VARCHAR2(10),
    C NUMBER);
    v_column_name VARCHAR2(10) := 'B';
    TYPE Tbl_Type IS TABLE OF Rec_Type
    INDEX BY BINARY_INTEGER;
    v_value NUMBER;
    v_tbl_type Tbl_type;
    begin
    v_tbl_type(1).A := 10;
    v_tbl_type(1).B :='MISSING';
    v_tbl_type(1).C := 5;

    v_tbl_type(1).A := 6;
    v_tbl_type(1).B :='PERSON';
    v_tbl_type(1).C := 15;

    begin
    execute immediate
    'begin'||
    ':x := v_tbl_type(1).'||v_column_name ||';'||
    'end;'
    using OUT v_value;
    dbms_output.put_line('Value of A is ' || v_value);
    end;

    end;
    /


    ERROR at line 1:
    ORA-06550: line 1, column 12:
    PLS-00201: identifier 'V_TBL_TYPE' must be declared
    ORA-06550: line 1, column 6:
    PL/SQL: Statement ignored
    ORA-06512: at line 19

    Regards
    obdev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •