Dani's IT Blog

…moving from Oracle to Software Development

PL/SQL Basics for the DBA. Part II (… or how to start having fun working as a DBA)

leave a comment »

In my last post I explained how to use some simple string concatenation to make the DBA’s life easier. But we still had to copy&paste the commands, or at least write them to the filesystem and use it as a script. I guess for many of you the question came up: What shall I do if this is not an option?

Of course Oracle has a solution for you. You can use PL/SQL and EXECUTE IMMEDIATE, which works the same as EVAL in shell scripting. But before we can use this we should learn some basics about PL/SQL.

There are two ways to use PL/SQL in an Oracle database. Either you store it in objects like procedures, functions or triggers or you use an anonymous PL/SQL block. If you use PL/SQL in an ad hoc kind of manner, you usually use an anonymous PL/SQL block.

DECLARE
  variable declaration;
BEGIN
  your code here;
END;
/

An anonymous PL/SQL block consists out of two blocks. The declaration is optional, you only need it if you want to use variables. For instance, if you run following statement out of sqlplus,

exec dbms_stats.gather_schema_stats('SCOTT');

then sqlplus converts this to

 begin dbms_stats.gather_schema_stats('SCOTT'); end;
 /

Now you immediately learned 4 things. DECLARE is optional, PL/SQL is NOT case sensitive, you can write one liners with PL/SQL and exec is a sqlplus command, not SQL or PL/SQL.

First thing to learn in a new language is usually, how to write a ‘Hello World!’ program. With PL/SQL this can be achieved with

BEGIN
  dbms_output.put_line('Hello World!');
END;
/

And now the trouble starts. Although you might not have realized it, the output is not written to your screen directly. It ends up in a buffer, which has to be read by the client. Depending on the clients implementation this is done automatically or not. And, as well, depending on the client, this happens during the execution of your code or afterwards. Unfortunately sqlplus does neither read the buffer with the standard configuration nor is it able to read the buffer while executing a PL/SQL block. To tell sqlplus that it should read the output buffer, you have to set the parameter SERVEROUTPUT to ON.

SET SERVEROUTPUT ON

Advertisements

Written by danirey

June 9, 2009 at 06:43

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: