how to query SP??

[Przemek]

Hello,

Does anybody knows if there is a way to query SP? I tried begin sp_name(list_of_arguments); end;. But I do not see any output other than some line populated with data for values of parameters. I'd like to see the same result I'm getting when running report, that it in tabular form with many lines of data. This report is SP based, and while having no problem with catching SQL and query data of JSP based report, for SP seems to be 'mission impossible'. Answers are greatly appreciated - please respond either here or to my pzawadzki@hotmail.com. Thank you.

 

[Marco van der Linden]

You have a problem there. You simply cannot query a stored procedure. A workaround might be to create a function which calls your stored procedure and have the function return your ref cursor. As far as I know your function has to return a strongly typed ref cursor, although this may depend on your Oracle version. The query would then look something like:

select *
from   table(cast(your_function as my_table_type))

For more detailed examples check out asktom.oracle.com and search for 'cast', you will find loads of examples there.

Good luck