help!! how to call pl/sql anonymous block from java?
code
want to call a pl/sql anonymous block, how can i do it? no procedure
name or package name will be offered.
Can u give me a sample code? thanks very much
Urs Metzger
import java.sql.*;
public class JP {
public static void main(String[] args) {
try {
Class.forName(oracle.jdbc.driver.OracleDriver.class.getName());
} catch(ClassNotFoundException ex) {
ex.printStackTrace();
}
String oraUser = args[0];
String oraPwd = args[1];
try {
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
oraUser,
oraPwd);
conn.setAutoCommit(false);
String plsql = "BEGIN INSERT INTO t VALUES(42); COMMIT; END;";
PreparedStatement statement = conn.prepareStatement(plsql);
statement.execute();
} catch(SQLException ex) {
ex.printStackTrace();
}
}
}
hth,
Urs Metzger
Vladimir M. Zakharychev
Just use a CallableStatement or OracleCallableStatement:
import java.sql.*;
import oracle.jdbc.*;
...
try
{
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@dbhost:
1521:ORCL","SCOTT","TIGER");
OracleCallableStatement oracs =
((OracleConnection)conn).prepareCall("BEGIN do_something; END;");
try
{
if (oracs.execute())
{
ResultSet rs = oracs.getResultSet();
// process the result set here
}
}
catch(SQLException ex)
{
System.out.println("Exception in execute phase: ");
ex.printStackTrace();
}
finally
{
// we want to close the statement regardless if it failed or not
oracs.close();
}
}
catch(SQLException sqlex)
{
System.out.println("General SQL exception encountered:");
sqlex.printStackTrace();
}
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
code
thanks very much.
but it can not work. here:oracs.execute())
this is my pl/sql
declare
deptno dept.deptno%TYPE;
dname dept.dname%TYPE;
CURSOR d_cursor is select deptno,dname from dept;
begin
open d_cursor;
loop
fetch d_cursor into deptno,dname;
exit when d_cursor%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE('d='||deptno||',n='||dname);
end loop;
close d_cursor;
end;
zigz...@yahoo.com
>
> - Show quoted text -
Did you put BEGIN and END when testing. That is very important if I
remember correctly.
Vladimir M. Zakharychev
What's the exact Java code you tried? Which error is being thrown with
this block (what's the exception stack trace?) "Can not work" is to
vague to diagnose and offer a solution... If no exception is thrown
then the block completes successfully, it simply doesn't return
anything and doesn't modify anything as far as I can see, it just
fetches a cursor in a do-nothing loop, so I wouldn't expect any output
from it, and execute() should return false because there's no result
set being returned.
Regards,
Frank van Bortel
>
> What's the exact Java code you tried? Which error is being thrown with
> this block (what's the exception stack trace?) "Can not work" is to
> vague to diagnose and offer a solution... If no exception is thrown
> then the block completes successfully, it simply doesn't return
> anything and doesn't modify anything as far as I can see, it just
> fetches a cursor in a do-nothing loop, so I wouldn't expect any output
> from it, and execute() should return false because there's no result
> set being returned.
>
In addition to Vladimirs obsevations, look into ref cursors.
I do add, I find this a dangerous approach: any programmer should
*know* the data model, and use it - this sounds like the "universal"
application (I believe the marketing term is "database independent")
getting reinvented.
You allow code to be inserted on the fly. Great stuff for hackers!
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
code
>
> - 显示引用的文字 -
i am sorry, i fogot to return value. this is pl/sql.
but execute() returned false., resultset is null.
i want to get result from anonymous block. thanks very much.
CURSOR d_cursor is select deptno,dname from dept;
begin
open d_cursor;
return d_cursor;
close d_cursor;
end;
Vladimir M. Zakharychev
>
> > - 显示引用的文字 -
>
> i am sorry, i fogot to return value. this is pl/sql.
> but execute() returned false., resultset is null.
> i want to get result from anonymous block. thanks very much.
>
> CURSOR d_cursor is select deptno,dname from dept;
> begin
> open d_cursor;
> return d_cursor;
> close d_cursor;
> end;
1) Don't close the cursor being returned. Besides, CLOSE is
unreachable because RETURN exits the block.
2) You don't need PL/SQL for this particular example.
3) You can't RETURN anything from an anonymous block, only from a
stored procedure (function.) But you can use bind variables for this
purpose. Consider this:
import java.sql.*;
import oracle.jdbc.*;
public class curtest {
public static void main(String[] argv)
{
// resolve the JDBC driver class
try
{
Class.forName(oracle.jdbc.driver.OracleDriver.class.getName());
}
catch(ClassNotFoundException ex)
{
ex.printStackTrace();
}
try
{
OracleConnection conn =
(OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@localhost:
1521:BZ92","SCOTT","TIGER");
OracleCallableStatement oracs =
(OracleCallableStatement)conn.prepareCall("BEGIN OPEN ? FOR SELECT
DEPTNO, DNAME FROM DEPT; END;");
// register the output parameter
oracs.registerOutParameter(1,OracleTypes.CURSOR);
try
{
// we don't expect true here
if(oracs.execute())
System.out.println("execute() returned true");
else
System.out.println("execute() returned false");
// however, we'll retrieve our result set via the bind
variable...
ResultSet rs = oracs.getCursor(1);
// and scroll through it
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
}
finally
{
oracs.close();
}
}
catch(SQLException sqlex)
{
sqlex.printStackTrace();
}
}
}
$ java -classpath .:$ORACLE_HOME/jdbc/lib/ojdbc14.jar curtest
execute() returned false
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
There you have it, a cursor returned from an anonymous block.
abhish...@gmail.com
> oracs.registerOutParameter(1,OracleTypes.CURSOR);
How could use same for SQL Server.
I am unable find equivalent for OracleTypes.CURSOR?
Please let me know.
Thanks
Mladen Gogala
>> // register the output parameter
>> oracs.registerOutParameter(1,OracleTypes.CURSOR);
>
>
> Hello,
>
> How could use same for SQL Server.
databases that support PL/SQL are Oracle and DB2. For those surprised by the
latter database, the documentation is here:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.plsql.doc%2Fdoc%2Fc0053607.html
http://pic.dhe.ibm.com/infocenter/idmhelp/ds-v2r2/index.jsp?topic=%2Fcom.ibm.datatools.routines.plsql.doc%2Ftopics%2Ftplsqlrun.html
>
> I am unable find equivalent for OracleTypes.CURSOR?
commend you for working directly with JDBC and not using some DB agnostic Java
framework like Hibernate.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Jeremy
says...
> On Tue, 18 Feb 2014 05:41:05 -0800, abhishek.anne wrote:
>
> >> // register the output parameter
> >> oracs.registerOutParameter(1,OracleTypes.CURSOR);
> >
> >
> > Hello,
> >
> > How could use same for SQL Server.
>
> You cannot use PL/SQL in SQL Server, unless I'm very much mistaken. The only two
> databases that support PL/SQL are Oracle and DB2. For those surprised by the
> latter database, the documentation is here:
>
> http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.plsql.doc%2Fdoc%2Fc0053607.html
> http://pic.dhe.ibm.com/infocenter/idmhelp/ds-v2r2/index.jsp?topic=%2Fcom.ibm.datatools.routines.plsql.doc%2Ftopics%2Ftplsqlrun.html
>
licensing costs - wondering if DB2 would work out cheaper.
Sort of a non-issue once I releaised there are some key data types that
are not supported in the DB2 implementation (IIRC XMLTYPE being one of
them).
--
jeremy
Mladen Gogala
types are. DB2 has its own XML mechanism and interface but it's not PL/SQL
based like Oracle's. Still, that does make application porting much
easier than porting applications to SQL Server.