Home » SQL & PL/SQL » SQL & PL/SQL » passing table name and column names as parameter to procedure
passing table name and column names as parameter to procedure [message #247842] Wed, 27 June 2007 06:31 Go to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

i need to create a procedure that takes table name and column names as parameter and dispalys data. this is intergated in front end. columns and no columns differ
for example
procedue ( table_name,col1,col2)
...
and
procedure ( table_name, col1,col2,col3)



thanks
dh

[Updated on: Wed, 27 June 2007 06:33]

Report message to a moderator

Re: passing table name and column names as parameter to procedure [message #247847 is a reply to message #247842] Wed, 27 June 2007 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search on AskTom for print_table, you'll get a clue how to do it.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247848 is a reply to message #247842] Wed, 27 June 2007 06:36 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Why don't you try using Ref cursors?
Re: passing table name and column names as parameter to procedure [message #247851 is a reply to message #247848] Wed, 27 June 2007 06:43 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

please write some code .
Re: passing table name and column names as parameter to procedure [message #247861 is a reply to message #247851] Wed, 27 June 2007 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please search, code is there.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247864 is a reply to message #247861] Wed, 27 June 2007 07:04 Go to previous messageGo to next message
noorahmed.shaik
Messages: 6
Registered: June 2007
Location: Bangalore
Junior Member

check this code

CREATE PROCEDURE dbo.StoredProcedure5
@p1 varchar(50),
@p2 varchar(50),
@d1 varchar(50),
@d2 varchar(50)
AS
exec ('create table t1(' + @p1 + @d1 + ',' + @p2+ @d2+ ')')
go
Re: passing table name and column names as parameter to procedure [message #247870 is a reply to message #247842] Wed, 27 June 2007 07:16 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

actually what iwant is
take
procedure p ( table_name , col1)
this procedure should display all col1 values in table_name

if i give like this
procedure p ( table_namee, col1,col2)
it should display col1, col2 values in table_name

first parameter is table _name
and remaining parameters are column names in that table .



[Updated on: Wed, 27 June 2007 07:24]

Report message to a moderator

Re: passing table name and column names as parameter to procedure [message #247873 is a reply to message #247870] Wed, 27 June 2007 07:27 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi ddkdhar,

you should pass two argument in procedure one is table name and second is ','separated cloumn name list...
i am trying some thing like that but i am getting error.

function search_table (table_name in varchar2,colum in varchar2) return  ref_rec
is
final_cur ref_rec;
flag number;
v_final_query varchar2(300);
begin 

v_final_query :='select'|| colum ||' from '||table_name; 

execute immediate v_final_query into final_cur;

return final_cur;

end search_table;

 


i am getting error in
execute immediate v_final_query into final_cur;


error is -:ORA-00900: invalid SQL statement
ORA-06512: at "RAMSADM.TEST_SEARCH", line 36

any bosy can suggest some thing ..

--Yash

--Yash



Re: passing table name and column names as parameter to procedure [message #247877 is a reply to message #247873] Wed, 27 June 2007 07:37 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

yes

if i pass two columns names it should display those column values.
if i pass three column names it should display all those three column values .
like that if i pass one table name and 10 column names int that table it should display those 10 column all values.
thanks and regards
dd
Re: passing table name and column names as parameter to procedure [message #247879 is a reply to message #247864] Wed, 27 June 2007 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Ahmed,

Good joke! Laughing

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247880 is a reply to message #247873] Wed, 27 June 2007 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash,

A space is missing.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247883 is a reply to message #247880] Wed, 27 June 2007 07:44 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

i tried but

CREATE OR REPLACE PROCEDURE SFA_TAB_COL1
( P_tname IN varchar2,p_cnames in array)
IS
l_stmt long;
l_rc number;
begin
l_stmt :='select '||p_cnames(1);
for i in 2..p_cnames.count
loop
l_stmt :=l_stmt||' , '||p_cnames(i);
end loop;
l_stmt:= l_stmt||' from '|| p_tname ;
execute immediate l_stmt;
end ;
Re: passing table name and column names as parameter to procedure [message #247886 is a reply to message #247883] Wed, 27 June 2007 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but

But what? What is the error? Where is "array" declared/defined?

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247888 is a reply to message #247880] Wed, 27 June 2007 07:51 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Michel !!

now i am getting fallowing error.

ORA-00932: inconsistent datatypes: expected CURSER got CHAR


it means quesy has returned char datatype and i am trying to store result in ref_cursor type ..can you let me know how can i handle this ..

--Yash
Re: passing table name and column names as parameter to procedure [message #247889 is a reply to message #247842] Wed, 27 June 2007 07:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Does Oracle really have Cursor spelled incorrectly in their error message ?

That's pretty funny if they do

Re: passing table name and column names as parameter to procedure [message #247890 is a reply to message #247861] Wed, 27 June 2007 07:54 Go to previous messageGo to next message
noorahmed.shaik
Messages: 6
Registered: June 2007
Location: Bangalore
Junior Member

CREATE PROCEDURE dbo.StoredProcedure5
@p1 varchar(50),
@p2 varchar(50),
@d1 varchar(50),
@d2 varchar(50)
AS
exec ('create table t1(' + @p1 + @d1 + ',' + @p2+ @d2+ ')')
go
Re: passing table name and column names as parameter to procedure [message #247891 is a reply to message #247890] Wed, 27 June 2007 07:57 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
here is complete error message


declare
result_cur  test_search.ref_rec;
TNAME varchar2(60);
begin
result_cur := test_search.search_table('tab','TNAME');

LOOP

FETCH result_cur INTO TNAME;
EXIT WHEN result_cur%NOTFOUND;
		dbms_output.put_line(TNAME);
-- process data record
END LOOP;
end;
ORA-00932: inconsistent datatypes: expected CURSER got CHAR
ORA-06512: at "RAMSADM.TEST_SEARCH", line 38
ORA-06512: at line 5


--Yash
Re: passing table name and column names as parameter to procedure [message #247895 is a reply to message #247890] Wed, 27 June 2007 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Ahmed,

Once again, good joke! Laughing

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247897 is a reply to message #247891] Wed, 27 June 2007 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash,

What is line 38.
There is nothing with 38 lines in this thread.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247899 is a reply to message #247842] Wed, 27 June 2007 08:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I believe the stack trace is from line 38 in the procedure being called.

Re: passing table name and column names as parameter to procedure [message #247903 is a reply to message #247899] Wed, 27 June 2007 08:19 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
here is complete package body..

CREATE OR REPLACE package body test_search  AS

function search_fun ( proj_id in number,userid in varchar2) return  ref_rec

is 

seach_string varchar2(4000);
searcg_id number :=1;
v_final_query varchar2(300);
final_cur ref_rec;

begin 

seach_string := proj_id||'|'||userid;

--insert into tmp1 values(searcg_id,seach_string);

--v_final_query :='select proj_id,year,empid,'||searcg_id||'from ram_resources where proj_id='||proj_id||' and empid='||userid;

v_final_query :='select TNAME from tab';
open final_cur for (v_final_query);

return final_cur;

end search_fun;

function search_table (table_name in varchar2,colum in varchar2) return  ref_rec
is
final_cur ref_rec;
flag number;
v_final_query varchar2(300);
begin 
--v_final_query :='select TNAME'|| colum ||' from '||table_name; 
v_final_query :='select TNAME from TAb';

execute immediate v_final_query into final_cur;

return final_cur;

end search_table;

end test_search;
/




here is error message ..which is cmg on execute immediate statment..


declare
result_cur  test_search.ref_rec;
TNAME varchar2(60);
begin
result_cur := test_search.search_table('tab','TNAME');

LOOP

FETCH result_cur INTO TNAME;
EXIT WHEN result_cur%NOTFOUND;
		dbms_output.put_line(TNAME);
-- process data record
END LOOP;
end;
ORA-00932: inconsistent datatypes: expected CURSER got CHAR
ORA-06512: at "RAMSADM.TEST_SEARCH", line 38
ORA-06512: at line 5
 



--Yash
Re: passing table name and column names as parameter to procedure [message #247904 is a reply to message #247903] Wed, 27 June 2007 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is line 38?

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247906 is a reply to message #247904] Wed, 27 June 2007 08:36 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
38th line of test_search package is .

execute immediate v_final_query into final_cur;



--Yash
Re: passing table name and column names as parameter to procedure [message #247907 is a reply to message #247842] Wed, 27 June 2007 08:37 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Actually, I believe line 38 is

 return final_cur; 
Re: passing table name and column names as parameter to procedure [message #247913 is a reply to message #247907] Wed, 27 June 2007 09:00 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
38th line of test_search package is

execute immediate v_final_query into final_cur;


ORA-00932: inconsistent datatypes: expected CURSER got CHAR
ORA-06512: at "RAMSADM.TEST_SEARCH", line 38
ORA-06512: at line 5


i though error is also related on excute immediate statment ..statment expecting cursor value but query return the char data type ..

--Yash
Re: passing table name and column names as parameter to procedure [message #247915 is a reply to message #247906] Wed, 27 June 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Statement in v_final_query returns a string, you want to put it in a cursor, so the error.
I think what you want to execute is "open cursor for string".

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247919 is a reply to message #247915] Wed, 27 June 2007 09:11 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Michel i tried to open cursor like that


open final_cur for (execute immediate  v_final_query);


it has given error is :-
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

   . ( ) , * @ % & | = - + < / > at in is mod not range rem =>
   .. <an exponent (**)> <> or != or ~= >= <= <> and 


can you plz let me know the exact sysntax ..


--Yash
Re: passing table name and column names as parameter to procedure [message #247922 is a reply to message #247919] Wed, 27 June 2007 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Syntax is in the documentation.
If you checked it, you'd never put this "execute immediate".

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247928 is a reply to message #247922] Wed, 27 June 2007 09:55 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i am not able to debug it ..can you let me know how i need to handle it..if possible syntax plz..


--Yash
Re: passing table name and column names as parameter to procedure [message #247933 is a reply to message #247842] Wed, 27 June 2007 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
try printing out the SQL statement before EXECUTE IMMEDIATE.
The test SQL in SQL*Plus
Re: passing table name and column names as parameter to procedure [message #247938 is a reply to message #247928] Wed, 27 June 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash,

Can you post where you search in the documentation.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247941 is a reply to message #247933] Wed, 27 June 2007 10:41 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i have modifyed my package i have added fallowing line.

sql_stmt := 'INSERT INTO tmp1(QUERY_S)  VALUES (:1)';
EXECUTE IMMEDIATE sql_stmt using v_final_query ;
commit; 


i chked in tmp1 table... query is working fine on toad...but still i am getting error.

ORA-00932: inconsistent datatypes: expected CURSER got CHAR


please let me know the syantx

--Yash
Re: passing table name and column names as parameter to procedure [message #247948 is a reply to message #247941] Wed, 27 June 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All is there in this thread.
Try again.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #247954 is a reply to message #247842] Wed, 27 June 2007 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ammishra,
You HIJACKED this thread start by ddkdhar; makes a mess try to sort out the two DIFFERENT problems.

You keep changing your code drastically:
>EXECUTE IMMEDIATE sql_stmt using v_final_query ;
>execute immediate v_final_query into final_cur;

USING and INTO are way different constructs.

I have a serious problem understanding why folks have to resort to EXECUTE IMMEDIATE to do simple DML & why they don't just use bind variables.

Since I don't understand this morphing mess, I'll let others to do your job for you.
Re: passing table name and column names as parameter to procedure [message #247955 is a reply to message #247954] Wed, 27 June 2007 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ana,

There is no more to say.
All what he need is already in the thread.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #248117 is a reply to message #247955] Thu, 28 June 2007 02:03 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member

i got my result with these chnages ....

v_final_query :='select '|| colum || ' from '|| table_name;
open final_cur for v_final_query ;--using colum,table_name; 


but still i am not able to do the same thing with EXECUTE IMMEDIATE statment..i tried with bind variable also but i am not able ..

v_final_query :='select '|| :colum || ' from '|| :table_name;
open final_cur for v_final_query using colum,table_name; 



--Yash

Re: passing table name and column names as parameter to procedure [message #248141 is a reply to message #248117] Thu, 28 June 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try again.
Maybe if you read a little bit the documentation on PL/SQL you'll find it faster.

Regards
Michel
Re: passing table name and column names as parameter to procedure [message #248144 is a reply to message #247842] Thu, 28 June 2007 03:01 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Although you try with SQL*Plus variables, you cannot do it either.
As stated on Dynamic Dynamic SQL thread on AskTom:
Quote:
Identifiers cannot be used as bind variables -- ever. Identifiers must be "hard coded" into the query.

In your case, colum and table_name are identifiers.
What is wrong with your first code?
Previous Topic: What r the ways of reducing cost & time?
Next Topic: FIRST_VALUE under a subselect problem
Goto Forum:
  


Current Time: Sun Dec 04 06:19:43 CST 2016

Total time taken to generate the page: 0.10654 seconds