I am working on a web-based query execution platform. Here I have an option to run PlSQL code. I was trying to create a trigger with
dbms_output.put_line();
But to execute this properly, I need
SET SERVEROUTPUT ON;
to be given before the trigger create statement.
I am running the sql file using below command in a sh file
timeout 40 java -Djava.security.egd=file:/dev/./urandom -cp /path/to/java/ojdbc6-11.2.0.jar:/path/to/java/gsqlparser-current.jar:/path/to/java/mysql-connector-java-8.0.20.jar:/path/to/java/opencsv-2.2.jar:/path/to/java/jsqlparser-4.0.jar:/path/to/java/btc-ascii-table-1.0-sources.jar:target/classes Check_plsql 2>&1
Here target/class having all the dependency files and the query.sql file.
If I am giving the trigger only, then I am getting "Trigger Created" message. But if I add the "SET SERVEROUTPUT ON" option before the trigger query, I am getting the below error
java.sql.SQLException: Sql [sstsqlpluscmd] is not supported in shard sql. Only support 'select' 'create' 'insert' 'update' 'create function' 'create block' 'create procedure' sql statement at Plsql_Check.main(Plsql_Check.java:168)
How can I get rid of this error and run the "serveroutput" option?
Please help me on this. I am not much familiar with this type of exxecution.
CodePudding user response：
"set serveroutput" and other such commands of this form are not SQL commands, but commands for particular tools that access the database. Things like SQLPlus, SQLcl etc.
So only those tools that understand the "set" command are appropriate. If you want to collect dbms_output from your own tool, then you need to implement that functionality yourself. DBMS_OUTPUT just puts data into an array and thus at the end of the call you would check the array for content and do what you want with it, eg
declare
line varchar2(1000);
status integer;
begin
-- turn on dbms_output capture
dbms_output.enable;
-- your app code goes here
-- now check the array for content and do something with it. In my case, I'm putting into a table
dbms_output.get_line( line, status);
while ( line is not null ) loop
insert into t values (line);
commit;
dbms_output.get_line( line, status);
end loop;
end;
/
CodePudding user response：
A quick internet search for "java access dbms_output buffer" leads to the top result (for me) of Ask Tom's "Retrieving DBMS_OUTPUT.put_line from JDBC?"
Which contains the code:
import java.sql.*; class DbmsOutput { /* * our instance variables. It is always best to * use callable or prepared statements and prepare (parse) * them once per program execution, rather then one per * execution in the program. The cost of reparsing is * very high. Also -- make sure to use BIND VARIABLES! * * we use three statments in this class. One to enable * dbms_output - equivalent to SET SERVEROUTPUT on in SQL*PLUS. * another to disable it -- like SET SERVEROUTPUT OFF. * the last is to "dump" or display the results from dbms_output * using system.out * */ private CallableStatement enable_stmt; private CallableStatement disable_stmt; private CallableStatement show_stmt; /* * our constructor simply prepares the three * statements we plan on executing. * * the statement we prepare for SHOW is a block of * code to return a String of dbms_output output. Normally, * you might bind to a PLSQL table type but the jdbc drivers * don't support PLSQL table types -- hence we get the output * and concatenate it into a string. We will retrieve at least * one line of output -- so we may exceed your MAXBYTES parameter * below. If you set MAXBYTES to 10 and the first line is 100 * bytes long, you will get the 100 bytes. MAXBYTES will stop us * from getting yet another line but it will not chunk up a line. * */ public DbmsOutput(Connection conn) throws SQLException { enable_stmt = conn.prepareCall("begin dbms_output.enable(:1); end;"); disable_stmt = conn.prepareCall("begin dbms_output.disable; end;"); show_stmt = conn.prepareCall( "declare " " l_line varchar2(255); " " l_done number; " " l_buffer long; " "begin " " loop " " exit when length(l_buffer) 255 > :maxbytes OR l_done = 1; " " dbms_output.get_line( l_line, l_done ); " " l_buffer := l_buffer || l_line || chr(10); " " end loop; " " :done := l_done; " " :buffer := l_buffer; " "end;"); } /* * enable simply sets your size and executes * the dbms_output.enable call * */ public void enable(int size) throws SQLException { enable_stmt.setInt(1, size); enable_stmt.executeUpdate(); } /* * disable only has to execute the dbms_output.disable call */ public void disable() throws SQLException { disable_stmt.executeUpdate(); } /* * show does most of the work. It loops over * all of the dbms_output data, fetching it in this * case 32,000 bytes at a time (give or take 255 bytes). * It will print this output on stdout by default (just * reset what System.out is to change or redirect this * output). */ public void show() throws SQLException { int done = 0; show_stmt.registerOutParameter(2, java.sql.Types.INTEGER); show_stmt.registerOutParameter(3, java.sql.Types.VARCHAR); for (;;) { show_stmt.setInt(1, 32000); show_stmt.executeUpdate(); System.out.print(show_stmt.getString(3)); if ((done = show_stmt.getInt(2)) == 1) { break; } } } /* * close closes the callable statements associated with * the DbmsOutput class. Call this if you allocate a DbmsOutput * statement on the stack and it is going to go out of scope -- * just as you would with any callable statement, result set * and so on. */ public void close() throws SQLException { enable_stmt.close(); disable_stmt.close(); show_stmt.close(); } }
and sample usage for it:
import java.sql.*; class test { public static void main(String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dbserver:1521:ora8i", "scott", "tiger"); conn.setAutoCommit(false); Statement stmt = conn.createStatement(); DbmsOutput dbmsOutput = new DbmsOutput(conn); dbmsOutput.enable(1000000); stmt.execute("begin emp_report; end;"); stmt.close(); dbmsOutput.show(); dbmsOutput.close(); conn.close(); } }