1 person found this helpful
Not sure , Are u looking for get the number of rows from table like this...
SQL> CREATE OR REPLACE PROCEDURE my_pro (ov_count OUT sys_refcursor)
4 OPEN ov_count FOR
5 SELECT COUNT (*)
6 FROM emp;
SQL> var x refcursor;
SQL> execute my_pro(:x);
PL/SQL procedure successfully completed.
SQL> print x;
Just fetch them all and at the end your_refcursor%ROWCOUNT will give you the count. But if you want to know the count without fetching from it, ITS NOT POSSIBLE!!
That is because the OPEN operation of a cursor does not execute the instructions in the cursor work area, only FETCH does it. So you need to FETCH all the rows to know the total number of rows.
create or replace(...,p_ref_cur out sys_refcursor)as
l_str := '.....'
execute immediate l_str using out p_ref_cur
but records fetched morethan 1
> execute immediate l_str using out p_ref_cur
This is a incorrect syntax. You cant use refcursor like this.
You can't Use Refcursor in Execute Immediate Statement,
Execute As shown if you dont want execute in Sql*plus,
EXIT WHEN rc%NOTFOUND;
how to count the rows in refcursor?
A ref cursor is an interface (or pointer) to a SQL cursor in the database's memory.
A SQL cursor is a program. Not a result set (set of data results in memory).
Question. How many rows do the SQL cursor return (SQL cursor program output)?
Answer. The caller need to consume (fetch) all the output (until no more data rows returned by cursor) to determine how many rows were returned by the cursor.