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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: SQL Terminal Interface
Oracle Tips by Burleson
 

SQL Terminal is a very basic, character based interface.  Most of the examples in this book were run in the SQL Terminal. 

When you launch the SQL Terminal, you are provided with help commands.

Welcome to edb-psql 8.1.5.21, the EnterpriseDB interactive terminal.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with edb-psql commands

       \g or terminate with semicolon to execute query

       \q to quit

Warning: Console code page (437) differs from Windows code page (1252)

         8-bit characters may not work correctly. See psql reference

         page "Notes for Windows users" for details.

testdb=#

The \h command provides a list of SQL commands.  The majority of these commands are covered in Chapter 3 of this book.

The \? command is a very useful command as it lists all of the SQL Terminal specific commands.  Rather than list all of the commands here, I suggest you log into the SQL Terminal and run the command yourself.  With the commands in SQL Terminal, you can connect to other databases, turn query timing on, change OS file system directory, set and unset variables and access the query buffer.

For quick SQL commands, I often choose the SQL Terminal because it loads fast and has minimal overhead.  For heavy or interactive coding, I usually choose SQL Interactive.

SQL Interactive Interface

The SQL Interactive window is the GUI replacement to the SQL Terminal.  Results can be formatted in a grid, or displayed textually (as in SQL terminal).

The screen shot in Figure 5.41 is in Grid mode and is displaying an error.  You can see that the error is at position 3, which means I have a misspelling in the SELECT statement.  That error is fixed in Figure 5.40 and the results of the query are displayed in a grid.

Figure 5.40: SQL Interactive Notices

Figure 5.41: SQL Interactive Grid

The running man icon in the upper left runs the command in the SQL pane (or press F5).  If the SQL pane contains SQL statements, those statements are executed.  If the SQL pane contains procedural code (such as an anonymous block), the code is executed.  If the SQL pane contains create statements for stored procedures, the code is compiled.

The three icons next to the running man allow you to open an existing query text file, or save the query (existing or new).

The drop down next to the icons determines the format of your output: Grid mode for a grid or Text mode for PSQL.

The next three icons are transaction control.  I talked about transaction control in detail in chapters 2 and 3.

Below those icons is the history drop down.  Select the down arrow and get a drop down list of all previously run commands.

There is a limit rows checkbox next to the history drop down.  If you select that checkbox, you can enter the number of rows that you wish to limit.  This is the equivalent of adding a LIMIT clause in your query.

Below that is the results window.  At the top of the results window are a set of tabs.  Each time you run a different SQL command, you will get a new tab for that command.  If you click on the Notices tab (Figure 5.42), you will see statistical information for the last run command.

Figure 5.42: Grid Mode Notices

When you use the wizard to create a stored procedure, Developer Studio launches SQL Interactive with a template already loaded.

The templates may change over time as more functionality is added (or modified) in EnterpriseDB Advanced Server and in SPL.  Below are the current code templates provided by Developer Studio.  For each of these templates, change the names and add your own code.

Detailed SPL programming is provided in Chapter 4.

Create Package Template

CREATE OR REPLACE PACKAGE testschema.<package_name>
IS
  PROCEDURE <procedure_name>;
  FUNCTION <function_name> RETURN INTEGER;
END;

CREATE OR REPLACE PACKAGE BODY testschema.<package_name>
IS
  PROCEDURE <procedure_name>
  IS
  BEGIN

    --Replace with your code

    NULL;
  END;
  FUNCTION <function_name> RETURN INTEGER
  IS
  BEGIN

    -- Add your code here

  RETURN -1;
  END;
BEGIN
  NULL; -- Replace with initialization code
END;

Create Procedure Template

CREATE OR REPLACE PROCEDURE testschema.<PROCEDURE_NAME>
IS
BEGIN

  --Replace with your code

  NULL;
END;

Create Function Template

CREATE OR REPLACE FUNCTION testschema.<function_name> RETURN INTEGER
IS
BEGIN

  -- Add your code here

  RETURN -1;
END;

Create Trigger Template

CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE INSERT OR UPDATE OR DELETE ON testschema.<table_name>
FOR EACH ROW
BEGIN
  NULL; -- Replace with your code
END;
/

Developing with Other Languages

Developer Studio also allows you to develop in PL/pgSQL, PL/TCL and any of the other languages supported by EnterpriseDB Advanced Server and PostgreSQL. 

        
     

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