Running Anonymous block in ADS on DB2 database

by Nov 4, 2014

Hi Guys, I am not able to execute below anonymous block i ADS

 

BEGIN
  dbms_output.put_line( ‘Hello’ );
END;
 
error:
>[Error] Script lines: 1-3 ————————–
 DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: dbms_output;BEGIN
  ;RETURN
 Message: An unexpected token “dbms_output” was found following “BEGIN
  “.  Expected tokens may include:  “RETURN”.
 Line: 1 
 
Is it possible to run it in ADS? Any specific setting or editor we need to run in. I am using Query analyzer to execute it.
 

 

Response

Sachin Prakash over 9 years ago
Hi,

To get your PL/SQL syntax to execute without a server error, you need to change it to the following:

BEGIN
call dbms_output.put_line( ‘Hello’ );
END
PL/SQL syntax was originally created by Oracle and it seems that DB2 added support for it in v9.7. However, the DB2 syntax is different than Oracle’s, as demonstrated by the above example.

ADS supports Oracle’s PL/SQL when connected to Oracle DB. This allows users to view the output of DBMS messages inside of ADS. However, ADS has not added support for PL/SQL when connected to DB2. Hence, when you execute the above dbms_output command, you will not see ‘Hello’ displayed inside of ADS. The DB2 server will process the command dbms_output command but ADS will not be able to read & display its output.

Ravi Rane over 9 years ago
Thanks Sachin for the reply.