Solved

How to call oracle stored function from visual basic

Posted on 2003-12-09
7
625 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
Comment Utility
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
Learning.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:skaykay
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

13 Experts available now in Live!

Get 1:1 Help Now