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:
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'
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
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;