Calling a Oracle stored procedure from VB6

Posted on 2010-03-30
Medium Priority
Last Modified: 2013-12-07

I am desperately searching for a solution to call a Oracle stored procedure that’s has only one parameter. I search the site to find a solution and what came up I tried but no success.

The message that I get is

[Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
PLS-00201: identifier ‘MELDPRINTOPDRACHTAF’ must be declared
ORA-06550: line 1, column 7:
PS/SQL: Statement ignored

But what do I wrong? What do I have to declare? The connection to the Oracle database is OK. Here the code that I use and I hope that you can point me in the right direction.

    Dim cn As ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParameter As New ADODB.Parameter
    Dim objRs As New ADODB.recordset

    Dim SetPSkey    As String
    SetPSkey = lvwOpdrachten.SelectedItem.SubItems(4)
    Set cn = New ADODB.Connection
    cn.Open SetDBconnectstring
    objParameter.Direction = adParamInput
    objParameter.Type = adVarChar
    objParameter.Size = 10
    objParameter.Value = SetPSkey
    objCmd.Parameters.Append objParameter
    objCmd.ActiveConnection = cn
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "meldPrintOpdrachtAf"
    Set objRs = objCmd.Execute                       ' objCmd.Execute for no resultset
    Set objRs.ActiveConnection = Nothing
    Set objCmd = Nothing

Many thanks in advance
The Netherlands

Question by:tmnin
  • 2
  • 2
LVL 22

Expert Comment

by:Helena Marková
ID: 29076574
Is MELDPRINTOPDRACHTAF the name of a stored procedure ? If yes then if you call it from SQL*Plus (or similar tool) is it executed ?

Author Comment

ID: 29078605

Yes MELDPRINTOPDRACHTAF is the procedure. It needs one parameter which for example is KST106171. I am not so familiar with Oracle but I have started SqlPlus and executed the procedure with and without the parameter like MELDPRINTOPDRACHTAF (KST106171).

In then first situation, only the procedure name I get the error message MELDPRINTOPDRACHTAF must be declared.

When I try MELDPRINTOPDRACHTAF (KST106171) I get the error message KST106171 must be declared.

I probaly do something wrong or the procedure is incorrect. I hereby send you also the strored procedure.

create or replace procedure meldPrintOpdrachtAf
(pskey in el_opdracht.PSKEY%TYPE) IS
update el_opdracht
set folioRealisatieDatum = current_date
pskey = pskey and
ingetrokken is null and
folioRealisatieDatum is null;

I hope this helps to solve the problem
Many thanks
LVL 22

Accepted Solution

Helena Marková earned 1500 total points
ID: 29079332
The procedure is wrong, because there is "current_date" and it is not declared. I think that the status of this procedure is invalid.

create or replace procedure meldPrintOpdrachtAf
(pskey in el_opdracht.PSKEY%TYPE) IS
update el_opdracht
set folioRealisatieDatum = current_date -- current_date is not declared
pskey = pskey and
ingetrokken is null and
folioRealisatieDatum is null;

Also, don't use this:
pskey = pskey
because it is rather confusion.

so your procedure can look like this:

create or replace procedure meldPrintOpdrachtAf
(pskey in el_opdracht.PSKEY%TYPE) IS
current_date  DATE:=sysdate; -- here is a declaration + it is set to sysdate
update el_opdracht  t
set t.folioRealisatieDatum = current_date
t.pskey = pskey and
t.ingetrokken is null and
t.folioRealisatieDatum is null;

Author Closing Comment

ID: 31708823
Solution provided solved my problem.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

607 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