If pl/sql exception get catched, it can be difficult to get the cause of the error.
To work around this the exception handler should log what was going wrong.
Suggestion
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
Yesterday I ran into a challenge on mysql.
A procedure to create an drop a database on mysql has been needed. My first thought was “easy”, it wasn’t.
There is no “execute immediate” or dynamic variable replacement in mysql. The workaround to execute dynamic DDL in a mysql procedure was found in a bug description. (http://bugs.mysql.com/bug.php?id=9150)
It can be solved with a prepared statement.
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
My template to create a DB I just poste here. Maybe it helps:
delimiter //
DROP PROCEDURE IF EXISTS create_schema;
CREATE PROCEDURE create_schema (p_schema_name VARCHAR(50))
SQL SECURITY DEFINER
BEGIN
DECLARE v_schema_count INTEGER;
startb:BEGIN
-- check exists
SELECT count(1) INTO v_schema_count FROM information_schema.SCHEMATA WHERE upper(SCHEMA_NAME)=upper(p_schema_name);
IF v_schema_count > 0 THEN
SELECT 'Error: DB Exists' FROM dual;
leave startb;
END IF;
-- check name
IF ( INSTR(p_schema_name ,'AD_') = 0) THEN
SELECT 'Error: Invalid schema name' FROM dual;
leave startb;
END IF;
-- create db
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
-- grant permissiosn
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
END startb;
END;
//
For class file version 50.0 (Java 1.6), the opening bytes are:
CA FE BA BE 00 00 00 32
Check if your class file was compiled with java 1.6
bash-3.00$ od -x JDBCVersion.class | head
0000000 cafe babe 0000 0032 009c 0a00 2f00 4007
tip:
EAR and JAR files are just zip files an can be unzipte with
unzip