Stack Overflow
Stack Overflow

Questions tagged [ref-cursor]

122 questions
47
votes
6 answers

How to test an Oracle Stored Procedure with RefCursor return type?

I'm looking for a good explanation on how to test an Oracle stored procedure in SQL Developer or Embarcardero Rapid XE2. Thank you.
Ryan Fisch
  • 2,614
  • 5
  • 36
  • 57
13
votes
2 answers

Calling a function that returns a refcursor

I am using Postgresql 8.3 and have the following simple function that will return a refcursor to the client CREATE OR REPLACE FUNCTION function_1() RETURNS refcursor AS $$ DECLARE ref_cursor REFCURSOR; BEGIN OPEN ref_cursor FOR…
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
11
votes
2 answers

How to see refcursor result/output in Oracle SQL Developer?

Possible Duplicate: Best way/tool to get the results from an oracle package procedure Oracle SQL Developer: Show REFCURSOR Results in Grid? I am new to Oracle SQL Developer. I am using Oracle SQL Developer Version 3.0. I was trying to test my SP…
Prabin Yovan
  • 164
  • 1
  • 3
  • 14
10
votes
1 answer

How to close a returning cursor in PL/SQL?

I am new to PL/SQL and I just got to cursors in my learning process. I've been seeing stored procedure parameters with type OUT SYS_REFCURSOR which, as far as I understand, its purpose is to 'return data' just like a pointer in C language. I been…
Y_Y
  • 1,259
  • 5
  • 26
  • 43
8
votes
2 answers

How to fetch Oracle reference cursor into table variable?

I am trying to load data from reference cursor into a table variable (or array), the reference cursor works if the table variable is based on existingtable %Rowtype but my reference cursor gets loaded by joining multiple tables so let me try to…
sam
  • 345
  • 2
  • 4
  • 18
7
votes
2 answers

How to return a empty cursor from a stored procedure?

I have OUT parameter of a stored procedure as a REF CURSOR. Based on a particular condition, I would want to either return a result set (already implemented). But how do I return an empty cursor when the condition fails? Without raising an…
KeenUser
  • 5,305
  • 14
  • 41
  • 62
6
votes
1 answer

How to call an Oracle function with a Ref Cursor as Out-parameter from C#?

I'm using a product that provides a database API based on Oracle functions and I'm able to call functions via ODP.NET in general. However, I can't figure out, how to call a function that includes a Ref Cursor as Out-parameter. All the samples I…
Martin Klinke
  • 7,294
  • 5
  • 42
  • 64
6
votes
2 answers

How to call a stored procedure using a ref cursor in Oracle with squirrel

I'm trying to do the same request I'm using in Toad (the stored procedure 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…
rapdum
  • 323
  • 1
  • 2
  • 7
4
votes
2 answers

Column names in an empty Oracle REF CURSOR

In PL/SQL, I can use a trick like this one here to find out name/value pairs for every column in a row from a REF CURSOR: TO_CHAR of an Oracle PL/SQL TABLE type That's an awesome trick. But it doesn't work when the REF CURSOR is empty, such as this…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
1 answer

CURSOR and REF CURSOR as a JDBC data type

Many RDBMS support "CURSOR" types of some sort. Those types are mostly useful when returned from stored procedures. An example in Oracle: TYPE t_cursor_type IS REF CURSOR; CREATE PROCEDURE p (c OUT t_cursor_type); When calling this procedure using…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
2 answers

Use text output from a function as new query

In continuing from a previous case that was assisted by @Erwin Brandstetter and @Craig Ringer, I have fixed my code to become as follows. Note, that my function myresult() outputs now text, and not a table (as indeed, as was pointed out in the…
Roy
  • 723
  • 2
  • 8
  • 21
4
votes
2 answers

Returning a ref cursor from a Oracle Function

I am getting the error - PLS-00382 Expression is of wrong type. I want to get the ref cursor as output. please let me know how can I do this create or replace function test_cur return sys_refcursor as var_ref sys_refcursor; begin open var_ref…
redsoxlost
  • 1,215
  • 5
  • 19
  • 32
3
votes
1 answer

How to use record to loop a ref cursor?

I want to write PL/SQL to test a function in a package. The package defines a cursor type TYPE ref_cursor IS REF CURSOR; I want to define a record based on that type. My code is: DECLARE cur PACKAGE_NAME.ref_cursor; rec cur%ROWTYPE; why is…
Alex Tang
  • 115
  • 1
  • 4
  • 12
3
votes
3 answers

Using a ref cursor as input type with ODP.NET

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.…
CMPerez
  • 905
  • 1
  • 10
  • 25
3
votes
2 answers

Is having RefCursor as an OUT parameter of a Postgresql function, allowed?

I know the following is possible. i.e I can have a ref cursor as a return value in Postgresql. CREATE FUNCTION employeefunc(int) RETURNS refcursor AS ' DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT * FROM employee where id = $1; RETURN…
Manuri Perera
  • 534
  • 6
  • 16
1
2 3
…
8 9