Trying to run PL/SQL script using Java

Question

I'm familiar with Java JDBC and use it often to run simple SQLs. However, I want to run something like the below. This is more PL/SQL than regular SQL, hence my problems. I am not running this from the Oracle box but from my own computer. Can someone help me to run this kind of PL/SQL using Java?

spool C:\count.txt;
DEFINE date="TO_DATE ('08-29-2011','mm-dd-yyyy')";
SET NEWPAGE 0;
SET SPACE 0;
SET LINESIZE 500;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET TRIMSPOOL ON;
alter session set nls_date_format='yyyy-mm-dd hh24:mi';
select 'TABLE1', count(*) from SCHEMA.TABLE1 where modifyts < &date;
select 'TABLE2', count(*) from SCHEMA.TABLE2 where modifyts < &date;
select 'TABLE3', count(*) from SCHEMA.TABLE3 where modifyts < &date;
spool off;

Hmm, are you asking how to run pl\sql from your Java Client. I am sorry I am having trouble understanding your question and especially your solution. If you are just trying to run PL\SQL, then you should create a function or procedure in your oracle database and use java to just call the routine. — billygoat
– billygoat, Commented Sep 7, 2011 at 17:58
Did you already try plain Statement.execute(String)? For the SELECTs you'll probably have to use the getResultSet(), getUpdateCount(), getMoreResults() mentioned in that methods @SeeAlso. — Philipp Reichart
– Philipp Reichart, Commented Sep 7, 2011 at 18:00

Justin Cave · Accepted Answer · 2011-09-07 18:03:58Z

10

SPOOL, DEFINE, and SET are all SQL*Plus commands. They are not valid in PL/SQL or in SQL. You cannot, therefore, run this sort of script through a tool other than SQL*Plus (or a tool that supports SQL*Plus commands like SQL Developer or Toad).

You could, of course, have your Java application call out to the operating system to invoke the SQL*Plus executable (assuming it is installed on the machine that the Java application is running on) and pass the script to SQL*Plus. But that's generally way more complexity than you need. It would make more sense to either just use SQL*Plus or to issue just the SELECT statements from your Java application and use Java's file I/O classes to write the results to a file.

answered Sep 7, 2011 at 18:03

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

Thanks, this is what I should be doing. I didn't remember that most of those commands are SQL*Plus only.

Collectives™ on Stack Overflow

Trying to run PL/SQL script using Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related