getting output of Oracle PLSQL/Procedure from Java 
Author Message
 getting output of Oracle PLSQL/Procedure from Java
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



Sat, 31 Dec 2005 15:41:08 GMT
 getting output of Oracle PLSQL/Procedure from Java

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


Sat, 31 Dec 2005 17:45:07 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. PLSQL procedure gets hung..

2. Oracle.plsql java class

3. Problem getting an output parameter from stored procedure

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

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

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

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

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

9. Getting OUTPUT parameters and return values from Stored Procedures

10. Getting PRINTS, SELECTS, and other output to show immediatley in stored procedure

11. Retrieving output params from stored procedures ODBC-JDBC bridge Java SQL Server

12. PLSQL/Oracle Stored Procedures


 
Powered by phpBB® Forum Software