In Oracle I can declare a reference cursor... TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; ...and use it to pass a cursor as the return value... FUNCTION end_spool RETURN t_spool AS v_spool t_spool; BEGIN COMMIT; OPEN v_spool FOR SELECT * FROM ...
This package uses two unique features of Oracle, REF_CURSOR and a package global variable. I would like to port the functionality from Oracle to Postgresql or MySQL. PACKAGE tox IS /*=======================*/ g_spool_key spool.key%TYPE := NULL; TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; /*=======================...
Hi all I'm trying to use a RefCursor as an input parameter on an Oracle stored procedure. The idea is to select a group of records, feed them into the stored procedure and then the SP loops over the input RefCursor, doing some operations to its records. No, I can't select the records inside the SP and thus avoid having to use the RefCur...
I want to return data from an Oracle procedure to populate some Label controls. The procedure accepts 26 input parameters (search variable) and returns 3 output cursors. I have been successful returning data from a procedure which returns a single ref cursor using OracleCommand, a DataAdapter, and a DataSet, but have been having all kin...
Hi, I'm using ODP.NET (migrating from Microsoft's provider), and I have got stuck on a stored procedure that returs a refcursor. I have the following PL/SQL procedure (I have changed it a little bit to make it more general): PROCEDURE MyProc(parameter_no1 IN NUMBER, parameter_no2 IN NUMBER, RETCURSOR OUT ret_type ) AS BEGIN OPEN RET...
I'm a little bit new to PL/SQL and need something that looks a bit like this: create type base as object ( unused number, member procedure p( c in ref cursor ) ) not final; create type child1 under base ( overriding member procedure p( c in ref cursor ) as t table1%rowtype begin fetch c into t; -- process table1 row...
I have a PostgreSQL function that selects data and returns it via a refcursor, similar to the following declaration: CREATE OR REPLACE FUNCTION my_function() RETURNS refcursor AS ... How do I retrieve data from this function via a CodeIgniter model? I can't just SELECT directly from the function as it does not return the data dire...
Hello folks, i'm working in an app that was originally built to connect to SQL Server by using ADO.NET SqlClientProvider. Recently, i had to generalize the data provider code to work with either SQL Server or Oracle (by using DbClasses instead of SqlClasses and so on...). My problem is: Oracle SPs need to use ref cursors to retrieve ta...
I have a coworker who came across DBMS_SQL.to_refcursor which would be a great solution for him to pass back the refcursor he needs, however we are running Oracle 10g and this feature is only available in 11g. Is there an easy equivalent to this in Oracle 10g? We have developed an alternate way of coding our solution but it would be ...
Is it possible to get ref cursor from oracle stored procedure by using DAAB from Microsoft Enterprise Library 4.1? ...
My PL/SQL procedure returns a cursor. It always returns data. I fetch (oci_fetch_assoc) it and save it in an array. If results were found the keys of the array will be strings. If the cursor didn't find data, it will return value 0, thus the key of the array will be numeric. while($data = oci_fetch_assoc($cursor)){ if(!isset($data[...
Hello, I'm trying to do the same request i'm using in Toad (the store proc signature is two varchar2 parameter and one REF CURSOR parameter) Here is what I do with Toad variable myCursor refcursor; EXEC myproc('param1','param2',:myCursor ); print myCursor; I don't know how to write this with Squirrel and I have to use Squirrel. th...
oracle i wish to select few rows at random from a table, update a column in those rows and return them using stored procedure PROCEDURE getrows(box IN VARCHAR2, row_no IN NUMBER, work_dtls_out OUT dtls_cursor) AS v_id VARCHAR2(20); v_workname VARCHAR2(20); v_status VARCHAR2(20); v_work_dtls_cursor dtls_cursor; BEGIN O...
I would like to know how to check if a ref cursor returns data. Let's say I have the following code in a PL/SQL package: type refcursor is ref cursor; procedure Foo(cursorresult out refcursor) is begin open cursorresult for select * from table t inner join t2 on t.id = t2.id where t.column1 is null; end; proced...