application , security , web-services

Useful SQL Commands

February 28, 2010

SQL Commands

Begin Statements that make up the block.
Built In functions Most SQL data functions are supported within PL/SQL blocks.
Code storage Blocks may be stored within an Oracle database as procedures, functions, packages (a group of blocks) and triggers.
Composite Datatypes Records allow groups of fields to be defined and manipulated in PL/SQL blocks.
Connect userid/password Connect to another schema.
Connect userid/password@instname Connect to another schema on another database instance.
Cursor handling Cursors (a memory area holding a result set) can be explicitly defined and manipulated allowing the processing of multiple rows. A group of PL/SQL system attributes provide the ability to test a cursor’s internal state.
Declare Definition of any variables or objects that are used within the declared block.
Describe tablename Describe the structure of the named table, abbreviate to DESC.
Ed Start the local text editor and load the last SQL command. When you save the text file the SQL will be placed back into SQL *Plus.
End; End of block marker.
Exception All exception handlers.
Exception handling Blocks have the ability to trap and handle local error conditions (implicit exceptions). You may also self generate explicit exceptions that deal with logic and data errors.
Flow Control
Help Call system help (if installed)
Host command Run command on local shell (UNIX or DOS)
Load commandfilename Load a command file but don’t run it.
Pause In command file, forces command file to halt execution until any key is pressed.
Prompt text Output a line of text to any user of the command file with the PROMPT in it.
Remark Comment line in a command file.
Save commandfilename Save the last SQL command to the named command file
Set Used to set SQL *Plus environment variables.
Spool filename Write all console text from this point on to named text file.
Spool Off Close any open spool file, stop spooling.
Sql support All SQL statements are supported within PL/SQL blocks including transaction control statements.
Start commandfilename Load a command file and run it, also use @commandfilename.
Variables and Constants These objects are used to store and manipulate block level data. They can be CHAR, Varchar2, Number, Date or Boolean data types.

www.bestitdocuments.com