Call the Stored Procedure returned result set in the Oracle package

In actual project development, we need to use VB (or other language tools) to call Oracle Program The returned result set of the stored procedure in the package. Here, a call from the SMS operation platform is used as an example to describe this process. I hope it will help you.

-- 1. Use SQL * Plus to create the following projects:
-- 1. Create a table ("ow_smp" is the solution name, the same below)

Create Table "ow_smp ". "sm_send_sm_list" (
serialno int primary key, -- serial number
serviceid varchar (50), -- service ID (business type)
smcontent varchar (1000 ), -- text message content
sendtarget varchar (20), -- sending target
priority smallint, -- sending priority
rcompletetimebegin date, -- required completion date (start)
rcompletetimeend date, -- required completion date (end)
rcompletehourbegin smallint, -- required completion time (start)
rcompletehourend smallint, -- required completion time (end)
requesttime date, -- send request time
roadby smallint, -- send channel (0: GSM module, 1:

SMS gateway)
Sendtargetdesc varchar (100), -- Description of the sending target
Feevalue float, -- text message fee (

Unit: minute)
Pad1 varchar (50 ),
Pad2 varchar (100 ),
Pad3 varchar (200 ),
Pad4 varchar (500 ),
Pad5 varchar (1000)
);
-- 2. Create an auto-incrementing Sequence
Create sequence "ow_smp". "sendsno ";
Create or replace trigger "ow_smp". "bfinert_sm_send" before
Insert on "sm_send_sm_list"
For each row begin
Select sendsno. nextval into: New. serialno from dual;
End;
-- 3. insert data
Insert sm_send_sm_list (smcontent) values (Happy New Year to jakcy !);
Insert sm_send_sm_list (smcontent) values (Happy New Year to wxl !);
-- 4. Create a package and package body

Create or replace package "ow_smp". "ow_smp_package"
Is
Type tserialno is table of sm_send_sm_list.serialno % Type
Index by binary_integer;
Type tserviceid is table of sm_send_sm_list.serviceid % Type
Index by binary_integer;
Type tsmcontent is table of sm_send_sm_list.smcontent % Type
Index by binary_integer;
Type tsendtarget is table of sm_send_sm_list.sendtarget % Type
Index by binary_integer;
Type tpriority is table of sm_send_sm_list.priority % Type
Index by binary_integer;
Type trcompletetimebegin is table of sm_send_sm_list.rcompletetimebegin % Type
Index by binary_integer;
Type trcompletetimeend is table of sm_send_sm_list.rcompletetimeend % Type
Index by binary_integer;
Type trcompletehourbegin is table of sm_send_sm_list.rcompletehourbegin % Type
Index by binary_integer;
Type trcompletehourend is table of sm_send_sm_list.rcompletehourend % Type
Index by binary_integer;
Type trequesttime is table of sm_send_sm_list.requesttime % Type
Index by binary_integer;
Type troadby is table of sm_send_sm_list.roadby % Type
Index by binary_integer;
Type tsendtargetdesc is table of sm_send_sm_list.sendtargetdesc % Type
Index by binary_integer;
Type tfeevalue is table of sm_send_sm_list.feevalue % Type
Index by binary_integer;
Type tpad1 is table of sm_send_sm_list.pad1 % Type
Index by binary_integer;
Type tpad2 is table of sm_send_sm_list.pad2 % Type
Index by binary_integer;
Type tpad3 is table of sm_send_sm_list.pad3 % Type
Index by binary_integer;
Type tpad4 is table of sm_send_sm_list.pad4 % Type
Index by binary_integer;
Type tpad5 is table of sm_send_sm_list.pad5 % Type
Index by binary_integer;
Type tcount is table of number
Index by binary_integer;

Procedure getsendsm
(V_nowbyminute in number,
V_serialno out tserialno,
V_serviceid out tserviceid,
V_smcontent out tsmcontent,
V_sendtarget out tsendtarget,
V_priority out tpriority,
V_rcompletetimebegin out trcompletetimebegin,
V_rcompletetimeend out trcompletetimeend,
V_rcompletehourbegin out trcompletehourbegin,
V_rcompletehourend out trcompletehourend,
V_requesttime out trequesttime,
V_roadby out troadby,
V_sendtargetdesc out tsendtargetdesc,
V_feevalue out tfeevalue,
V_pad1 out tpad1,
V_pad2 out tpad2,
V_pad3 out tpad3,
V_pad4 out tpad4,
V_pad5 out tpad5,
V_count out tcount
);

End;
/
Create or replace package body "ow_smp". "ow_smp_package"
Is
Procedure getsendsm -- get the first 1000 messages to be sent within the specified time
(V_nowbyminute in number,
V_serialno out tserialno,
V_serviceid out tserviceid,
V_smcontent out tsmcontent,
V_sendtarget out tsendtarget,
V_priority out tpriority,
V_rcompletetimebegin out trcompletetimebegin,
V_rcompletetimeend out trcompletetimeend,
V_rcompletehourbegin out trcompletehourbegin,
V_rcompletehourend out trcompletehourend,
V_requesttime out trequesttime,
V_roadby out troadby,
V_sendtargetdesc out tsendtargetdesc,
V_feevalue out tfeevalue,
V_pad1 out tpad1,
V_pad2 out tpad2,
V_pad3 out tpad3,
V_pad4 out tpad4,
V_pad5 out tpad5,
V_count out tcount)

Is
Cursor sendsm_cur is
Select * From sm_send_sm_list
Where rcompletehourbegin <= v_nowbyminute and

Rcompletehourend> = v_nowbyminute and (rcompletetimebegin is null or

Rcompletetimebegin <= sysdate)
And (rcompletetimeend is null or rcompletetimeend >=sysdate-1)
And rownum: <1001;

Smcount number default 1;
Begin
For sm in sendsm_cur
Loop
V_serialno (smcount): = Sm. serialno;
V_serviceid (smcount): = Sm. serviceid;
V_smcontent (smcount): = Sm. smcontent;
V_sendtarget (smcount): = Sm. sendtarget;
V_priority (smcount): = Sm. priority;
V_rcompletetimebegin (smcount): = Sm. rcompletetimebegin;
V_rcompletetimeend (smcount): = Sm. rcompletetimeend;
V_rcompletehourbegin (smcount): = Sm. rcompletehourbegin;
V_rcompletehourend (smcount): = Sm. rcompletehourend;
V_requesttime (smcount): = Sm. requesttime;
V_roadby (smcount): = Sm. roadby;
V_sendtargetdesc (smcount): = Sm. sendtargetdesc;
V_feevalue (smcount): = Sm. feevalue;
V_pad1 (smcount): = Sm. pad1;
V_pad2 (smcount): = Sm. pad2;
V_pad3 (smcount): = Sm. pad3;
V_pad4 (smcount): = Sm. pad4;
V_pad5 (smcount): = Sm. pad5;
If smcount = 1 then
Select count (*)
Into v_count (smcount)
From sm_send_sm_list
Where rcompletehourbegin <= v_nowbyminute and

Rcompletehourend> = v_nowbyminute and (rcompletetimebegin is null or

Rcompletetimebegin <= sysdate)
And (rcompletetimeend is null or rcompletetimeend >=sysdate-1)
And rownum: <1001;
End if;
Smcount: = smcount + 1;
End loop;
End;
End;
/

2. Use VB to call the ow_smp_package.getsendsm stored procedure:

Sub getsendsm ()
Dim cmd as new ADODB. Command
Dim RS as new ADODB. recordset
Cmd. activeconnection = getconnection get database connection
Cmd. commandtext = "{call ow_smp_package.getsendsm (?, {Resultset

1000, v_serialno, v_serviceid, v_smcontent, v_sendtarget, v_priority, v_rcompletetimebegin, v_rcomp

Letetimeend, v_rcompletehourbegin, v_rcompletehourend, v_requesttime, v_roadby, v_sendtargetdesc,

V_feevalue, v_pad1, v_pad2, v_pad3, v_pad4, v_pad5, v_count })}"
Cmd. commandtype = ad1_text
Cmd. Parameters. append. createparameter ("v_nowbyminute", adinteger, adparaminput, 900)

Rs. cursortype = adopenstatic
Rs. locktype = adlockreadonly
Set Rs. Source = cmd
Rs. Open
While not Rs. EOF
Msgbox "sendsm data: serialno:" & RS ("v_serialno") & ", smcontent:" & RS

("v_smcontent") & ", Count:" & RS ("v_count ")
added Code
Rs. movenext
Wend
Rs. close
set rs = nothing
set cmd = nothing
end sub