Muito saudosamente eu retorno a esse prestigiado fórum, que me ajudou muito no começo da minha carreira e o qual eu acredito ter deixado a minha humilde contribuição também.
Continuo atuando com Oracle PL/SQL e demais tecnologias como ASP.NET C#, dentre outras.
Mas o que me traz de volta aqui é a seguinte situação:
Hoje, na empresa que atuo. Existe uma espécie de versionamento de objetos de banco de dados, realizado através de uma aplicação em ASP.NET C#, que acessa o repositório de versionamento e converte o arquivo em array e passa esses dados como parâmetro de entrada para uma package que se encarrega de compila-lo no banco. Porém, esse processo está apresentando algumas instabilidades. Trata-se de uma package desenvolvida há muitos anos, a qual ficou muito tempo sem manutenção. Por isso, eu me predispus a fazer uma pré-análise para tentar entender o cenário e me deparei com o seguinte:
* Versão do banco de dados: Oracle 10g;
-- CRM5.PG_GTO
-- SP_EXECUTA
-- SP_COMPILA*
-- SP_SINONIMOS
-- SP_PERMISSOES
-- SP_STATUS
PROCEDURE SP_COMPILA
(P_TIPO_OBJETO IN VARCHAR2,
P_NOME_OBJETO IN VARCHAR2,
P_SCRIPT_OBJETO IN T_ARRAY)
IS
V_SCRIPT_OBJETO DBMS_SQL.VARCHAR2S;
V_CURSOR INTEGER;
V_ROWS INTEGER;
V_FINAL INTEGER;
V_LINHA VARCHAR2(4000);
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
FOR I IN P_SCRIPT_OBJETO.FIRST .. P_SCRIPT_OBJETO.LAST LOOP
V_LINHA := REPLACE(P_SCRIPT_OBJETO(I), CHR(13), '');
V_SCRIPT_OBJETO(I) := V_LINHA;
V_FINAL := I;
END LOOP;
DBMS_SQL.PARSE(V_CURSOR, V_SCRIPT_OBJETO, 1, V_FINAL, FALSE, DBMS_SQL.NATIVE); -- erro nessa linha
V_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
IF P_TIPO_OBJETO = 'PACKAGE' OR P_TIPO_OBJETO = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE ' || P_NOME_OBJETO || ' COMPILE PACKAGE' ;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERRO_CODIGO := TO_CHAR(SQLCODE); ERRO_MENSAGEM := 'PG_GTO.SP_COMPILA [COMANDO: COMPILE PACKAGE] [OBJETO: ' || P_NOME_OBJETO || '] [CODIGO: ' || TO_CHAR(SQLCODE) || '] [DESCRIC?O: ' || SQLERRM || ']';
RAISE ERRO_SP_COMPILA;
END SP_COMPILA;
DECLARE
P_AMBIENTE VARCHAR2(3) := 'XXX';
P_NOME_SCHEMA VARCHAR2(5) := 'XXX';
P_TIPO_OBJETO VARCHAR2(15); -- := 'PACKAGE';
P_NOME_OBJETO VARCHAR2(60) := 'PKG_TESTE';
P_SCRIPT_OBJETO CRM5.PG_GTO.T_ARRAY;
P_STATUS VARCHAR2(20000);
P_RETORNO VARCHAR2(20000);
BEGIN
-- ------------------------------------------------------------------------
-- Cria a PACKAGE SPEC
P_SCRIPT_OBJETO(0) := 'CREATE OR REPLACE PACKAGE CRM5.PKG_TESTE IS ';
P_SCRIPT_OBJETO(1) := 'V_TESTE VARCHAR2(10); ';
P_SCRIPT_OBJETO(2) := 'END PKG_TESTE;';
P_TIPO_OBJETO := 'PACKAGE';
CRM5.PG_GTO.SP_EXECUTA(P_AMBIENTE => P_AMBIENTE,
P_NOME_SCHEMA => P_NOME_SCHEMA,
P_TIPO_OBJETO => P_TIPO_OBJETO,
P_NOME_OBJETO => P_NOME_OBJETO,
P_SCRIPT_OBJETO => P_SCRIPT_OBJETO,
P_STATUS => P_STATUS,
P_RETORNO => P_RETORNO);
DBMS_OUTPUT.PUT_LINE('P_STATUS: ' || P_STATUS);
DBMS_OUTPUT.PUT_LINE('P_RETORNO: ' || P_RETORNO);
P_SCRIPT_OBJETO.DELETE();
END;
Ao debugar, a exceção é levantada na linha:
P_RETORNO: PROBLEMA DE COMPILAC?O DO OBJETO PKG_TESTE, TIPO PACKAGE - [DESCRIC?O: PG_GTO.SP_COMPILA [COMANDO: COMPILE PACKAGE] [OBJETO: PKG_TESTE] [CODIGO: -900] [DESCRIC?O: ORA-00900: invalid SQL statement]];
DBMS_SQL.PARSE(V_CURSOR, V_SCRIPT_OBJETO, 1, V_FINAL, FALSE, DBMS_SQL.NATIVE); -- erro nessa linha
Se eu executar esse código manualmente:
CREATE OR REPLACE PACKAGE CRM5.PKG_TESTE IS
V_TESTE VARCHAR2(10);
END PKG_TESTE;
Analisei os parâmetros do pacote: DBMS_SQL.PARSE, sem encontrar nenhuma solução. Por isso, fiz um teste com as seguintes alterações:
PROCEDURE SP_COMPILA( P_TIPO_OBJETO IN VARCHAR2,
P_NOME_OBJETO IN VARCHAR2,
P_SCRIPT_OBJETO IN T_ARRAY )
IS
V_CURSOR NUMBER := 0;
V_ROWS NUMBER := 0;
V_SQL CLOB;
L_TESTE VARCHAR2(10);
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
FOR I IN P_SCRIPT_OBJETO.FIRST .. P_SCRIPT_OBJETO.LAST
LOOP
V_SQL := V_SQL || P_SCRIPT_OBJETO(I);
END LOOP I;
L_TESTE := 'TESTE';
DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);
V_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
IF P_TIPO_OBJETO = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE CRM5.' || P_NOME_OBJETO || ' COMPILE PACKAGE';
ELSE
EXECUTE IMMEDIATE 'ALTER PACKAGE CRM5.' || P_NOME_OBJETO || ' COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
ERRO_CODIGO := TO_CHAR(SQLCODE);
ERRO_MENSAGEM := 'PG_GTO.SP_COMPILA [COMANDO: COMPILE PACKAGE] [OBJETO: ' || P_NOME_OBJETO || '] [CODIGO: ' || TO_CHAR(SQLCODE) || '] [DESCRIC?O: ' || SQLERRM || ']';
RAISE ERRO_SP_COMPILA;
END SP_COMPILA;
E o erro de criação da package de testes foi corrigido e consegui criá-las sem problemas! Pensei ter corrigido o erro, mas ao testá-la em ambiente de desenvolvimento com um arquivo real, onde a PACKAGE SPE possui aproximadamente: 870 linhas e a PACKAGE BODY: 9000 e poucas linhas, não funciona! Ao processar, recebo o erro abaixo:
Da variável: V_SCRIPT_OBJETO DBMS_SQL.VARCHAR2S;
Por: V_SQL CLOB;
E no momento da execução, alterei os parâmetros:
DE: DBMS_SQL.PARSE(V_CURSOR, V_SCRIPT_OBJETO, 1, V_FINAL, FALSE, DBMS_SQL.NATIVE); -- erro nessa linha
PARA: DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);
No entanto, esse erro não parece estar relacionado a procedure de compilação (SP_COMPILA) e sim ao conteúdo do arquivo (PACKAGE SPEC/BODY) a ser compilado.
ORA-06502 PL/SQL numeric or value error
Ao analisar o conteúdo da variável: V_SQL. Ela não exibe os dados completos relacionados ao arquivo a ser processado. Agora, não sei se é uma limitação de cache do próprio PL/SQL ou se ela não está sendo incrementada em sua totalidade, gerando assim o erro.
Por isso, gostaria de recorrer aos senhores para saber se conseguem me ajudar a trazer luz a essas questões.
Antecipadamente, agradeço!
