StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't know how you should create them. Perhaps the OnApplicationStart method is what you need. In my environment the procedures are already in place. We just use Play to invoke them. To invoke stored procedures, you should take a look at the <code>Work</code> interface. By implementing this you can execute statements in the database.</p> <p>We've created a basic OracleProcedure class:</p> <pre><code>public class CallOracleProcedure implements Work { private String anonymousPLSQL; private String[] parameters; public CallOracleProcedure(String anonymousPLSQL, String[] parameters) { this.anonymousPLSQL = anonymousPLSQL; this.parameters = parameters.clone(); } /** * Create a JDBC PreparedStatement and then execute the anonymous * PL/SQL procedure. */ @Override public void execute(Connection connection) { PreparedStatement statement = null; try { statement = connection.prepareStatement("begin " + anonymousPLSQL + "; end;"); if (parameters != null) { int i = 1; for (String param : parameters) { statement.setString(i++, param); } } statement.executeUpdate(); } catch (SQLException e) { Logger.error("Error performing anonymous pl/sql statement: '%s', with parameters: '%s' - catched error '%s'", anonymousPLSQL, parameters, e); } finally { if (statement != null) { try { statement.close(); } catch (Exception e) { Logger.error("Error closing statement: %s", e); } } } } } </code></pre> <p>For each specific stored procedure you can extend this class and pass the name and parameters to the constructor via <code>super()</code>:</p> <pre><code>public class StoredProcedureCall extends CallOracleProcedure { public StoredProcedureCall(String param) { super("package.storedprocedure(?)", new String[] { orgname }); } } </code></pre> <p>In your code you can then call it like this:</p> <pre><code>StoredProcedureCall procedure = new StoredProcedureCall("your parameter"); session.doWork(procedure); </code></pre> <p>If you need to call a procedure and retrieve a return value you can use a <code>CallableStatement</code> in the <code>execute()</code> method:</p> <pre><code>public class ProcedureWithReturnValue implements Work { private final String parameter; private String returnValue = null; public ProcedureWithReturnValue (final String parameter) { this.parameter = parameter; } @Override public void execute(Connection connection) { CallableStatement statement = null; try { statement = connection.prepareCall("begin ? := package.procedure(?); end;"); statement.registerOutParameter(1, OracleTypes.VARCHAR); statement.setString(2, parameter); statement.execute(); returnValue = statement.getString(1); } catch (SQLException e) { Logger.error("Error getting return value - catched error '%s'", e); } } public String getReturnValue() { return returnValue; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload