SQL *Plus Logins - Regular and SYSDBA

Type in the user ID, password and Host name and click OK. Ex/ scott (user Name), tiger (password)

To login as SYS as SYSDBA, open SQL *Plus and do not enter any of the fields and click OK.

The SQL Plus screen will prompt you for a User-Name. Enter SYS as SYSDBA. Then it will prompt for a Password. The password of the user plus the hostname should be entered here. Ex/ mypassword@HOSTNAME. You will then be connected to the Oracle database as SYS as SYSDBA.

Creating and Using a PL/SQL Table in SQL *Plus

Creating a table is very straightforward. There are a few differences from SQL Server in the datatypes such as Number vs Integer, Float, Double, etc. Use the Constraint keyword to name the Primary key.

Insert statements are similar to those in SQL Server.

Using PL/SQL Keyword DESC to Display Table Variables

Although you can go to the Oracle Enterprise Manager Console to view the columns and datatypes, an alternate way of viewing this information is available in PL SQL. Type in Desc or Describe <table name>.

Using SQL *Plus PL/SQL Anonymous Code Blocks

Anonymous blocks of code can provide information about how to use variables and DBMS_OUTPUT back to the user. This sample code shows how to form the block:
DECLARE
 <variables>
 BEGIN
  <Code Block>
 EXCEPTION
  <code block>
 END

The variables declared use the %type datatype which is the datatype of the given table column. For example, v_empno is the datatype of the emp table empno datatype of Number. The code block then selects the empno and ename from table emp into the variables and put them back out on the screen with the DBMS_OUTPUT.PUTLINE statement. The || concatenation operator puts the line together. Lastly, the / executes the code block.

SQL *Plus PL/SQL - RowID vs RowNum

This block of code shows the hidden table columns ROWID and ROWNUM.

PL/SQL Variable Assignment Example

This example demonstrates using comments (the -- in front of the text), declaration of the variables and use of the assignment operator := to set the variable.

PL/SQL Variable Assignment Operator with User Input

This example uses a variable with an assignment operator that takes user input from the screen. The & in front of text will cause the PL SQL block to prompt the user for data entry.

PL/SQL Keyword GOTO Example

This section demonstates use of an If Elsif Statement with GOTO labels. Labels are marked with <<labelname>> on both sides of the label name. Depending on the value assigned, the appropriate label will be selected and Put_line statement executed.

System Tables: This example shows the information returned by SELECT Table_Name from All_Tables;. Note the Environment variables were changed

PL/SQL Error Message: Catching No_Data_Found Exception

In the anonymous code block, in the Exception section put the code "WHEN NO_DATA_FOUND" keywords to capture the event of a SELECT statement not finding any data to put out in a DBMS_OUTPUT.PUT_LINE statement.

Using PL/SQL Keyword DUAL

This sample uses the System Table DUAL with a SELECT statement to retrieve server SYSDATE and SYSTIMESTAMP information.