Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Oracle PL/SQL - SQL *Plus Overview

Oracle SQL *Plus Basics

This article describes some of the basics of using SQL *Plus and the PL/SQL Language.

SQL *Plus Logins - Regular and SYSDBA

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

Login SQL Plus

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

Login as SYS as SYSDBA

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.

Connection Info SQL Plus

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.

Create Table SQL

Insert statements are similar to those in SQL Server.

INSERT statement PL SQL

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>.

Describe Table Information

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.

PL SQL Code Block

SQL *Plus PL/SQL - RowID vs RowNum

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

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.

Assignment Example

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.

Assignment Operator

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.

GOTO Example

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

All_tables

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.

Error Handling

Using PL/SQL Keyword DUAL

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

System Table DUAL