Problem in executing pl/sql block through jdbc

[Anand]

Hi All,

I am trying to execute contents of a sql file on an oracle 8i database
using java.The requirement is such that I can't run the file directly
on the DB server.
Further, since I could not find any method to run the sql file
directly through jdbc, I decided to parse the scripts from the file,
and execute them using executebatch.
The file contains some sql commands as well as pl/sql blocks (these
blocks create some triggers).
The program works fine and terminates without any exception; but when
I try to invoke any of the triggers, I receieve ORA-04098 error saying
that the trigger REGISTRY_INSERT is invalid and failed re-validation.

If I try to execute those pl/sql blocks directly on sql prompt or
through TOAD, the triggers get created succesfully and they can be
invoked without any problem.

Just to ensure that there is no problem in reading the contents of the
file, I redirected the output of the java class to a text file, copied
all commands and executed them on sql plus. This gave me succesfull
results.

I don't get any exception/errors while running the sql commands
through jdbc.

Could anybody help me on this.


Thanks in advance,
Anand

 

[Galen Boyer]

Hi All,

I am trying to execute contents of a sql file on an oracle 8i
database using java.The requirement is such that I can't run
the file directly on the DB server. Further, since I could not
find any method to run the sql file directly through jdbc, I
decided to parse the scripts from the file, and execute them
using executebatch. The file contains some sql commands as
well as pl/sql blocks (these blocks create some triggers).

You shouldn't create triggers on the fly. This should be a DBA
operation, not application operation.

The program works fine and terminates without any exception;
but when I try to invoke any of the triggers, I receieve
ORA-04098 error saying that the trigger REGISTRY_INSERT is
invalid and failed re-validation.

If I try to execute those pl/sql blocks directly on sql prompt
or through TOAD, the triggers get created succesfully and they
can be invoked without any problem.

Just to ensure that there is no problem in reading the contents
of the file, I redirected the output of the java class to a
text file, copied all commands and executed them on sql
plus. This gave me succesfull results.

I don't get any exception/errors while running the sql commands
through jdbc.

Could anybody help me on this.

Don't know. Post the code and error messages.

 

[Sudsy]

Anand wrote:

If I try to execute those pl/sql blocks directly on sql prompt or
through TOAD, the triggers get created succesfully and they can be
invoked without any problem.

I've seen similar problems with triggers before. Examine the PL/SQL
VERY CAREFULLY and see if there's not something there which expects
a console to be attached. DISPLAY or PRINT, anything which involves
terminal I/O. That's what was causing the problem in the aforementioned
case.

 

[Anand]

Thanks for your reply; there was problem with file parsing. Used
bufferedReader's readLine() instead of read() alongwith little bit of
tweaking with code and everything got sorted out.

Anand

 

[Galen Boyer]

Thanks for your reply; there was problem with file
parsing. Used bufferedReader's readLine() instead of read()
alongwith little bit of tweaking with code and everything got
sorted out.

Well, I hope you also removed the creation of triggers within
your java code.