PL/SQL procedures to JSP conversion. How are exceptions handled?

[PFC]

I wrote a set of procedures that would suppress:
ORA-00942: table or view does not exist

Ex:
CREATE OR REPLACE PROCEDURE "DTABLE" (aname varchar) IS
table_or_view_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT (table_or_view_not_found, -942);
BEGIN
execute IMMEDIATE 'DROP table ' || aname || ' cascade constraints';
Exception
WHEN table_or_view_not_found THEN
NULL;
When Others then
RAISE;
END dtable;
/

I'm being asked to port this into JSPs. I found the following generic drop object example
in Oracle's documentation: A64686-01. It seems to swallow all errors. For
example: exec drop_it('not_a_table','test_table');
does not report any errors. Is it possible to raise PRAGMA exceptions in java?


Oracle's Code:
/* Start Java Class */
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

public class GenericDrop {
public static void dropIt (String object_type, String object_name)
throws SQLException {
// Connect to Oracle using JDBC driver
Connection conn = new OracleDriver().defaultConnection();
// Build SQL statement
String sql = "DROP " + object_type + " " + object_name;
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {System.err.println(e.getMessage());}
}
}
/* End Java Class */

/* Start Call Procedure */
CREATE OR REPLACE PROCEDURE drop_it (
obj_type VARCHAR2,
obj_name VARCHAR2)
AS LANGUAGE JAVA
NAME 'GenericDrop.dropIt(java.lang.String, java.lang.String)';
/* End Call Procedure */

[dmitriy]

Hi, PFC!

You are doing just great, and the only thing you have to do is to organize an output for Java err output stream within Aurora Oracle java machine. All you need to do is just to add a couple of strings:

-- this one redirect Java err messages to serveroutput buffer
-- (1000000 max allowing size of the buffer)
exec dbms_java.set_output(1000000);

-- this one allows server output
set serveroutput on size 1000000;


I tried you example and got the next, when tried to drop not existing table:
========================================
Without these strings:

SQL> exec drop_it('table', 'not_existing_table');

PL/SQL procedure successfully completed.

SQL>


With these strings:

SQL> exec drop_it('table', 'not_existing_table');
ORA-00942: table or view does not exist


PL/SQL procedure successfully completed.

SQL>

Good luck!