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

No, you can't do that.

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

Thx anyway for your quick reply.

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.

THX a lot!!!
THANK YOU!!! THANK YOU!!! THANK YOU!!!

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

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

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.

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......

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 ?