RE: how to run procedure containig REF CURSOR as datatype

From: Raymond Lee Meng Hong <RAYMOND_at_infopro.com.my>
Date: Wed, 06 Jun 2001 18:53:23 -0700
Message-ID: <F001.0031F875.20010606184028@fatcity.com>

Can I cut in for a while.

After reading revealnet explanation on the REF CURSOR , I still feel confuse on the usage of ref cursor and the actual CURSOR itself , what is the different ?

-----Original Message-----
Sent: Wednesday, June 06, 2001 1:11 AM
To: Multiple recipients of list ORACLE-L

Harvinder,

Here is the package I put together

CREATE OR REPLACE PACKAGE GetRateSchedules_pkg AS

TYPE t_cursor IS REF CURSOR;

PROCEDURE GetRateSchedules( temp_id_acc INT,

                              acc_cycle_id INT,
                              default_pl INT,
                              RecordDate DATE,
                              temp_id_pi_type INT,
                              io_cursor IN OUT t_cursor);

/
CREATE OR REPLACE PACKAGE BODY GetRateSchedules_pkg AS

PROCEDURE GetRateSchedules( temp_id_acc INT,

                              acc_cycle_id INT,
                              default_pl INT,
                              RecordDate DATE,
                              temp_id_pi_type INT,
                              io_cursor IN OUT t_cursor) IS

BEGIN NULL;
dbms_output.put_line('hello'||recorddate); END; END; and the call to it:

DECLARE
io_cursor getrateschedules_pkg.t_cursor;

BEGIN getrateschedules_pkg.getrateschedules(1,2,3,SYSDATE,4,io_cursor);

END; It seems to be working fine. The DBMS_OUTPUT message is giving me the date I passed in.

What version of Oracle you working on?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, June 05, 2001 10:46 AM
To: Multiple recipients of list ORACLE-L

Tom,

I used ur suggestion but i am still getting the same error. I am running the procedure from sqlplus.... Do i have to modify something to run from sqlplus...... ORA-01790: expression must have same datatype as corresponding expression.........

Thanks
Harvinder

-----Original Message-----
Sent: Tuesday, June 05, 2001 9:01 AM
To: Multiple recipients of list ORACLE-L

Harvinder,

change your script like this:

declare
io_cursor getrateschedules_pkg.t_cursor; <== change the reference to

                                                        the cursor declared
in the
                                                        package body.

begin
getrateschedules_pkg.getrateschedules(1,2,3,sysdate,4,io_cursor); end;
/

we do this all the time for ref cursors used by VB.

hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, June 04, 2001 7:36 PM
To: Multiple recipients of list ORACLE-L

Hi,

What value to pass as io_cursor in the following procedure....How to run the procedures containing REF CURSOR..........

create or replace package GetRateSchedules_pkg as

         TYPE t_cursor is REF CURSOR;
         procedure GetRateSchedules( temp_id_acc int,acc_cycle_id

int,default_pl int,RecordDate date,temp_id_pi_type int,io_cursor in out t_cursor);

end;

i am getting error when i run the following code:

declare
TYPE t_cursor is REF CURSOR;
io_cursor t_cursor;
begin
getrateschedules_pkg.getrateschedules(1,2,3,sysdate,4,io_cursor); end;
/

ORA-01790: expression must have same datatype as corresponding expression.........

Thanks
Harvinder
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Harvinder Singh
INET: Harvinder.Singh_at_MetraTech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Harvinder Singh
INET: Harvinder.Singh_at_MetraTech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Raymond Lee Meng Hong
INET: RAYMOND_at_infopro.com.my

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------