0

I am trying to write a stored procedure in oracle which takes an IN param and uses this as part of the SELECT query. I want to return a results set exactly the same as if I had simply ran SELECT * FROM table where id = 1; However I cannot seem to find any way to do this?

I have looked at sys_refcursor, dbms_sql.return_result and a few others but these either only compile or compile and return output to a console and not as a query result set.

A simple example of a procedure below

CREATE OR REPLACE PROCEDURE myProc
(
     v_id IN table.id%TYPE;
)
AS

BEGIN
     --desired query
     SELECT * FROM table WHERE id = v_id;

     --sys_refcursor which only prints to console
     open c1 for
       select * from table where id=v_id;
     dbms_sql.return_result(c1);

     --execute immediate does not return any results set
     execute immediate 'SELECT * FROM table WHERE id = v_id';
END;

Are there any other suggestions or ideas on how to achieve this? NOTE: I do not wish to process the results which is why I am not using an INTO clause, even if I did use one it does not return a results set.

C-MATT
  • 1
  • 1
  • 3
  • If you do not wish to process the results, then why are you doing the SQL anyway? You want your procedure to return the results? and from where is your procedure called? – gsalem Dec 16 '19 at 09:52
  • Does this answer your question? [Get resultset from oracle stored procedure](https://stackoverflow.com/questions/1170548/get-resultset-from-oracle-stored-procedure) – Serg Dec 16 '19 at 09:53
  • @gsalem yes I wish the procedure to return the results. I am migrating procedures from sybase to oracle. I believe the results set from this gets wrapped in a java object as part of the dbservice – C-MATT Dec 16 '19 at 09:58
  • @Serg thanks for the link I didnt see that one earlier. It looks as if the solutions there focus on sql plus / functions / dbms_output. Neither of which are suitable for my specific case unfortunately – C-MATT Dec 16 '19 at 10:03
  • Then have a look at how to write a [table function] (https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-4E10CBFA-4B6A-4761-8905-83C26C112694) – gsalem Dec 16 '19 at 10:23
  • Posted my comment on the answer, should have been here: Use a pipelined function. – TurtlesAllTheWayDown Dec 16 '19 at 20:28

1 Answers1

0

Sys_refcursor is all you need as it return the result to application - 

CREATE OR REPLACE PROCEDURE myProc
(
     v_id IN table.id%TYPE,
     P_Result OUT SYS_REFCURSOR
)
AS

BEGIN
     --desired query
     OPEN P_Result FOR
     SELECT *
     FROM table WHERE id = v_id;
END;

This procedure then should be called from another block - 

DECLARE
result SYS_REFCURSOR;
BEGIN
    myProc(your_ID, result);
END;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • tried this, all the console says is compiled successfully. This saves the result set in a variable right, it doesn't actually return it as a 'query result' – C-MATT Dec 16 '19 at 15:06
  • Sys_refcursor won't return you the result in query. You have to use this in a procedure to return data. – Ankit Bajpai Dec 16 '19 at 15:09
  • how could this be used inside the proc to return and show the results as a 'query result' without resorting to the dbms_sql or dbms_output? – C-MATT Dec 16 '19 at 15:19
  • Which sql tool you are using? – Ankit Bajpai Dec 16 '19 at 15:46
  • Use a pipelined function. – TurtlesAllTheWayDown Dec 16 '19 at 18:34
  • 1
    You need to process the results of of your procedure in sqlplus, a pipelined fuunction, or whatever tool you use developing it. It called testing. You validate this component is working as expected/necessary. If then you have problems with the Java service you know the problem is either in the Java code or the interface/connection. You won't spend hours or days looking at perfectly good and correct plsql code trying to find the error. – Belayer Dec 16 '19 at 18:55