Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to call oracle stored function from visual basic

Posted on 2003-12-09
7
Medium Priority
?
700 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 640 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
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…
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…
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…
Suggested Courses

604 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