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
Hello I want to read a text file that could look in some different ways since the user choose file from a list. I then want to igonre all the lines that begin with a special character, for example %. I then want to find all the lines "words" tha [More]
I have had my iPhone 4 since launch day abd have had no issues with it. Runs great, but I went ahead and got the free bumper just in case and sure enough after getting it and putting it on, I was at a concert and had it knocked out of my hand. Causin [More]
Hi, Generally taxes are based on country but in my case I want to get it triggered on the basis of region in a country. Say tax rate in a sales order for a SH(ship to party) for region A of country 1 should be 10% and if the sales order is raised for [More]
Hi, We have developed some custom WD ABAP applications in PPM 4.5 which were using Java webservices provided by xRPM 4.0 to get the UI context. The details are provided in the document https://websmp109.sap-ag.de/~sapidb/012006153200000315412008E/How [More]
I wanted to play with ArchLinux 2009.02, but replacing the existing Debian-Lenny on the laptop's HDD wasn't an option. So I experimented with installing Arch on an external USB Hard Disk Drive. I have tried to make this "newbie-friendly", so I h [More]
Hi. I'm using iPhoto 09, and have posted many MobileMe galleries over the years. Recently, I changed my MobileMe password. Now, iPhoto tells me that an "error occurred with the publication of album 'XYZ' Your MobileMe account information is not corre [More]
Hello, I am using an LG L246WHX monitor and wondering wether this is normal that system recognizes the monitor as a TV and confirms that no display is connected? I did not install any driver, etc, just plugged in via the DVI/HDMI connector included w [More]
On some websites can not get video or audio to work. Like on nascar.com i can not get it to work to watch race buddy. Can get it to work on other sites. Tried using IE 7 and chrome. I tried deleting the files on the support page but sxtill no luck. B [More]
Can you get QuickTime for iPad?No, it's built in. Make sure the file type of the movie is .mov or .m4v. Tap the video and it should playRead other 3 answers [More]
I just paid for PS and LR, - both downloaded via CC and LR opens fine, -but PS just shows a dialogbox with the classical white X on red bg, saying that PS could not open, cause the PNG-file couldn't be analyzed. What to do here? Regards Mikkel Denmar [More]