Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: PL/pgSQL
Oracle Tips by Burleson

Support for PL/pgSQL is fairly robust.  All PL/pgSQL stored procedures are functions.  That means that only SPL allows you to create procedures and packages.

To compile a PL/pgSQL function, enter the code in the SQL Interactive window and hit the running man icon (as you would for a SPL procedure). 

In the navigator, under the functions node (Figure 5.43), you can get a list of all available functions.  EnterpriseDB SPL functions have a shark's head icon next to them and PL/pgSQL functions have an elephant's head next to them.

Figure 5.43: Developer Studio Function Icons

You will use the SQL Interactive window to develop PL/pgSQL code just as you do with SPL.  You must follow naming and coding requirements as defined for PostgreSQL.

Other Languages

At this time, Developer Studio does not provide robust support for the additional languages that are standard with PostgreSQL distributions (such as PL/TCL and PL/Perl).  You may compile them using Developer Studio, but they do not show up in the Developer Studio navigator.

Compilation is achieved in the same way as SPL.  Enter your code in the SQL Interactive window and hit the running man to compile. 

The easiest way to see these other language code routines is to use a tool that supports them (which I speak of below).  You may also select this information from several system catalog tables.

I find the easiest route is to use the routines catalog table in the ANSI information schema catalog.

SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE external_language IN ('PLPGSQL', 'PLPERL', 'PLPERLU', 'PLTCL', 'PLTCLU');

This returns the values in Figure 5.44:

Figure 5.44: Non-SPL Function List

If you double-click on the routine_definition column of one of the rows, you can get an indented, printable view of the function (Figure 5.45):

Figure 5.45: Display Printable Code

You can also export this code to a text file via the Use File and Export button.  Enter the file name (including directories and drives) and press Export.  You can view and save any code in this manner, including SPL code. 

To close this window, hit the X in the upper right hand corner. 

Backup and Restore

Developer Studio provides a method for backing up databases (including DDL and data or just data).  For large databases, you will want to use an OS level backup tool or run scheduled backups (I cover scheduling in a later chapter).  However, for most databases under a few gigabytes, the Developer Studio backup and restore tool is sufficient.

A Developer Studio backup is comparable to an Oracle Export.  The Developer Studio restore works much like the Oracle Import. 

To backup a database, choose the database you wish to backup in the Navigator and select backup from the Tools menu.  This will launch the Backup Database dialog (Figure 5.46).

Remember that this is a database level backup, not a cluster backup.  To backup a cluster, you must backup each database in the cluster.

Figure 5.46: Backup Options

The options on the backup screen are:

* Table – You may choose a single table or all tables.  Code will also be backed up.

* Dump Method – The copy method seems to be the most performant to me.  Copy copies all values in a loadable format.  The other two options include Insert statements with and without column lists.  Using Insert with column lists is the most portable but creates the largest export file.

* Content Selectivity – This determines how much of the database to backup.  Choosing Only Data dumps data only, choosing Only Schema dumps all of the DDL.  Choosing Data and Schema takes a full database snapshot.

* Verbose – The verbose mode displays each object that has been backed up in the dialog following this one.

* Dump To File – This is the location, including path, for the dump file.

Execute the backup by pressing Ok.  While the backup is running, you will get a pg_dump output dialog (Figure 5.47).  This screen will also provide a result code at completion.

Figure 5.47: pg_dump Backup Output Dialog

Restoring a database is just as easy as backing one up.   Choose Restore from the Tools menu and enter the file name to restore from on the Restore dialog (Figure 5.48).

Figure 5.48: Restore Dialog

When you press the Ok button, you will get again get a pg_dump dialog (Figure 5.49).  This time it will display all objects restored.

Figure 5.49: pg_dump Restore Output Dialog

I would recommend that you keep scripts for all of your code and DDL and just use backup and restore for data.  The main reason for this is that you can have much granular control over your database objects and you can check each object into a version control system.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter