Stack Overflow
Stack Overflow

Questions tagged [sys-refcursor]

A predefined Oracle cursor variable type.

A SYS_REFCURSOR is a predefined cursor variable type. It is weakly defined, meaning that, according to the documentation, it is both more error-prone and more flexible than a strongly defined cursor.

Further Reading:

173 questions
20
votes
5 answers

How can I use Oracle SQL developer to run stored procedures?

* EDIT6: * This is what ended up working for me (from accepted answer): var ret1 number var tran_cnt number var msg_cnt number var rc refcursor exec :tran_cnt := 0 exec :msg_cnt := 123 exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt,…
Hamish Grubijan
  • 10,562
  • 23
  • 99
  • 147
13
votes
6 answers

How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA

I have an Oracle function which return sys-refcursor and when I call this function using Hibernate, I am getting the following exception. Hibernate: { ? = call my_function(?) } org.hibernate.exception.GenericJDBCException: could not execute…
Jacob
  • 14,463
  • 65
  • 207
  • 320
11
votes
1 answer

Oracle EXECUTE IMMEDIATE into a cursor

I have a stored procedure which used the EXECUTE IMMEDIATE command to execute a very long string. How do I support a very long string and return the data into a refcursor?
Ankit
  • 6,388
  • 8
  • 54
  • 79
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
10
votes
2 answers

How to display a sys_refcursor data in TOAD's DataGrid

Please i need help. (I SEARCHED A lot and get more confused . ) I use Toad 9.7.25 and i made this procedure (in a package) PROCEDURE ReportaCC(pfcorte IN DATE, lcursor IN OUT SYS_REFCURSOR) IS BEGIN OPEN lcursor FOR select c1, c3, c3…
Edgar Fco Delgado Zamarripa
  • 190
  • 1
  • 2
  • 11
9
votes
1 answer

How to return a RefCursor from Oracle function?

I am trying to execute a user-defined Oracle function that returns a RefCursor using ODP.NET. Here is the function: CREATE OR REPLACE FUNCTION PKG.FUNC_TEST (ID IN TABLE.ID%type) RETURN SYS_REFCURSOR AS REF_TEST SYS_REFCURSOR; BEGIN OPEN…
Gady
  • 4,935
  • 8
  • 38
  • 48
9
votes
1 answer

how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR?

W.r.t code below I can not declare the type of fetch-into-variable as the underlying table's %ROWTYPE because the SYS_REFCURSOR is on a select that joins two tables and also selects a few functions called on the attributes of the underlying two…
Vishal Saxena
  • 137
  • 2
  • 2
  • 6
8
votes
2 answers

How to create an interactive report using pl/sql refcursor

I need to create an interactive report but instead of running a sql statement I need to run a pl/sql statement ----------------------------------------------------------------------------- USER_ID Name 1 java 2 php 3 pl/sql /…
user3057514
  • 261
  • 3
  • 12
7
votes
3 answers

Reading ref cursor as output parameter in a stored procedure with spring data jpa returns null

I'm trying to read a refcursor in an oracle stored procedure using spring data jpa and spring boot, the stored procedure runs succesfully but the reference to the returned List is always null. I tried using Hibernate as a JPA provider and Eclipse…
okor
  • 71
  • 1
  • 1
  • 3
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
4 answers

How to print the cursor values in oracle?

I am new to oracle and I am learning cursors.My table City has two columns city_id,city_name.So,this is what I tried: DECLARE CURSOR city_list is SELECT * from OT.City; v_list SYS_REFCURSOR; BEGIN OPEN city_list FOR v_list := city_list; …
Random guy
  • 883
  • 3
  • 11
  • 32
6
votes
2 answers

Function return sys_refcursor call from sql with specific columns

This may find little silly, but I would like to know whether this is possible. I have a function which return sys_refcursor CREATE OR REPLACE FUNCTION get_employee_details(p_emp_no IN EMP.EMPNO%TYPE) RETURN SYS_REFCURSOR AS o_cursor …
Jacob
  • 14,463
  • 65
  • 207
  • 320
5
votes
5 answers

Best way to check if SYS_REFCURSOR is empty

I have a cursor with values from a select and i want to do something after depending if i had any row found or none. recs_Table SYS_REFCURSOR; begin open recs_Table for select * from table1, table2; if recs_Table%found then …
Hélder Gonçalves
  • 3,822
  • 13
  • 38
  • 63
4
votes
2 answers

Ref cursor with Execute immediate

I want to get the results in ref_cursor, but I am not able to do that. Please suggest me how to get the results in ref_cursor using Execute immediate CREATE OR REPLACE PROCEDURE TEST_PROC_QT ( p_name IN VARCHAR2, …
Naveen Chakravarthy
  • 819
  • 2
  • 15
  • 30
4
votes
1 answer

Returning a cursor from an inner procedure to outer procedure in oracle pl/sql

I am using oracle PL/SQL procedure. I am calling one procedure inside another. I want to return a cursor from the nested procedure to the outer procedure. Is this possible? How adversely does it affect the procedure? Below is the calling structure: …
Tito
  • 8,894
  • 12
  • 52
  • 86
1
2 3
…
11 12