PL/SQL procedures to JSP conversion. How are exceptions handled?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    46

    Question

    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 */

  2. #2
    Join Date
    Apr 2001
    Posts
    47

    to solve the problem ..

    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width