SET SERVEROUTPUT ON;
SET ECHO ON;
SET TIMING ON;
DECLARE
V_SERVER_HOST CONSTANT VARCHAR2(100) := LOWER(SYS_CONTEXT('USERENV','SERVER_HOST'));
V_SESSION_USER CONSTANT VARCHAR2(100) := LOWER(SYS_CONTEXT('USERENV','SESSION_USER'));
V_INSTANCE_NAME CONSTANT VARCHAR2(100) := LOWER(SYS_CONTEXT('USERENV','INSTANCE_NAME'));
V_SERVICE_NAME CONSTANT VARCHAR2(100) := LOWER(SYS_CONTEXT('USERENV','INSTANCE_NAME'));
V_SERVICE_IP CONSTANT VARCHAR2(100) := LOWER(SYS_CONTEXT('USERENV','IP_ADDRESS'));
-- Constantes para os valores fixos
V_COD CONSTANT VARCHAR2(5) := '41233';
V_DESCRICAO CONSTANT VARCHAR2(100) := 'Sabor de Verão - Laranja - 2 Litro';
V_CATEGORIA CONSTANT VARCHAR2(100) := 'Sucos de Frutas';
-- Mensagens de erro
V_ROLLBACK_MSG CONSTANT VARCHAR2(100) := 'Realizando Rollback.';
V_ERROR_MSG CONSTANT VARCHAR2(100) := 'Erro na execução do script';
BEGIN
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Início Script.: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ' - ' || V_SESSION_USER|| '@' || V_INSTANCE_NAME|| '/' || V_SERVICE_NAME|| '(' || V_SERVER_HOST || '/' || V_SERVICE_IP ||')');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------------------------');
INSERT INTO produto_exercicio (
cod,
descricao,
categoria
) VALUES (
V_COD,
V_DESCRICAO,
V_CATEGORIA
);
COMMIT;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' linha(s) inserida(s).');
DBMS_OUTPUT.PUT_LINE('Script executado com sucesso.');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Fim Script.: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
|| ' - ' || V_SESSION_USER|| '@' || V_INSTANCE_NAME|| '/' || V_SERVICE_NAME|| '(' || V_SERVER_HOST ||')');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(V_ROLLBACK_MSG);
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(V_ERROR_MSG || ': [' || SQLERRM || '].');
DBMS_OUTPUT.PUT_LINE('Error_Stack...' || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_STACK());
DBMS_OUTPUT.PUT_LINE('Error_Backtrace...' || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
END;
/