nasarudin
asked on
How to call oracle stored function from visual basic
Dear experts,
I am trying to call oracle stored function from vb6.
Here is oracle function code:
CREATE OR REPLACE FUNCTION "GENERATE_NUM" (
current_date tbl_message.FLD_CREATE_DAT E%TYPE
)
return varchar is
num number;
id varchar(13);
today_date varchar(8);
cur_date varchar(8);
temp varchar(20);
cursor runnum is
select to_char(fld_date,'YYYYMMDD ')
from tbl_runnum
where to_char(fld_date,'YYYYMMDD ') = to_char(current_date,'YYYY MMDD');
begin
cur_date:=to_char(current_ date,'YYYY MMDD');
open runnum;
fetch runnum into today_date;
if runnum%found then
select fld_num into num
from tbl_runnum;
num:=num+1;
update tbl_runnum
set fld_num=num;
else
num:=1;
temp:=to_char(current_date ,'MM/DD/YY YY');
update tbl_runnum
set fld_date = to_date(temp,'MM/DD/YYYY') ,
fld_num = num;
today_date := cur_date;
end if;
close runnum;
id:=today_date || lpad(to_char(num),5,'0');
return id;
end;
/
This is my vb code:
Dim objConn As New ADODB.Connection
Dim objCmd As ADODB.Command
Dim parm As ADODB.Parameter
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "POSTMAN.GENERATE_NUM"
Set parm = objCmd.CreateParameter("PA RAM1", adDBDate, adParamInput, , Format(Date, "MM/DD/YYYY Hh:M:SS AMPM"))
objCmd.Parameters.Append parm
Set parm = objCmd.CreateParameter("PA RAM2", adVarChar, adParamOutput, 13, "")
objCmd.Parameters.Append parm
objConn.Open "dsn=dsnPostman;uid=postma n;pwd=post man"
objCmd.ActiveConnection = objConn
objCmd.Execute
Dim this As String
this = objCmd.Parameters("PARAM2" ).Value
MsgBox "result " & this
But when I run the code I got the following error:
[oracle][odbc][ora]ORA-065 50: line 1, column 7:
PLS-00306:wrong number or types of arguments in call to 'GENERATE_NUM'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Could anyone please show me the right way to call oracle stored function from vb?
Please help.
I am trying to call oracle stored function from vb6.
Here is oracle function code:
CREATE OR REPLACE FUNCTION "GENERATE_NUM" (
current_date tbl_message.FLD_CREATE_DAT
)
return varchar is
num number;
id varchar(13);
today_date varchar(8);
cur_date varchar(8);
temp varchar(20);
cursor runnum is
select to_char(fld_date,'YYYYMMDD
from tbl_runnum
where to_char(fld_date,'YYYYMMDD
begin
cur_date:=to_char(current_
open runnum;
fetch runnum into today_date;
if runnum%found then
select fld_num into num
from tbl_runnum;
num:=num+1;
update tbl_runnum
set fld_num=num;
else
num:=1;
temp:=to_char(current_date
update tbl_runnum
set fld_date = to_date(temp,'MM/DD/YYYY')
fld_num = num;
today_date := cur_date;
end if;
close runnum;
id:=today_date || lpad(to_char(num),5,'0');
return id;
end;
/
This is my vb code:
Dim objConn As New ADODB.Connection
Dim objCmd As ADODB.Command
Dim parm As ADODB.Parameter
Set objCmd = New ADODB.Command
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "POSTMAN.GENERATE_NUM"
Set parm = objCmd.CreateParameter("PA
objCmd.Parameters.Append parm
Set parm = objCmd.CreateParameter("PA
objCmd.Parameters.Append parm
objConn.Open "dsn=dsnPostman;uid=postma
objCmd.ActiveConnection = objConn
objCmd.Execute
Dim this As String
this = objCmd.Parameters("PARAM2"
MsgBox "result " & this
But when I run the code I got the following error:
[oracle][odbc][ora]ORA-065
PLS-00306:wrong number or types of arguments in call to 'GENERATE_NUM'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Could anyone please show me the right way to call oracle stored function from vb?
Please help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Learning.
http://www.vb2themax.com/KBBank.asp?Cat=900&DocType=HOWTO
This is the link to the complete MS Knowledge base link for interacting with Oracle from VB using ADO/ RDO. Hope this helps.
skaykay!
This is the link to the complete MS Knowledge base link for interacting with Oracle from VB using ADO/ RDO. Hope this helps.
skaykay!
I think you have to specify whether your parameter is an "IN" or "OUT" parameter.
Try this as your function definition: (assuming you want to pass "current_date" into the function)
CREATE OR REPLACE FUNCTION "GENERATE_NUM" (
current_date IN tbl_message.FLD_CREATE_DAT E%TYPE
)
Not specifying this might explain why the error message says "wrong number or types of arguments in call to 'GENERATE_NUM'"
Good luck.
Try this as your function definition: (assuming you want to pass "current_date" into the function)
CREATE OR REPLACE FUNCTION "GENERATE_NUM" (
current_date IN tbl_message.FLD_CREATE_DAT
)
Not specifying this might explain why the error message says "wrong number or types of arguments in call to 'GENERATE_NUM'"
Good luck.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Answered leonstryker
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
leonstryker
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Answered leonstryker
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
leonstryker
EE Cleanup Volunteer
Please maintain your open questions.
10/29/2003 55 How jsp could get parameters from perl Open JSP
06/17/2003 50 CREATE DATABASE failed; database already... Open Oracle
06/04/2003 50 How to call XMLDOM using JSP Open JSP
03/23/2003 75 How to open ie browser from netscape bro... Open JavaScript
03/18/2003 100 How to place my jsp file into oracle 9ia... Open JSP
02/13/2003 50 Oracle JOB status failed Open Oracle
01/24/2003 25 ORA-03113: end-of-file on communication ... Open Oracle