Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Need to substitute the column name from a variable for the Select query.
Sreekumars
post Jan 24 2012, 05:45 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 24-January 12
Member No.: 46,645



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
Go to the top of the page
 
+Quote Post
burleson
post Jan 24 2012, 11:18 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,610
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Sreekumars
post Jan 24 2012, 11:40 PM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 24-January 12
Member No.: 46,645



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.


QUOTE (burleson @ Jan 24 2012, 12:18 PM) *
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

Go to the top of the page
 
+Quote Post
burleson
post Jan 25 2012, 12:13 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,610
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Sreekumars
post Jan 27 2012, 05:50 AM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 24-January 12
Member No.: 46,645



QUOTE (burleson @ Jan 25 2012, 01:13 PM) *
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.
Go to the top of the page
 
+Quote Post
burleson
post Jan 27 2012, 08:01 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,610
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 21st October 2014 - 02:54 PM