Oracle fetch cursor into cursor

Question

I just want to know how it is possible to fetch a cursor into another one. I have the following Package:

create or replace
PACKAGE Matching AS 
  type Cursor_Return is ref Cursor;
    Procedure Get_Data
      (Cus_ID in Varchar2,
       Cursor_back OUT Cursor_Return,
       Cursor_back2 OUT Cursor_Return);
END BARCODEMATCHING;

create or replace
PACKAGE BODY Matching AS

  Procedure Matching_Proc
      (Cus_ID in Varchar2,
       Cursor_back OUT Cursor_Return,
       Cursor_back2 OUT Cursor_Return
       ) AS
  BEGIN
    Open Cursor_back for 'Select * from cus.customerHead where CustomerID = ' || cus_Id;
    Open Cursor_back2 for 'Select Cus_Location, Cus_zipcode from cus.customerBody where = CustomerID = ' || cus_ID;
    Fetch Cursor_back2 into Cursor_back;
END Matching_Proc;


END Matching;

This is my code so far. I only need to return the Cursor: 'Cursor_back'. When I try to run this code I get the error: ORA-06512: Missing Expression. Is there a way how to solve this? Can I declare my two Colums, which I want to Hand over to Cursor_back2 in another way? I simply would like to return the Cursor_back with two (max four) columns so I have an Output like:

cus.customerbody.cus_location | cus.customerbody.cus_zipcode | cus.customerhead.cus_id | cus.customerhead.cus_Name | and so on

Thanks in advance.

Answer 1

You're getting the "ORA-06512: Missing Expression" error because you have an extra = sign in this line:

Open Cursor_back2 for 'Select Cus_Location, Cus_zipcode from cus.customerBody where = CustomerID = ' || cus_ID;

It should be where = CustomerID =, not where = CustomerID =. The cursor statements don't need to be dynamic though, you can use:

Open Cursor_back for
  Select * from cus.customerHead where CustomerID = cus_Id;
Open Cursor_back2 for
  Select Cus_Location, Cus_zipcode from cus.customerBody where CustomerID = cus_ID;

If you stick with the dynamic version, in which the queries can't be syntax-checked until run time, then since you pass cus_ID as a string you probably need to enclose that in escaped single quotes as part of the dynamic SQL statement. But don't use dynamic SQL unless you really have to.

You don't really want two cursors though, since you're trying to combine values from two related queries. You need a join between the tables, and a single out parameter cursor, something like:

Open Cursor_back for
  Select cb.cus_location, cb.cus_zipcode, ch.cus_id, ch.cus_Name
  from cus.customerHead ch
  join cus.customerBody cb
  on cb.customerID = ch.customerID
  where ch.CustomerID =  cus_Id;