Solved

How to call oracle stored function from visual basic

Posted on 2003-12-09
7
676 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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