Cursor in Procedure Returning More Values Than Query

Cursor in procedure returning more values than query

You have a name conflict. You have called your local variables the same as your column names, and the column names are taking precedence, as noted in the documentation:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

Caution:
When a variable or parameter name is interpreted as a column name, data can be deleted, changed, or inserted unintentionally.

The first four checks are always going to be true (unless you have null values), so you'll get every row that has done = 'N'.

Change your local variable names to something else; it's fairly common to use a prefix to distinguish between local variables, parameters, and columns, something like:

Cursor linija IS 
SELECT *
FROM table_x X
where x.mjt = l_mjt
and   x.salesman = l_salesman
and x.kind = l_kind
and x.kolo1 = l_kolo1
and x.done = 'N';

If this is in a stored procedure, rather than an anonymous block, you could use the procedure/function name as a prefix, which some people prefer. If your procedure was called myproc, for example, you could do:

Cursor linija IS 
SELECT *
FROM table_x X
where x.mjt = myproc.mjt
and   x.salesman = myproc.salesman
and x.kind = myproc.kind
and x.kolo1 = myproc.kolo1
and x.done = 'N';

Return 2 resultset from cursor based on one query (nested cursor)

Each row of data from a cursor can be read exactly once; once the next row (or set of rows) is read from the cursor then the previous row (or set of rows) cannot be returned to and the cursor cannot be re-used. So what you are asking is impossible as if you read the cursor to find the maximum values (ignoring that you can't use a cursor as a source in a SELECT statement but, instead, you could read it using a PL/SQL loop) then the cursor's rows would have been "used up" and the cursor closed so it could not be read from when it is returned from the procedure.

You would need to use two separate queries:

CREATE PROCEDURE MySchema.Test(
  RESULT     OUT SYS_REFCURSOR,
  MAX_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
 OPEN RESULT FOR
   SELECT Name,
          Surname
   FROM   MyTable; 

 OPEN MAX_RESULT FOR
   SELECT MAX(LENGTH(Name))    AS max_name_length,
          MAX(LENGTH(Surname)) AS max_surname_length
   FROM   MyTable;
END Test;
/

Just for theoretical purposes, it is possible to only read from the table once if you bulk collect the data into a collection then select from a table-collection expression (however, it is going to be more complicated to code/maintain and is going to require that the rows from the table are stored in memory [which your DBA might not appreciate if the table is large] and may not be more performant than compared to just querying the table twice as you'll end up with three SELECT statements instead of two).

Something like:

CREATE TYPE test_obj IS OBJECT(
  name    VARCHAR2(50),
  surname VARCHAR2(50)
);

CREATE TYPE test_obj_table IS TABLE OF test_obj;

CREATE PROCEDURE MySchema.Test(
  RESULT     OUT SYS_REFCURSOR,
  MAX_RESULT OUT SYS_REFCURSOR
)
AS
 t_names test_obj_table;
BEGIN
 SELECT Name,
        Surname
 BULK COLLECT INTO t_names
 FROM   MyTable; 

 OPEN RESULT FOR
   SELECT * FROM TABLE( t_names );

 OPEN MAX_RESULT FOR
   SELECT MAX(LENGTH(Name))    AS max_name_length,
          MAX(LENGTH(Surname)) AS max_surname_length
   FROM   TABLE( t_names );
END Test;
/

How to return multiple rows from oracle stored procedure from multiple cursors?

My suggestion is going to be insert the rows from your cursor into a temporary table. Then join the temporary table with your existing table for the filter criteria you mention. Psuedocode:

create or replace function my_func
return sysrefcursor
is
    cursor cursor_one is 
        SELECT * FROM table_one ; 

    cursor cursor_two is 
        SELECT * FROM table_one ; 
    BEGIN    

     FOR current_row in cursor_one
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 

     FOR current_row in cursor_two
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 

    -- results from cursor 1 and 2 exist in temporary table

    open out_cursor for
     select t.* from
      my_temp_table t
      join
      my_other_table tt
      on (t.col1 = tt.col1) -- or whatever columns are appropriate
      where t.col2 = 'some criteria' -- or whatever filter criteria you like.

    return out_cursor;

    END;  

Cursor in Oracle - after passing parameters, select does not filter result rows

Why? Because you named parameter the same as column, so Oracle reads it as if it was where 1 = 1, i.e. no filtering at all.

Rename parameters to e.g.

CURSOR cur_action ( par_product_code V
                    ----
                    this

and, later,

AND    act.PRODUCT_CODE = par_product_code;
                          ----
                          this

Return multiple resultset from a function in postgres using cursor

This SQL code is fine by itself, but your SQL client is probably sending the whole block in one go as a multi-query string. Then if shows only the result of the last instruction of that sequence, which is the result of commit.

If you tried this in in psql (the primary command-line interface for postgresql), it would show results, since psql parses the SQL buffer to identify queries between ; and sends them as separate statements (use \; to group them).

Stored proc using cursor for looping and storing return value into variable

drop procedure if exists sp_test3;
drop table if exists table_b, table_a;

create table if not exists table_a(a1 varchar(255));
create table if not exists table_b(b1 varchar(255));

insert into table_a values ('col2');
insert into table_a values ('col3');
insert into table_a values ('col5');
insert into table_a values ('col1');

insert into table_b values ('col2');
insert into table_b values ('col3');
insert into table_b values ('col4');
insert into table_b values ('col1');

 CREATE PROCEDURE sp_test3()
 BEGIN
 DECLARE DONE, DONE1 INT DEFAULT 0;
 DECLARE col1 varchar(255);
 DECLARE curA CURSOR FOR SELECT a1 FROM table_a;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
 OPEN curA;
 SET @SQL_TXT = '';
    while done = 0 do  
     fetch next from CurA into col1;
     if done = 0 then
     BEGIN
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE1 = 1;
     SET @xxx = CONCAT("select b1 into @yyy 
                       from table_b
                       where b1 = '", col1, "'");
     PREPARE stmt_name FROM @xxx;
     EXECUTE stmt_name;
     DEALLOCATE PREPARE stmt_name;
     if (DONE1 = 0) THEN
       SELECT @yyy;
     ELSE
       SET DONE1 = 0;
     END IF;
     END;
     END IF;
   END WHILE;
  close curA;
 end;

How to return multiple rows with multiple columns using cursor in pl/sql procedure?

You may use a single REFCURSOR out parameter instead of multiple out parameters.

CREATE OR REPLACE PROCEDURE display_users (
    pi_date1          IN DATE,
    pi_date2          IN DATE,
    po_userdisp_cur   OUT SYS_REFCURSOR 
)
    IS
BEGIN
    OPEN po_userdisp_cur FOR SELECT u.enrollno,
                                    u.error_code,
                                    u.enroll_date
                             FROM user_table u
                             WHERE u.enroll_date BETWEEN pi_date1 AND pi_date2;
END;

This can be easily used in java to fetch the records as shown in this link:

Using oracle ref cursors in java

how to return all rows from oracle procedure using multiple cursors

You can use custom data types and pipelined functions.
You create a type for your "record" template:

CREATE OR REPLACE
TYPE O_MY_OBJECT AS OBJECT (
  MY_DATE                       DATE
, MY_NUMBER                     NUMBER
, MY_VARCHAR                    VARCHAR2(20)
);

Then you create a type for a table of your record template:

CREATE OR REPLACE
TYPE T_MY_OBJECT AS TABLE OF O_MY_OBJECT;

And then create a pipelined function that returns your table object:

FUNCTION MY_FUNCTION(V_MY_PARAM IN VARCHAR2)
  RETURN  T_MY_OBJECT
  PIPELINED
AS
V_MY_OBJ_VAR O_MY_OBJECT := O_MY_OBJECT(null, null, null);
BEGIN
      SELECT
            SYSDATE
          , 10
          , 'HELLO WORLD! -> ' || V_MY_PARAM
      INTO
          V_MY_OBJ_VAR.MY_DATE
        , V_MY_OBJ_VAR.MY_NUMBER
        , V_MY_OBJ_VAR.MY_VARCHAR
      FROM
          DUAL;

      PIPE ROW(V_MY_OBJ_VAR);

  RETURN;
END MY_FUNCTION;

In your case you will pipe a row every time you need it from each of the five cursors' loops.
Once you've created such function you can get records from it with a table query:

SELECT * FROM TABLE(MY_FUNCTION('SOME ARGUMENT VALUE, IF NEEDED'));

---

Related Topics