Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Question Unanswered: Using variable as field name in record based on cursor

    Is it possible to use a variable as a field name within a record fetched by a cursor. E.g.: I have a variable columnName (type: VARCHAR2(30)) and I defined a record variable (r_var) based on a cursor (c_var). Now, I want to use it this way: r_var.columnName. But when I try to execute it I get the following error: PLS-00302: component 'columnName' must be declared

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using variable as field name in record based on cursor

    No, you can't do that.

  3. #3
    Join Date
    Feb 2003
    Posts
    7

    Lightbulb

    It seems I always try to achieve the impossible!!! ;-)

    Thx anyway for your quick reply.

  4. #4
    Join Date
    Aug 2001
    Posts
    66
    Provided the record is declared as a public packaged global variable (i.e. in a package spec. somewhere) and the column is a valid SQL type (this wouldn't work for example with PL/SQL BOOLEAN) then you can reference it by name but there is no simple syntax, you would have to use dynamic PL/SQL, e.g.

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.2.0 - Production

    SQL> CREATE OR REPLACE PACKAGE package_name AS
    2
    3 TYPE record_type IS RECORD (
    4 some_column_name VARCHAR2 (30) := 'Vanilla');
    5
    6 record_name record_type;
    7
    8 END;
    9 /

    Package created.

    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
    2 v_column_name VARCHAR2 (30) := 'some_column_name';
    3 v_column_value VARCHAR2 (30);
    4 BEGIN
    5 EXECUTE IMMEDIATE
    6 'BEGIN' ||
    7 ' :x := package_name.record_name.' || v_column_name || ';' ||
    8 'END;'
    9 USING OUT v_column_value;
    10 DBMS_OUTPUT.PUT_LINE (v_column_value);
    11 END;
    12 /
    Vanilla

    PL/SQL procedure successfully completed.

    SQL>

    Be aware that if you do this repeatedly the parsing costs of this can get very expensive very quickly.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  5. #5
    Join Date
    Feb 2003
    Posts
    7
    THX a lot!!!
    THANK YOU!!! THANK YOU!!! THANK YOU!!!

  6. #6
    Join Date
    Oct 2003
    Posts
    2
    I was not able to do this using PL/SQL Tables. In my scenario I get the column name dynamically. But when I try to retrieve the value like the one used to retrieve from Record type it gives me an error. Any pointers to this would be great.

    Regards,
    Obdev

    Originally posted by padderz
    Provided the record is declared as a public packaged global variable (i.e. in a package spec. somewhere) and the column is a valid SQL type (this wouldn't work for example with PL/SQL BOOLEAN) then you can reference it by name but there is no simple syntax, you would have to use dynamic PL/SQL, e.g.

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.2.0 - Production

    SQL> CREATE OR REPLACE PACKAGE package_name AS
    2
    3 TYPE record_type IS RECORD (
    4 some_column_name VARCHAR2 (30) := 'Vanilla');
    5
    6 record_name record_type;
    7
    8 END;
    9 /

    Package created.

    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
    2 v_column_name VARCHAR2 (30) := 'some_column_name';
    3 v_column_value VARCHAR2 (30);
    4 BEGIN
    5 EXECUTE IMMEDIATE
    6 'BEGIN' ||
    7 ' :x := package_name.record_name.' || v_column_name || ';' ||
    8 'END;'
    9 USING OUT v_column_value;
    10 DBMS_OUTPUT.PUT_LINE (v_column_value);
    11 END;
    12 /
    Vanilla

    PL/SQL procedure successfully completed.

    SQL>

    Be aware that if you do this repeatedly the parsing costs of this can get very expensive very quickly.

  7. #7
    Join Date
    Aug 2001
    Posts
    66
    Likewise any pointers to exactly what you tried and what error you got would be great.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  8. #8
    Join Date
    Oct 2003
    Posts
    2
    Originally posted by padderz
    Likewise any pointers to exactly what you tried and what error you got would be great.
    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

  9. #9
    Join Date
    Aug 2001
    Posts
    66
    Read original post...

    "Provided the record is declared as a public packaged global variable (i.e. in a package spec. somewhere)"

    The variable you are trying to access is declared locally, not in a package spec.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  10. #10
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Thumbs up Quite Simple you made it complex

    procedure proc(sTableField VARCHAR2) AS
    BEGIN
    sql_stmt:='select nvl('||sTableField||',0) from table_name WHERE clause';
    execute Immediate sql_stmt into iFieldValue;
    return iFieldValue;
    END;


    You could even take the advantage of collections too.....but the above one is best if you are restricting the statement to return couple of values only. Enjoy......

  11. #11
    Join Date
    Aug 2001
    Posts
    66

    Re: Quite Simple you made it complex

    You're right that is simple.

    The answering the original question thing kind of went out of the window though didn't it?
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  12. #12
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Talking Re: Quite Simple you made it complex

    Originally posted by padderz
    You're right that is simple.

    The answering the original question thing kind of went out of the window though didn't it?
    Thnx Padderz it just that simple.... The problem with dbforums in everyone expects working code isnt it ?

Posting Permissions

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