Home » SQL & PL/SQL » SQL & PL/SQL » How to display the query result in procedure? (Oracle 9i)
How to display the query result in procedure? [message #331574] Fri, 04 July 2008 01:42 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
create table test(a int, b int);

insert into test values(1,2);
insert into test values(2,2);
insert into test values(3,2);
insert into test values(4,2);
insert into test values(5,2);

CREATE OR REPLACE PROCEDURE TRANSPOSE_TABLE(P_sqlstmt IN varchar2)
AS
L_sqlstmt Varchar2(255);
BEGIN	
  L_sqlstmt := trim(p_sqlstmt);
  EXECUTE IMMEDIATE(L_sqlstmt) ; 
  
END; 
/

Exec TRANSPOSE_TABLE('select * from test');
/


thanks in advance........
Re: How to display the query result in procedure? [message #331576 is a reply to message #331574] Fri, 04 July 2008 01:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And your question is?
Re: How to display the query result in procedure? [message #331579 is a reply to message #331576] Fri, 04 July 2008 02:00 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

His question is 'How to display the query result in procedure?'.

You can use refcursor to store your query resultset and then you can print your refcursor in SQL Plus. For that you need to modify your procedure.

Regards
Soni
Re: How to display the query result in procedure? [message #331580 is a reply to message #331579] Fri, 04 July 2008 02:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would be sheer nonsense and NOT answer his question.
Nonsense because it would be easier to just execute the query, instead of going through the hassle of dynamically executing it through a procedure and returning the results.
Not answering the question you claim he has, because it is NOT the procedure that does the displaying.
Previous Topic: Checking Time in and out vs others
Next Topic: need help in creating a view in 10g
Goto Forum:
  


Current Time: Tue Dec 06 02:44:06 CST 2016

Total time taken to generate the page: 0.10356 seconds