Need to substitute the column name from a variable for the Select query. Options

[Sreekumars]

Dear All,
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. Can someone help me to sort it out?

Create table Test1(ID Number(10),
Name varchar2(100),
Description varchar2(1000),
Site varchar2(100));

Insert Into Test1 values(10,'Thomas','Manager','LA');
Insert Into Test1 values(11,'George','Chairman','LA');

declare
MTest1 Test1%RowType;
str1 VarChar2(1000):='Name';
MValue1 Varchar2(100);
begin
Select * Into MTest1 from Test1 where ID=10;
str1:='Select MTest1.'||str1||' from dual';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;
/

I am getting the error:

ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9

[burleson]

Hi,

>> In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below

But you're nor really changing the colimn name, you hav a mutli-use column and a flig that says what tyoes of data is in it . . . .

*****************************************
>> str1:='Select MTest1.'||str1||' from dual';

What are you trying to dio here?

>> execute immediate str1 into mvalue1;

This is not a valid INSERT statdment!

See here, using dynamic SQL in PL/SQL:

http://www.dba-oracle.com/t_building_dynam...l_statement.htm

[Sreekumars]

Hi,

My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names, to reduce number of lines in the code.

Also, by modifying the code as given below, I am able to get MValue1='Thomas'.

declare
str1 VarChar2(1000);
MValue1 Varchar2(100);
begin
str1:='Select Name from Test1 where id=10';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;

But it doesn't serve my purpose, as I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database. Hope my requirement is clear.

Thanking all,
Sreekumar.

Hi,

>> In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below

But you're nor really changing the colimn name, you hav a mutli-use column and a flig that says what tyoes of data is in it . . . .

*****************************************
>> str1:='Select MTest1.'||str1||' from dual';

What are you trying to dio here?

>> execute immediate str1 into mvalue1;

This is not a valid INSERT statdment!

See here, using dynamic SQL in PL/SQL:

http://www.dba-oracle.com/t_building_dynam...l_statement.htm

[burleson]

Hi Sreekumar,

Like I noted, this is a DBA forum, and we are not developers here!

You might seek a developer forum . . .

******************************'
>> Hope my requirement is clear.

Yes, I just don't know the answer . . .

Sorry I can't help . . . .

[Sreekumars]

Hi Sreekumar,

Like I noted, this is a DBA forum, and we are not developers here!

You might seek a developer forum . . .

******************************'
>> Hope my requirement is clear.

Yes, I just don't know the answer . . .

Sorry I can't help . . . .

Thanks Burleson.
I will take help from any Developers forum.

Sreekumar.

[burleson]

Hi Sreekumar,

>> I want to have the column name to be dynamic, taken from an array of column names, to reduce number of lines in the code.


I dunno. That seems like a lot of complexity just ti reduce the number of lines of code.

Anyway, check out context_api, similar to your spec:

http://www.dba-oracle.com/plsql/t_plsql_dynamic_binds.htm