getting output of Oracle PLSQL/Procedure from Java

getting output of Oracle PLSQL/Procedure from Java

Post by Paresh Sh » Wed, 16 Jul 2003 16:41:08



Hi

I have a problem executing PL/SQL block using Java Connection.
What I want is I have a Pl/SQL which prints the values using
dbms_output.print function
now this output i get spooled in a txt file when I execute that PL/SQL
block from Oracle client

Now I want to call this PL/SQL block from the java using jdbc
drivers...
can anybody suggest me how is it possible with the help of an example

thanks in advance
Regards
Paresh Shah

 
 
 

getting output of Oracle PLSQL/Procedure from Java

Post by Thomas Kellere » Wed, 16 Jul 2003 18:45:07


Paresh Shah schrieb:

Quote:> Hi

> I have a problem executing PL/SQL block using Java Connection.
> What I want is I have a Pl/SQL which prints the values using
> dbms_output.print function
> now this output i get spooled in a txt file when I execute that PL/SQL
> block from Oracle client

> Now I want to call this PL/SQL block from the java using jdbc
> drivers...
> can anybody suggest me how is it possible with the help of an example

I could successfully read the messages with this code, which I found
somewhere on the internet, but I don't remember where. If the author of
those lines recognizes his code: big thanks to you :-)

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;" );

show_stmt.registerOutParameter( 2, Types.INTEGER );
show_stmt.registerOutParameter( 3, Types.VARCHAR );
StringBuffer result = new StringBuffer(1024);
for(;;)
{
   show_stmt.setInt( 1, 32000 );
   show_stmt.executeUpdate();
   result.append(show_stmt.getString(3).trim());
   if ( (done = show_stmt.getInt(2)) == 1 ) break;

Quote:}

return result.toString();

 
 
 

1. PLSQL procedure gets hung..

Another intriguing problem for you, Oracle Experts:

I have been given a PLSQL procedure to be debugged that gets hung when
executed. It basically implements a Cascade Update by inserting a new
record with a new ID, then updating all the child records with the new
ID, and finally deletes the record with the old ID from the parent
table. Interesting thing is that when I execute all the sql statements
within this procedure one by one, everything works fine. The only
diffence that I can see is that in the procedure commits are done by
calling 'STANDARD.commit'. I don't really know why 'STANDARD.commit'
would be used rather than just 'commit'. But this leads me to think that
something is wrong with this.
Does anybody have any ideas if this could be the reason or what else
could be the cause of this problem? The procedure is basically as
follows:

insert into parent
     select newId, ..., .... from parent where doc_id = OldId.
commit;
update chiltable1 set doc_id = NewId where doc_id= OldId;
commit;
update childtable2 set doc_id = NewId where doc_id= Oldid;
commit
....
update childtable3 ...
..
commit;
delete from parent where doc_id = OldId;

Thanks..

Have a bug-free day..

Tansel

2. BlobInputStream.java patch

3. Oracle.plsql java class

4. Visual Basic 6.0

5. Problem getting an output parameter from stored procedure

6. Connection to Informix Problem

7. Getting the output from a stored procedure in VB.

8. IO on tempdbs

9. Getting output values from stored procedures called from a C program

10. Need Help - Not getting output back from stored procedure

11. Getting the output from a stored procedure in VB.

12. Getting the output of a stored procedure in a VBScript

13. Getting OUTPUT parameters and return values from Stored Procedures