Home » Other » Client Tools » about stred procedure
about stred procedure [message #248687] Sat, 30 June 2007 07:46 Go to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

hi,



i need a stroed procedure that takes table name and column names as arg and this procedure should return all column values in that table .

this option available in sql server. but no option available in oracle.
how can i accomplish this task?

this procedure is integrated in .net application?
Re: about stred procedure [message #248694 is a reply to message #248687] Sat, 30 June 2007 10:53 Go to previous messageGo to next message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
U need to create ur own function. Its not available. u can use following query

select column_name
from all_tables
where table_name = p_table_name
and owner = p_owner_name;
Re: about stred procedure [message #248697 is a reply to message #248687] Sat, 30 June 2007 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace procedure p (tab in varchar2, col in varchar2, cur out sys_refcursor)
  2  is
  3  begin
  4    open cur for 'select '||col||' from '||tab;
  5  end;
  6  /

Procedure created.

SQL> var v refcursor
SQL> exec p('T','VAL',:v);

PL/SQL procedure successfully completed.

SQL> print v;
VAL
--------------------
abc1
abc10
abc100
abc21
abc3
abc2

6 rows selected.

Regards
Michel
Re: about stred procedure [message #248706 is a reply to message #248697] Sat, 30 June 2007 22:31 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

thanks for reply.
one problem again
in pl/sql developer refcursor is not supported.

can you write another procedure for this?

Re: about stred procedure [message #248722 is a reply to message #248706] Sun, 01 July 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know PL/SQL Developer but I can't imagine a tool that does not know cursor.

I transfer this thread to SQL Client Tools forum.

Regards
Michel
Re: about stred procedure [message #248742 is a reply to message #248722] Sun, 01 July 2007 07:45 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

ok

in pl/sql developer tool

if i declare host variable in sql window like this
sql>variable x refcursor

it gives

refcursor is not supported .


i dont about pl/sql developer iam new to pl/sql developer tool.
Re: about stred procedure [message #248750 is a reply to message #248742] Sun, 01 July 2007 10:55 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant this may not be the syntax.
There may be another syntax.
But there surely is a way to declare a cursor.
Did you check in the documentation?
Otherwise, wait for someone.

Regards
Michel
Previous Topic: Call to pl/sql developer report file from a function
Next Topic: Debugging Problem In plsql Developer ver 7.0.0
Goto Forum:
  


Current Time: Thu Mar 28 18:13:39 CDT 2024