PL/SQL in 8.0.7

Hi All, I am trying to run this pl/sql --/ select 'hello world' from dual; / And I get the following error: 15:14:05 [SELECT - 0 row(s), 0.000 secs] [Error Code: 911, SQL State: 22019] ORA-00911: invalid character ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] When I remove --/ and /, SQL > Execute works but SQL > Execute Buffer fails. I am running it against an Oracle XE 10 db. I confirm that in Tools->Tool Properties, select the General->SQL Editor->Statement Delimiters > SQL Block Identifiers: Begin and End identifiers are "--/" and "/". When I look at the Debug window and run the statement with --/ and /, I see the following output: 15:23:01 [DEBUG Thread-91 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:23:01 [DEBUG pool-2-thread-13 C.?] DefaultEditor-5: T4CConnection.createStatement() 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 15:23:01 [DEBUG Thread-91 J.execute] Executing... 15:23:01 [DEBUG Thread-91 J.setCurrentCatalog] DbConnection='Timesloot Booking - robbram-vm as timeslot' Catalog='null' Schema='null' NewCatalog='null' 15:23:01 [DEBUG Thread-91 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:23:01 [DEBUG pool-2-thread-13 C.?] DefaultEditor-5: T4CConnection.createStatement() 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 15:23:01 [DEBUG Thread-91 J.setCurrentSchema] DbConnection='Timesloot Booking - robbram-vm as timeslot' Catalog='null' Schema='TIMESLOT' NewSchema='TIMESLOT' 15:23:01 [DEBUG Thread-91 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:23:01 [DEBUG pool-2-thread-13 C.?] DefaultEditor-5: T4CConnection.createStatement() 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 15:23:01 [DEBUG pool-2-thread-13 C.?] DefaultEditor-5: T4CConnection.createStatement() 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: OracleStatementWrapper.execute("--/ select 'hello world' from dual; /") SQLState(22019) vendor code(911) 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: EXCEPTION -> java.sql.SQLSyntaxErrorException: ORA-00911: invalid character 15:23:01 [DEBUG Thread-91 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:23:01 [DEBUG pool-2-thread-13 C.?] DefaultEditor-5: T4CConnection.createStatement() 15:23:01 [DEBUG pool-2-thread-13 B.?] DefaultEditor-5: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") Any help would be most appreciated! Rob :)

Hi Robert, When you use an SQL Block (--/ ? /) or Execute Buffer, the statement is sent to the database without any processing by DbVisualizer whatsoever. In your case, you have a SELECT statement ended by a semicolon, and Oracle (as well as most other databases) does not accept that. The semicolon is a statement delimiter, not part of the statement itself. For a SELECT and other "normal" statements, you should just use Execute without any SQL Blocks. DbVisualizer then separates the statements based on the statement delimiter and sends them one by one to the database. SQL Blocks and Execute Buffer are for special cases, such as CREATE PROCEDURE, when the statement uses semicolons to separate parts of the statement. Best Regards, Hans

Hi Hans, Coming back to this after a few months because I did not solve the issue so far. In Oracle SQL Developer 3.0.04 I can run this fine: --/ select 'hello world' from dual; / and get expected results: 'HELLOWORLD' ------------ hello world I tried to create a more complicated example.. serveroutput on DECLARE var_status varchar(200); BEGIN SELECT status INTO var_status FROM ODS.PERSON WHERE userid = 'ROBBRAM'; dbms_output.put_line(var_status); END; / Again, Oracle SQL Developer 3.0.04 I ran this just fine: anonymous block completed a In DbVisualiser 9.0, Execute Buffer gave me 11:22:38 [--/ - 0 row(s), 0.000 secs] [Error Code: 922, SQL State: 42000] ORA-00922: missing or invalid option ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] Sorry to appear dense, but I am finding it very frustrating to work with pieces of SQL that seem to run OK in SQL Developer but not DbVisualizer. Rob :)

Hi Robert, It looks like you are using both SQL Block delimiter and Execute Buffer. They are mutually exclusive. If you have SQL Block delimiters in the script and use Execute Current, the delimiters are sent to the database and it doesn't know what to do with them (they are only for telling DbVisualizer what to do). So, if you want to execute just the block, use Execute Buffer without any delimiters; if you want to execute the block plus a number of other statements, use the SQL Block delimiters and Execute (not Execute Buffer). Also, you have a "server output on" statement in your script. That is an sql*plus command that neither the database nor DbVisualizer know how to process. Remove it, and instead open the DBMS Output tab and click Enable Oracle DBMS Output to capture dbms_output.put_line() text. Best Regards, Hans