Topic
  • 1 reply
  • Latest Post - ‏2019-05-14T16:17:39Z by Luke Numrych
MarieG
MarieG
2 Posts

Pinned topic Running anonymous blocks in DS?

‏2019-05-14T04:19:36Z | anonymous blocks

Is there any method / syntax that would allow me to run simple SQL PL scripts via Data Studio? I most recently saw a posting about this from 4-5 years ago. I need to avoid using stored procedures. 

 

A simplistic example (that runs fine via the command line processor):

set serveroutput on@
begin
        declare counter int default 0;
        while counter < 10 do
                call dbms_output.put_line ('Counter: '||counter);
                set counter = counter + 1;
        end while;
end@
 

This won't run as-is in a Data Studio SQL window. 

If I strip it down so it is literally just
begin
end;

that runs without error - but obviously doesn't do anything.

  • Luke Numrych
    Luke Numrych
    82 Posts

    Re: Running anonymous blocks in DS?

    ‏2019-05-14T16:17:39Z  

    I don't think this is possible from Data Studio.  From the DBMS_OUTPUT module Knowledge Center entry:

    These procedures are useful during application debugging when you need to write messages to standard output

    To me that means that you have to be at a console which the CLP driver (or any other tool that uses the CLP driver) can target as a standard output.  Data Studio uses a JDBC driver to connect to the database and it seems to me that there is no out-of-the-box support for this provided by the JDBC driver.  To be clear, this is not a limitation of DB2 or Data Studio - it is a limitation of JDBC.

    Here is some supporting evidence for my statements above.  The post I am linking also shows that it is possible to "instrument" a JDBC application to read the DBMS_OUTPUT. but I don't know how difficult that would be in the case of Data Studio: Retrieving DBMS_OUTPUT.put_line from JDBC