selecting multiple columns in pl/sql collection

Actually i have created following procedure,which is working fine.

CREATE or REPLACE PROCEDURE GET_NOS(开发者_开发问答
firstDate IN DATE,
secondDate IN DATE,
thirdDate IN DATE,
fourthDate IN DATE,
test IN VARCHAR2,
Slnt_Entity OUT TEST.RefCsr
)
AS
 DemoTable CRITERIA_LIST_TABLE;
 BEGIN


SELECT column1 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
MINUS
SELECT column1 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;


OPEN Slnt_Entity FOR SELECT * FROM TABLE(
                                            CAST (
                                                    DemoTable AS CRITERIA_LIST_TABLE
                                                 )
                                    ) Nos;

END;
/

2. second

create or replace TYPE "CRITERIA_LIST_TABLE" as table of varchar2(20);
/

1. Third

   create or replace PACKAGE "TEST"
   AS
   TYPE RefCsr IS REF CURSOR;
   END TEST;
    /
   

   Now i want to change my query like this

   SELECT column1,column2 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
   MINUS
   SELECT column1,column2 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;
   

so i changed the procedure like

CREATE or REPLACE PROCEDURE GET_NOS(
firstDate IN DATE,
secondDate IN DATE,
thirdDate IN DATE,
fourthDate IN DATE,
test IN VARCHAR2,
Slnt_Entity OUT TEST.RefCsr
 )
AS

CURSOR c1 IS SELECT column1,column2 FROM opr_test;
  create or replace TYPE "ABC" IS TABLE OF c1%ROWTYPE;
DemoTable ABC;

BEGIN


 SELECT column1 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
 MINUS
 SELECT column1 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;


  OPEN Slnt_Entity FOR SELECT * FROM TABLE(
                                            CAST (
                                                    DemoTable AS CRITERIA_LIST_TABLE
                                                 )
                                    ) Nos;

  END;
  /

But this is incorrect,please tell me how the procedure will look like

user595014, to help you with the problem you are having and have several questions open on StackOverflow about. Read this article from ORACLE-BASE about creating and returning oracle REF_CURSOR types.

If you read it all it also gives you a demo on how to return a ref_cursor to a calling Java program (something you have asked me about in a previous question).

It will give you everything you need to sort out your issue.

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php