Test OUT parameters / bind variables

I use DbVis to test SQL before I use the SQL in Java/JDBC programmes. But the thing I don't understand is how to handle OUT parameters / bind variables. As a very simple example: In Java/JDBC I can prepare a CallableStatement with the anonymous SQL block below, registerOutParameter() of tiype cursor, and I can get a ResultSet from the SYS_REFCURSOR. But if I run execure the SQL below in DbVis I get an oracle error that not all bind varables are defined. How can I test this SQL in DbVis? DECLARE FUNCTION myfunc RETURN SYS_REFCURSOR IS rc SYS_REFCURSOR; BEGIN open rc for select sysdate from dual; RETURN rc; END; BEGIN ? := myfunc(); END;

Hi Mark, I'm not sure I really understand what you're asking for, but to run a function/procedure with cursors as OUT parameters, you need to either Execute them within the Procedure Editor or use the @call command in the SQL Commander. DbVisualizer the takes care of binding correctly and retrieving the result set. Please see the Users Guide for details: http://www.dbvis.com/doc/main/doc/ug/procEditor/procEditor.html#mozTocId478351 Best Regards, Hans

Hi Hans, Thanks for your reply, and sorry for not being clear enough. I specifically do not want to "CREATE OR REPLACE" a procedure. What I need to replicate in DbVis what I can do in Java, which is to create a anonymious block of SQL, execute it, and get the returned curser. In Java I can do this: final String plsql = "" + "DECLARE" + " FUNCTION myfunc RETURN SYS_REFCURSOR IS" + " rc SYS_REFCURSOR;" + " BEGIN" + " open rc for select sysdate from dual;" + " return rc;" + " END;" + "BEGIN" + " ? := myfunc();" + "END;"; try { final CallableStatement cs = c.prepareCall(plsql); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); final ResultSet cursorResultSet = (ResultSet) cs.getObject(1); while (cursorResultSet.next ()) { System.out.println (cursorResultSet.getString(1)); } } finally { cs.close(); c.close; } I'd dearly like to be able to test the SQL in DbVis before I put it in my Java. It's the registerOutParameter(...) call, which allows me to return moe or more result sets from one procedure, that I am trying to mimic in DbVis. I hope that clears up my question. Thanks :-)

Hi Mark, I believe I understand what you're after now: you want to test PL/SQL code that creates one or more cursors without having to put it in a permanent function or procedure, right? For Oracle (which I assume you use), you can try to execute the PL/SQL block as-is in the SQL Commanderm using Execute Buffer or enclosing it in SQL Block delimiters. But instead of trying to get the cursor bound to a PL/SQL variable, you need to use a PL/SQL looping construct and print the result to the DBMS Output log (represented by a tab in the SQL Commander result area for Oracle). I'm not sure of the exact syntax (and I'm unable to get a sample working), but something along these lines is what I mean: DECLARE FUNCTION myfunc RETURN SYS_REFCURSOR IS rc SYS_REFCURSOR; BEGIN OPEN rc FOR SELECT SYSDATE AS mydate FROM dual; RETURN rc; END; BEGIN FOR rec IN myfunc() LOOP dbms_output.put_line(rec.mydate); END LOOP; END; I hope this helps. Please see the Users Guide regarding Execute Buffer, SQL Block delimiters and the DBMS Output tab: http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId958847 http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId652495 http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId771579 Best Regards, Hans