Sitemap

Amicuk Programming Answers

Call procedure that uses REF CURSOR?

-0001-11-30   Views:1

Advertisement

Can someone tell me how to call a procedure that uses a REF CURSOR? Procedure is something like this: PROCEDURE my_proc P_PROG_ID IN VARCHAR2, P_CRITERIA IN VARCHAR2, P_TASKCURSOR OUT MYREFCUR IS blah, blah blah I tried this to call the procedure: ex

Can someone tell me how to call a procedure that uses a REF CURSOR?
Procedure is something like this:
PROCEDURE my_proc
P_PROG_ID IN VARCHAR2,
P_CRITERIA IN VARCHAR2,
P_TASKCURSOR OUT MYREFCUR
IS
blah, blah blah
I tried this to call the procedure:
exec my_proc(variable1,variable2,variable3)
but I don't know how to define variable3.
Can someone help me out?

The replay answer
Advertisement
Based on the parameters for the procedure, it looks like you have a cursor (myrefcursor) defined in a package somewhere. So, in sqlplus, you would need to do it in an anonymous block, something like:
DECLARE
   l_cur package_name,myrefcur;
   variables to hold cursor fields
BEGIN
   my_proc('ID', 'Criteria', l_cur);
   LOOP
      FETCH l_cur INTO variables to hold cursor fields
      EXIT WHEN l_cur%NOTFOUND;
      Do something with variables
   END LOOP;
   CLOSE l_cur;
END;If you are on Oracle 9 or higher, assuming that myrefcursor is weakly typed,
you could change the definition of the procedure to:
PROCEDURE my_proc (p_prog_id    IN  VARCHAR2,
                   p_criteria   IN  VARCHAR2,
                   P_TASKCURSOR OUT SYS_REFCURSOR)then you could call it in sql plus like:
-- Define the variables
var var1 VARCHAR2(100);
var var2 VARCHAR2(100);
var cur  SYS_REFCURSOR;
-- Assign Vlaues to the IN parameters
EXEC :var1 := 'ID'; :var2 := 'Criteria';
EXEC my_proc(:var1, :var2, :cur);
-- See the contents of the cursor
print curHTH
John

Go to See the other 5 answers

How to call a Stored Procedure with a REF CURSOR output parameter

Category:DefaultRelease time:2015-10-11Views:130

I am looking forward an example that call a stored function/procedure with a REF CURSOR output parameter and get the result. In other words, I have a stored function/procedure that execute a SELECT statement using the OCI library and then it could ge[More]

Call to Oracle stored procedure that returns ref cursor doesn't work

Category:DefaultRelease time:2015-10-11Views:130

I'm trying to use an OData service operation with Entity Framework to call an Oracle stored procedure that takes an number as an input parameter and returns a ref cursor. The client is javascript so I'm using the rest console to test my endpoints. I[More]

VC 7.0 Oracle stored procedures resultset with ref cursor

Category:DefaultRelease time:2015-10-11Views:130

Can VC (we are on NW7 SP13) handle Oracle's datatype ref cursor - which is the standard solution in Oracle to return result sets - as the return value of a stored procedure? When testing a data service in the VC story board based upon a simple Oracle[More]

Call procedure that uses REF CURSOR?

Category:DefaultRelease time:-0001-11-30Views:130

Can someone tell me how to call a procedure that uses a REF CURSOR? Procedure is something like this: PROCEDURE my_proc P_PROG_ID IN VARCHAR2, P_CRITERIA IN VARCHAR2, P_TASKCURSOR OUT MYREFCUR IS blah, blah blah I tried this to call the procedure: ex[More]

Stored procedure call with REF CURSOR from JDBC

Category:DefaultRelease time:-0001-11-30Views:130

How can I call a SP with a REF CURSOR OUT parameter from JDBC?This is a breeze. CallableStatement oraCall = oraConn.prepareCall("BEGIN PKG_SOMETHING.RETURNS_A_SP(?);END;"); oraCall.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR); o[More]

Problems calling stored procedure with out ref cursors

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am calling an oracle stored procedure and having problems. This is the code: ResultSet cursor1, cursor2, cursor3, cursor4,cursor5,cursor6; String sql = "BEGIN SYSADM.PKG_SERVICE.SERV_MAIN(:1,:2,:3,:4,:5,:6,:7,:8,:9) \n; END;"; CallableStat[More]

How to create a procedure to output REF CURSOR with any WHERE clause?

Category:DefaultRelease time:2015-10-11Views:130

I have an requirement like this: I have huge query which need to reuse in my code more than 10 times. This SQL has about 50 lines. Thing is for those 10 odd times sometimes the WHERE clause changes (columns are the same). So I cannot create a view si[More]

Problem to call stored procedure with several IN pars and single REF Cursor

Category:DefaultRelease time:2015-10-11Views:130

Hi, Oracle 9.2.0.1 Ole DB Provider I've got with ODP 9.2.0.4 First I try to call packaged procedure with single REF CURSOR - it works fine(PROCEDURE getDep(dep OUT DEPART.refcur) IS ...). When I try to call procedure with additional IN parameter, I g[More]

Select List based on a procedure returning ref cursor

Category:DefaultRelease time:-0001-11-30Views:130

Hi All, How do a I create a select list based on a procedure returning a ref cursor. e.g. proc test(vtest out ref cursor); vtest returns following values: 'test1' 'test2' 'test3' Thanks !I think I got it. I dont have a procedure to test but I used a[More]

Ref Cursor over Implicit and explicit cursors

Category:DefaultRelease time:2015-10-11Views:130

Hi, In my company when writing PL/SQL procedure, everyone uses "Ref Cursor", But the article below, says Implicit is best , then Explicit and finally Ref Cursor.. [http://www.oracle-base.com/forums/viewtopic.php?f=2&t=10720] I am bit confuse[More]

DB proc - do you need to create a table to pass a ref cursor record type?

Category:DefaultRelease time:2015-10-11Views:130

I want to pass a limited selection of columns from a large table through a DB procedure using a REF CURSOR, returning a table rowtype: CREATE OR REPLACE package XXVDF_XPOS_DS021_ITEMS AS      TYPE XXVDF_XPOS_DS021_ITEM_ARRAY      IS REF CURSOR      r[More]

Hot
Hi We are facing issue with ECC PO migration to SRM SUS client due to missing line item delivery address field name. We have PI integration. Name exist in PO XML on ECC interface but not copied on to PO line delivery address details (ME23n) Impact: W [More]
Hi, So I have collected so much music and movies in iTunes that my internal HD is practically full.  (Library is over 500GB of 1TB and the rest is taken up with my apps and programs.)  It's time to purchase an external hard drive to transfer my libra [More]
I decide to use Final Cut Pro 7 instead of Final Cut Pro X to use the Apple Color. But I wonder if the Final Cut Pro 7 can render video or work with Compressor 4 instead of the Compressor 3. Any ideas of what to do? Thank you for the answer. SteveThi [More]
what is the best way to "upgrade" a powerbook g4 with a powerpc chip?  it seems that many things are not supported these days.  can't download software, update my iphone, download video, etc.  can i get the latest software?  thanks for the help! [More]
Same as others on this thread.  Facebook slows, then freezes, then a pop-up says Skype Click to Call plug-in unresponsive.  I see this pop-up has been plaguing others for several months now.  I have been seeing it for about a week now.  I uninstalled [More]
Hi. I am on Jdev 11g R3 I have a table with several editable columns. One of the columns is LOV I put some data in the NON lov field. Next I choose the value from LOV. As soon as I choose the value in LOV  - the rest of the fields get cleared and app [More]
Hi Gurus, I was wondering if Hyperion Repository can be migrated from an existing Oracle database to MySQL database. If yes, then how? Any kind of advice is greatly appreciated. Regards, UdayHi, First question is why you want to move from Oracle to M [More]
Dear All, I have developed an ALV report . The users will create many layouts and save it. The requirement is that: In the selection screen , they need to get the list of saved layouts and select one from that so that they need not have to select lay [More]
Hi everyone! I'm back with more doubts I created 4  buttons with 4 animations each, one for each state (using tweens), so I  have that imense list of 16 "addEventListeners" (don't know if there's  another way to do it). But everything is working [More]
The default URL for the report server ( http://10.146.91.165:9704/xmlpserver ) returns an error message. I am accessing from Word and everything else works and appears to be running. Would someone please check the June 2009 BIC2G Windows Image and co [More]