OraFAQ Forum: Client Tools » about stred procedure

Home » Other » Client Tools » about stred procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

about stred procedure [message #248687]

Sat, 30 June 2007 07:46

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?

Report message to a moderator

Re: about stred procedure [message #248694 is a reply to message #248687]

Sat, 30 June 2007 10:53

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;

Report message to a moderator

Re: about stred procedure [message #248697 is a reply to message #248687]

Sat, 30 June 2007 11:35

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: about stred procedure [message #248706 is a reply to message #248697]

Sat, 30 June 2007 22:31

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?

Report message to a moderator

Re: about stred procedure [message #248722 is a reply to message #248706]

Sun, 01 July 2007 00:57

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: about stred procedure [message #248742 is a reply to message #248722]

Sun, 01 July 2007 07:45

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.

Report message to a moderator

Re: about stred procedure [message #248750 is a reply to message #248742]

Sun, 01 July 2007 10:55

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 09:54:36 CDT 2025