Dani's IT Blog

…moving from Oracle to Software Development

Posts Tagged ‘PL/SQL Basics

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

leave a comment »

Unbelievable but true. After talking about EXECUTE IMMEDIATE since 2 posts, I will finally show you how to use it. Basically EXECUTE IMMEDIATE takes a string as argument, interprets it and executes it as a statement, which is the same as what EVAL does in shell scripting.

It’s behavior can be tested with the following.

CONNECT t/t

SELECT table_name FROM user_tables;

DECLARE
  table_name VARCHAR2(50);
BEGIN
  table_name := 'exec_immediate_test_table';
  EXECUTE IMMEDIATE 'CREATE TABLE '||table_name||' (x NUMBER)';
END;
/

SELECT table_name FROM user_tables;

One thing you might consider a bit special is, you don’t need to use a semicolon or slash to terminate the statement. If we think about what this sings are used for, we realize, that they tell Oracle when a statement is finished and should be executed, then it makes sense. EXECUTE IMMEDIATE takes a string and executes it as a statement, so the end of the string is the end of the statement and no semicolon or slash is needed.

Now we can combine our knowledge and use it to drop all tables of a schema.

connect t/t;
SELECT table_name FROM user_tables;
SET SERVEROUTPUT ON

BEGIN
  FOR tables IN (SELECT 'DROP TABLE "'|| table_name ||'"' drop_statement FROM user_tables)
  LOOP
     --output to see what's going on
     dbms_output.put_line(tables.drop_statement);
     --execute the drop table statement
     EXECUTE IMMEDIATE tables.drop_statement;
  END LOOP;
END;
/

SELECT table_name FROM user_tables;

To test my code in your TEST database, please use the create schema code provided in the first part of this series.

Written by danirey

June 22, 2009 at 17:01

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

PL/SQL Basics für den DBA, Teil IV (…oder wie die Arbeit als DBA Spass macht)

leave a comment »

Unglaublich aber wahr. Nachdem ich nun schon seit 2 Blog Einträgen von EXECUTE IMMEDIATE erzähle, werde ich nun tatsächlich auch zeigen wie man es verwendet. Kurz gesagt verwendet EXECUTE IMMEDIATE einen String als Argument, interpretiert diesen und führt in danach als Befehl aus. Dies entspricht dem EVAL, welches einigen von Shell Scripts her bekannt ist.

Wie man es verwenden kann, sieht man im folgenden Beispiel.

CONNECT t/t

SELECT table_name FROM user_tables;

DECLARE
  table_name VARCHAR2(50);
BEGIN
  table_name := 'exec_immediate_test_table';
  EXECUTE IMMEDIATE 'CREATE TABLE '||table_name||' (x NUMBER)';
END;
/

SELECT table_name FROM user_tables;

Was am Anfang vielleicht ein wenig komisch wirkt, ist die Tatsache, dass der String weder mit Semikolon noch mit Slash abgeschlossen wird. Diese Zeichen werden von Oracle benötigt um zu wissen, wann ein Statement abgeschlossen ist. EXECUTE IMMEDIATE verwendet aber einen String als Argument, somit ist klar, dass das Ende des Strings auch das Ende des Statements ist. Deshalb wird keine Semikolon oder Slash benötigt.

Mit dem Erlernten können wir jetzt alle Tabellen eines Schemas löschen.

connect t/t;
SELECT table_name FROM user_tables;
SET SERVEROUTPUT ON

BEGIN
  FOR tables IN (SELECT 'DROP TABLE "'|| table_name ||'"' drop_statement FROM user_tables)
  LOOP
     --output to see what's going on
     dbms_output.put_line(tables.drop_statement);
     --execute the drop table statement
     EXECUTE IMMEDIATE tables.drop_statement;
  END LOOP;
END;
/

SELECT table_name FROM user_tables;

Um meinen Code in ihrer TEST Datenbank laufen zu lassen, erstellen Sie bitte vorgänig das Testschema, welches mit dem Code aus dem ersten Teil dieser Serie erstellt werden kann.

Written by danirey

June 22, 2009 at 17:00

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

leave a comment »

In the last two posts I showed you some basics of SQL and PL/SQL. We can now combine this knowhow to get a powerful tool for DBAs. Maybe you want to change multiple objects in a scripts, without copy&paste output nor writing files. To achieve this, we need CURSOR, LOOP and EXECUTE IMMEDIATE altogether combined in PL/SQL. Again three new things to learn, seems like we will never finish this task.

The simplest way to build a loop and read a cursor is by using an implicit cursor. This could look like this.

BEGIN
FOR object IN (SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10) LOOP dbms_output.put_line('object '||object.owner||'.'||object.object_name||' is of type '||object.object_type); END LOOP; END; / [/sourcecode] This loop works quite like an iterator in Java, Perl and other languages. But what happend to our cursor, where is it? Oracle opens an implicit cursor for you, when you use this FOR - LOOP notation. With an explicit cursor the, the same code would look like this. [sourcecode language='sql'] DECLARE CURSOR cObject IS SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10; vOwner dba_objects.owner%TYPE; vObjectName dba_objects.object_name%TYPE; vObjectType dba_objects.object_type%TYPE; BEGIN OPEN cObject; LOOP FETCH cObject INTO vObjectName, vOwner, vObjectType; EXIT WHEN cObject%NOTFOUND; dbms_output.put_line('object '||vOwner||'.'||vObjectName||' is of type '||vObjectType); END LOOP; CLOSE cObject; END; / [/sourcecode] As we see, with an explicit cursor we have additional stuff to code. In the end this means, use implicit cursor where ever possible. I will show you some examples where you need explicit cursors, and even examples where you need a more detailed and complex notation, with which you are able to explicitly parse, fetch and so forth.

Written by danirey

June 16, 2009 at 18:55

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

PL/SQL Basics für den DBA, Teil III (…oder wie die Arbeit als DBA Spass macht)

leave a comment »

In den letzen zwei Posts habe ich einige SQL und PL/SQL basics präsentiert. Jetzt können wir einen Schritt weiter gehen und das Erlernte kombinieren, dadurch erhalten wir ein nützliches Werkzeug für die tägliche Arbeit als DBA.
Das Hauptziel ist es immer noch ein Tool zu erhalten mit dessen Hilfe wir mehrere Objekte in einem Schritt verändern können, ohne dass wir Befehle kopieren- oder ein generiertes Script ausführen müssen. Um das zu ermöglichen müssen wir noch CURSOR, LOOP und EXECUTE IMMEDIATE kennen lernen. Wenn wir CURSOR, LOOP und EXECUTE IMMEDIATE mit dem KnowHow aus Teil 1 und 2 kombinieren, können wir unser Ziel erreichen.

Die einfachste Möglichkeit einen Loop zu schreiben und einen Cursor auszulesen, ist das Verwenden eines impliziten Cursors. Das könnte etwa wie folgt aussehen.

BEGIN
FOR object IN (SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10) LOOP dbms_output.put_line('object '||object.owner||'.'||object.object_name||' is of type '||object.object_type); END LOOP; END; / [/sourcecode] Dieser Loop funktioniert ähnlich wie ein Iterator in Java, Perl und vielen anderen Programmiersprachen. Aber was ist mit dem versprochenen Cursor, der ist ja nirgends definiert?! Das muss er auch nicht, Oracle ist so freundlich und erstellt ihn für uns. Deshalb spricht man auch von einem impliziten Cursor. Mit einem expliziten Cursor würde der selbe Code etwa wie folgt aussehen. [sourcecode language='sql'] DECLARE CURSOR cObject IS SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10; vOwner dba_objects.owner%TYPE; vObjectName dba_objects.object_name%TYPE; vObjectType dba_objects.object_type%TYPE; BEGIN OPEN cObject; LOOP FETCH cObject INTO vObjectName, vOwner, vObjectType; EXIT WHEN cObject%NOTFOUND; dbms_output.put_line('object '||vOwner||'.'||vObjectName||' is of type '||vObjectType); END LOOP; CLOSE cObject; END; / [/sourcecode] Wie wir sehen können erhalten wir dadurch einiges an zusätzlichen Code. Für uns bedeutet das, dass wir in den meisten Fällen einen impliziten Cursor verwenden können, da dieser viel einfacher zu schreiben ist. Ich werde aber in späteren Posts noch Situationen zeigen, in denen ein expliziter Cursor benötigt wird und werde auch noch auf eine noch komplexere Schreibweise zu sprechen kommen, bei welcher auf PARSE, EXECUTE und FETCH separat ausgeführt und definiert werden.

Written by danirey

June 16, 2009 at 18:45

PL/SQL Basics für den DBA, Teil II (…oder wie die Arbeit als DBA Spass macht)

leave a comment »

In meinem letzten Artikel habe ich gezeigt wie das zusammensetzen von fixen Textblöcken und Daten aus der Datenbank das Leben des DBA vereinfachen können. Leider musste man mit der Lösung von letztem Mal immer noch die DDL Statements mittels Copy&Paste ausführen, oder zumindest in einer Datei auf dem Filesystem zwischenspeichern. Ich nehme mal an, bei einigen hat sich dabei die Frage gestellt: Was tue ich wenn dies nicht möglich ist?

Natürlich hat Oracle auch in diesem Fall eine Lösung für uns. Wir können uns PL/SQL und EXECUTE IMMEDIATE zu Hilfe nehmen. Letzteres ist vergleichbar mit EVAL in einem Shell Script. Bevor wir aber diese Methode verwenden, sollten einige PL/SQL Basics verstanden sein.

Es gibt zwei Möglichkeiten PL/SQL in einer Oracle Datenbank zu verwenden. Entweder speichern wir den Code in Form von Objekten wie Procedures, Functions oder Triggern oder wir verwenden einen anonymen PL/SQL Block. Falls PL/SQL AdHoc verwendet wird, drängt sich der Einsatz eines anonymen PL/SQL Block auf, wir konzentrieren uns deshalb erst Mal auf diese Technik.

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

Ein anonymer PL/SQL Block besteht immer aus mindestens zwei Komponenten. Die Deklaration ist Optional, sie wird nur benötigt wenn Variabeln definiert werden. Setzen wir Beispielsweise folgendes Statement in sqlplus ab,

exec dbms_stats.gather_schema_stats('SCOTT');

so konvertiert sqlplus dies für uns zu

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

Dabei haben wir direkt 4 verschiedene Dinge gelernt. DECLARE ist Optional, PL/SQL ist NICHT Case Sensitiv (Gross- und Kleinschreibung spielt keine Rolle), wir können ohne Probleme Einzeiler schreiben mit PL/SQL und EXEC ist nur ein sqlplus Befehl welcher in einen anonymen PL/SQL Block übersetzt wird.

Wenn man eine neue Programmiersprache lernt, so ist das erste im Normalfall ein ‘Hello World!’ Programm. Mit PL/SQL kann dies wie folgt implementiert werden.

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

Hier geht nun das Drama los. Die meisten werden es nicht realisiert haben, aber diese Ausgabe wird nicht direkt auf dem Bildschirm angezeigt. Statt dessen landet die Ausgabe als Erstes in einem Buffer, welcher vom Client ausgelesen werden muss. Abhängig von der Implementation des Clients geschieht dies automatisch, oder eben nicht. Hinzu kommt, dass dies abhängig vom Client während oder erst nach der Ausführung eines PL/SQL Blocks geschieht. Leider liest sqlplus den Buffer standardmässig gar nicht aus und kann ihn auch nicht während der Ausführung eines PL/SQL Blocks auslesen. Um sqlplus den Buffer auslesen zu lassen, muss der Parameter SERVEROUTPUT auf ON gesetzt werden.

SET SERVEROUTPUT ON

Written by danirey

June 9, 2009 at 18:48

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

Written by danirey

June 9, 2009 at 06:43

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

PL/SQL Basics für den DBA, Teil I (…oder wie die Arbeit als DBA Spass macht)

leave a comment »

Dies ist mein erster Blog Eintrag. Ich habe vor eine Serie über “PL/SQL Basics für den DBA” zu schreiben, sowie verschiedene interessante Themen Rund um Oracle zu behandeln. Alle Blog Einträge werden auf Englisch erscheinen, einige Themen, von denen ich denke, dass das Pulbikum im Deutschen Sprachraum genügend gross ist, werden zusätzlich in Deutsch erscheinen. Dazu wird auch die Serie “PL/SQL Basics für den DBA” gehören

[English] As I said in German, I will write all my posts in English and a part of it will be available in German as well. If you prefer English, please look out for the English version of this post.[/English]

Als DBA müssen immer wieder Änderugen an einer Menge von Datenbankobjekten machen. Zum Beispiel:

  • Lese Zugriff auf alle Objekte eines Schemas einem anderen Datenbank Benutzer erteilen
  • Löschen aller Tabellen eines Schemas
  • Verschieben aller Indexe eines Schemas von einem Tablespace in einen anderen

Oftmals sehe ich DBA’s welche versuchen diesen Job mit Hilfe von grafischen Tools, wie Toad oder SqlDeveloper und einem Text Editor auszuführen. Aber eigentlich liesse sich die Arbeit einiges einfacher, mit Hilfe der Standardmittel von SQL und der Oracle Datenbank, ausführen.

Um unser Ziel zu erreichen, können wir die Tatsache nutzen, dass man mit SQL nicht nur Daten, welche in Tabellen gespeichert sind selektieren kann. Es ist auch möglich fixe Textblöcke zu selektieren.

SQL> SELECT 'hello world!' FROM dual;

‘HELLOWORLD!’
————————————
hello world!

Dies können wir nutzen um fixe Textblöcke mit Daten, welche in der Datenbank gespeichert sind zu kombinieren.

SQL> SELECT 'The signs O and '|| dummy ||' are needed to play TicTacToe' FROM dual;

‘THESIGNSOAND’||DUMMY||’ARENEEDEDTOPLAYTICTACTOE’
—————————————————————-
The signs O and X are needed to play TicTacToe

Aber, wie hilft uns das jetzt bei unserer Aufgabe? Erinnern wir uns an den Auftrage “Lösche alle Tabellen eines Schemas”. Die Information welche Tabellen zu einem Schema gehören finden wir in der View USER_TABLES (natürlich auch in ALL_TABLES und DBA_TABLES). Wenn wir diese Informationen mit dem Statement zum Löschen einer Tabelle kombinieren, können wir die Befehle zum Löschen aller Tabellen eines Schemas generieren.


CONNECT t/t

SQL> SELECT ‘DROP TABLE “‘|| table_name ||'”;’ FROM user_tables;
‘DROPTABLE”‘||TABLE_NAME||'”;’
—————————————————————-
DROP TABLE “T2”;
DROP TABLE “T1”;

2 rows selected.

Wer meine Scripts in der eigenen TEST Datenbank nachvollziehen möchte, kann dafür mit den folgenden Statements ein Test Schema erstellen.

CREATE USER t IDENTIFIED BY t;
GRANT CONNECT,RESOURCE TO t;
CONNECT t/t;
CREATE TABLE t1 (x number);
CREATE TABLE t2 (x number);
SELECT table_name FROM user_tables;

Jetzt müssen wir diese Befehle nur noch mittels Copy&Paste in ein geeignetes Tool (sqlplus) kopieren und die DDL Statments ausführen. Falls wir Copy&Paste nicht verwenden können- oder wollen, zum Beispiel, weil es in einem Patch verwendet werden soll, so können wir die Befehle mittels SPOOL aus einem Script in eine Datei schreiben. Diese kann dann via sqlplus als Script ausgeführt werden. Der dafür notwendige Code könnte etwa wie folgt aussehen.

CONNECT t/t

set heading off
set feedback off
spool drop_user_tables.sql
prompt spool drop_tables.log
SELECT 'DROP TABLE "'|| table_name ||'";' FROM user_tables;
prompt spool off
spool off
@drop_user_tables.sql

Written by danirey

June 6, 2009 at 20:40