Hi Richard You can do that. But I think it is recommandable to wrap up the PL/SQL and put it into packages. This way, your code is much better organized than having anonymous blocks throughout your Java code.
Regards, Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] -----Ursprüngliche Nachricht----- Von: Ji, Richard [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 8. Juli 2002 17:38 An: Multiple recipients of list ORACLE-L Betreff: RE: JDBC Maybe I misunderstood what you guys are talking about. And sorry if that's the case. But you can call anonymous block and return data from Oracle to Java: CallableStatement stmt = conn.prepareCall( "begin " + "open ? for select ename from emp;" + "end;"); stmt.registerOutParameter(1,OracleTypes.CURSOR); stmt.execute(); Richard -----Original Message----- Sent: Monday, July 08, 2002 6:23 AM To: Multiple recipients of list ORACLE-L Hi Antje Eventhough I never migrated Sybase (or SQL Server) code (JDBC/Java) to Oracle, maybe that helps anyway: In Transact SQL, you can actually return data from an anonymous block, that's correct. An equivalent to do this in PL/SQL might be the use of OUT parameters within stored procedures. That means that you just have to write wrappers for your T-SQL blocks (put them into PL/SQL stored procs). Done that, you can communicate from Java with the procedure through the OUT parameter, once you registered the proc with it's OUT parameters within the Java context. After binding the parameter to Java variables, the data will be transferred to those variables (when the proc is executed) for your further use within the Java context. Nothing new, actually. A problem might be the syntactical difference between T-SQL and PL/SQL, eventhough Oracle is able to handle more of the syntax you encounter in T-SQL like CASE or ANSI SQL joins. Hope that helps a little bit. If not, pipe it to /dev/null ;) Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Behalten Sie den Ueberblick - mit dem neuen BasicOverView, unserer Seminaruebersicht fuer das 2. Halbjahr 2002. Sie haben noch kein Exemplar? Schreiben Sie eine E-Mail an mailto:[EMAIL PROTECTED] oder rufen Sie uns an unter 0 18 03 / 73 64 62 73! Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -----Ursprüngliche Nachricht----- Von: Sackwitz, Antje [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 5. Juli 2002 17:13 An: Multiple recipients of list ORACLE-L Betreff: JDBC Hi, gurus, collegues of mine told me, that Sybase can return information from anonymous blocks. When I do not want to use SQLJ, but only JDBC what is the best way to migrate the Sybase code to Oracle. Any tips appreciated, Regards, Antje Sackwitz ____________________________ Antje Sackwitz ppi Media GmbH Deliusstraße 10 D-24114 Kiel phone +49 (0) 43 1-53 53-2 16 fax +49 (0) 43 1-53 53-2 22 email mailto:[EMAIL PROTECTED] web www.ppi.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sackwitz, Antje INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).