KB-97376: Erro na execução de Stored Procedure no Oracle DB.

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:

Primeiramente, quando estamos no ambiente do SQL Server, sabemos que é possível criar uma stored procedure com passagem de parâmetros de entrada e de retorno; essa stored procedure será então executada via consulta no E3, sendo necessário informar uma variável tipo OUTPUT.

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‘

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

Para uma melhor performance na aplicação, o script com a chamada da stored procedure pode ser inserido em um Driver Elipse VBScript, sendo executado em um processo IOServer separado.

Para criar a stored procedure CALCULA_VALORES, que retorna dados da tabela HISTORICO_CORRENTES, foi utilizada a seguinte sintaxe no banco Oracle.

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;

Descrição:

Solução:

Deixe seu Comentário Cancelar resposta