KB-97376: Error when executing Stored Procedure in Oracle DB.

Question:

How can I work around a syntax error in a stored procedure in an Oracle database?

I developed a stored procedure in an Oracle 11g databse which returns an OUTPUT parameter. Therefore, in SQL Developer, this is how the stored procedure is executed:

VARIABLE P_TOTAL VARCHAR2(200);
execute calcula_valores(10,:P_TOTAL);
print :P_TOTAL;

Then, the following result is displayed:

Soon after, the E3Query executes the stored procedure like in the example below:

VARIABLE P_TOTAL VARCHAR(200)
Call CALCULA_VALORES(10, :P_TOTAL)
SELECT P_TOTAL as Retorno

However, there is an SQL syntax error, which records the following message in the log:

('ora11preto') ErrorLocal = OpenRecordset() Error #0x80040E14 Description:ORA-00900: instrução SQL inválida
(Source: Microsoft OLE DB Provider for Oracle) (SQL State: (null)) (NativeError: 900)

So, how can I return the value of this stored procedure properly via Elipse E3?

Solution:

First of all, when in the SQL Server environment, we know it’s possible to create a stored procedure that passes input and return parameters; this stored procedure will then be executed via E3Query, and you will need to add an OUTPUT variable to it.

DECLARE @VarSaida
EXEC NOME_sp VarEntrada, @VarSaida OUTPUT
SELECT @VarSaida as Retorno

However, when editing the query’s SQL for execution, we depende on ADO (ActiveX Data Object) or OCI (Oracle Call Interface), technologies that Elipse E3 uses in order to access the Oracle database and, most importantly, the very DBServer on the other end to obtain a return. This issue is more prone to happening where INPUT and OUTPUT parameters are involved; that is why you may sometimes get an error in a stored procedure in an Oracle database.

To work around this, you can execute a synchronous VB that returns the stored procedure’s value. You can execute the script below either via Viewer or via Server:

Sub CommandButton1_Click()  'As definições de constantes podem ser vistas em: 'C:\Program Files\Common Files\System\ado\adovbs.inc Const adCmdStoredProc = &H0004'

ParameterDirectionEnum Values

Const adVarChar = 200
Const adParamInput = 1
Const adParamOutput = 2

Set con = CreateObject("ADODB.Connection")
con.open "provider=oraoledb.oracle;user id=system;password=Abcd1234;data source=ora11preto"
'con.open "dsn=ora11preto;uid=system;pwd=Abcd1234"

strvalfcode = "10"

Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "CALCULA_VALORES"
cmd.commandtype=adCmdStoredProc
cmd.ActiveConnection = con

'CreateParameter(name,type,direction,size,value)
Set prminput = cmd.CreateParameter("", adVarChar, adParamInput, Len(strvalfcode), strvalfcode)
cmd.Parameters.Append prminput

Set prmoutput = cmd.CreateParameter("", adVarChar, adParamOutput, 500)
cmd.Parameters.Append prmoutput

cmd.Execute
MsgBox prmoutput.value 'execução no Viewer 

'Application.Trace prmoutput.value 'execução no server

con.Close

End Sub

For a better performance in the application, you can add the script with the stored procedure call to an Elipse VBScript Driver, and execute it in a different IOServer process.

To create the CALCULA_VALORES stored procedure, which returns data from the HISTORICO_CORRENTES table, we used the following syntax in the Oracle database.

Create or replace PROCEDURE CALCULA_VALORES
(P_FUN_COD IN VARCHAR2,
P_TOTAL OUT VARCHAR2)
IS
BEGIN
SELECT SUM(CORR_BP14) INTO P_TOTAL
FROM HISTORICO_CORRENTES
WHERE lit_10 = P_FUN_COD;
END;

Question:

Solution:

Leave a Reply Cancel reply