tmnin
asked on
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-065
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
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
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-065
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
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
Helena Marková
Is MELDPRINTOPDRACHTAF the name of a stored procedure ? If yes then if you call it from SQL*Plus (or similar tool) is it executed ?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Solution provided solved my problem.