Calling a Oracle stored procedure from VB6

Hi,

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.

    OpenODBCconnection
   
    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
   
    CloseODBCconnection

Many thanks in advance
Tim
The Netherlands


tmninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
Is MELDPRINTOPDRACHTAF the name of a stored procedure ? If yes then if you call it from SQL*Plus (or similar tool) is it executed ?
0
tmninAuthor Commented:
Hi

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
begin
update el_opdracht
set folioRealisatieDatum = current_date
where
pskey = pskey and
ingetrokken is null and
folioRealisatieDatum is null;
end;

I hope this helps to solve the problem
Many thanks
Tim
0
Helena Markováprogrammer-analystCommented:
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
begin
update el_opdracht
set folioRealisatieDatum = current_date -- current_date is not declared
where
pskey = pskey and
ingetrokken is null and
folioRealisatieDatum is null;
end;

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
begin
update el_opdracht  t
set t.folioRealisatieDatum = current_date
where
t.pskey = pskey and
t.ingetrokken is null and
t.folioRealisatieDatum is null;
end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmninAuthor Commented:
Solution provided solved my problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.