Solved

How to call oracle stored function from visual basic

Posted on 2003-12-09
7
639 Views
Last Modified: 2013-12-25
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.

0
Comment
Question by:nasarudin
7 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 160 total points
ID: 9909903
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9909925
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
 
LVL 9

Expert Comment

by:Dang123
ID: 9920340
Learning.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:skaykay
ID: 9962561
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
 

Expert Comment

by:hatfullof12
ID: 10180458
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11121134
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now