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_DATE%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,'YYYYMMDD');
begin
       cur_date:=to_char(current_date,'YYYYMMDD');
       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/YYYY');
             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("PARAM1", adDBDate, adParamInput, , Format(Date, "MM/DD/YYYY Hh:M:SS   AMPM"))
    objCmd.Parameters.Append parm
    Set parm = objCmd.CreateParameter("PARAM2", adVarChar, adParamOutput, 13, "")
    objCmd.Parameters.Append parm


    objConn.Open "dsn=dsnPostman;uid=postman;pwd=postman"
    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-06550: 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.

nasarudinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leonstrykerCommented:
nasarudin,

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
0
Dang123Commented:
Learning.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

skaykayCommented:
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!
0
hatfullof12Commented:
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_DATE%TYPE
)

Not specifying this might explain why the error message says "wrong number or types of arguments in call to 'GENERATE_NUM'"
Good luck.
0
leonstrykerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.