Descrição:
Como contornar o erro de sintaxe em uma stored procedure no Oracle?
Desenvolvi uma stored procedure no banco Oracle 11g que retorna um parâmetro OUTPUT. Dessa forma, no SQL Developer, ela é executada da seguinte forma:
VARIABLE P_TOTAL VARCHAR2(200);
execute calcula_valores(10,:P_TOTAL);
print :P_TOTAL;
Então, o seguinte resultado é exibido:
Logo após, a Stored Procedure é executada da seguinte forma no objeto E3Query:
VARIABLE P_TOTAL VARCHAR(200)
Call CALCULA_VALORES(10, :P_TOTAL)
SELECT P_TOTAL as Retorno
No entanto, um erro de sintaxe SQL é exibido, e a seguinte mensagem é registrada no 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)
Portanto, como posso retornar o valor desta Stored Procedure via Elipse E3?
Solução:
DECLARE @VarSaida
EXEC NOME_sp VarEntrada, @VarSaida OUTPUT
SELECT @VarSaida as Retorno
No entanto, quando editamos o SQL da consulta para execução, dependemos do ADO (ActiveX Data Object) ou OCI (Oracle Call Interface), tecnologias utilizadas pelo E3 para acessar o banco de dados Oracle, e, em última análise, o próprio DBServer que temos do outro lado para obter o retorno. Esse problema se manifesta justamente onde temos parâmetros INPUT e OUTPUT.
Para contornar essa questão, pode ser executado um script VB síncrono que retorne o valor da stored procedure. O script abaixo pode ser executado não apenas no Viewer como também no 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
(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;