Use VB to invoke a stored procedure within an Oracle package to return a result set

oracle| Program | stored procedure
In the actual project development we need to use VB (or other language tools) to call the Oracle package of stored procedures to return the result set. Here is an example of a call in the SMS operating platform to illustrate this process, I hope to help you.

--one. Use Sql*plus to create the following items:
--1. Table ("OW_SMP" is the name of the scheme, hereinafter)

CREATE TABLE "OW_SMP". Sm_send_sm_list "(
Serialno INT PRIMARY KEY,--Serial number
Serviceid VARCHAR (50),--Service ID (business type)
Smcontent VARCHAR (1000),--SMS content
Sendtarget VARCHAR (20),--Send target
Priority SMALLINT,--Send priority
Rcompletetimebegin date,--Request finish (start)
Rcompletetimeend date,--Request finish (end)
Rcompletehourbegin SMALLINT,--Request 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),--Send target description
Feevalue FLOAT,--This message fee (

Unit: cent)
Pad1 VARCHAR (50),
Pad2 VARCHAR (100),
Pad3 VARCHAR (200),
Pad4 VARCHAR (500),
Pad5 VARCHAR (1000)
);
--2. Establish a self-adding 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. Inserting 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. Set up packages and packages

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 you want to send within a 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
/

Two. Use VB to invoke 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 = adCmdText
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")
' Processing of the result set adds code here
Rs.movenext
Wend
Rs.close
Set rs=nothing
Set cmd=nothing
End Sub