Example Depot

try { // To create a connection to an Oracle database, // see Connecting to an Oracle Database Statement stmt = connection.createStatement(); // Create procedure myproc with no parameters String procedure = "CREATE OR REPLACE PROCEDURE myproc IS " + "BEGIN " + "INSERT INTO oracle_table VALUES('string 1'); " + "END;"; stmt.executeUpdate(procedure); // Create procedure myprocin with an IN parameter named x. // IN is the default mode for parameter, so both `x VARCHAR' and `x IN VARCHAR' are valid procedure = "CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS " + "BEGIN " + "INSERT INTO oracle_table VALUES(x); " + "END;"; stmt.executeUpdate(procedure); // Create procedure myprocout with an OUT parameter named x procedure = "CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS " + "BEGIN " + "INSERT INTO oracle_table VALUES('string 2'); " + "x := 'outvalue'; " // Assign a value to x + "END;"; stmt.executeUpdate(procedure); // Create procedure myprocinout with an IN/OUT parameter named x; // x functions as an IN parameter and also as an OUT parameter procedure = "CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS " + "BEGIN " + "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter + "x := 'outvalue'; " // Use x as OUT parameter + "END;"; stmt.executeUpdate(procedure); // Create a function named myfunc which returns a VARCHAR value; // the function has no parameter String function = "CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS " + "BEGIN " + "RETURN 'a returned string'; " + "END;"; stmt.executeUpdate(function); // Create a function named myfuncin which returns a VARCHAR value; // the function has an IN parameter named x function = "CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS " + "BEGIN " + "RETURN 'a return string'||x; " + "END;"; stmt.executeUpdate(function); // Create a function named myfuncout which returns a VARCHAR value; // the function has an OUT parameter named x whose value is // returned to the calling PL/SQL block when the execution of the function ends function = "CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS " + "BEGIN " + "x:= 'outvalue'; " + "RETURN 'a returned string'; " + "END;"; stmt.executeUpdate(function); // Create a function named myfuncinout that returns a VARCHAR value; // the function has an IN/OUT parameter named x. As an IN parameter, the value of x is // defined in the calling PL/SQL block before it is passed in eyfuncinout // function. As an OUT parameter, the new value of x, `x value||outvalue', is also // returned to the calling PL/SQL block when the execution of the function ends. function = "CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS " + "BEGIN " + "x:= x||'outvalue'; " + "RETURN 'a returned string'; " + "END;"; stmt.executeUpdate(function); } catch (SQLException e) { }