Bulk  Mechanism

IT IS USED TO REDUCE THE TIME TAKEN TO RETREVE THE DATA

àBulk is one of the method to improve the performance of the applications

2 types:

                1:Bulk Collect

                2:Bulk binding

 1:Bulk Collect    In this clause we are used to fetch the data from database  into collection.

                            àselect ……into…clause

                            àcursor ….fetch…..statement

                            àdml ….returning

bulk collect practise

=====================

declare

type t1 is table of jacmel.jmplan_temp%rowtype index by binary_integer;

v1   t1;

begin

select * bulk collect into v1 from jacmel.jmplan_temp;

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i).SEGMENT1||','||v1(i).ATTR1||','||v1(i).CUSTOMER_NUMBER);

end loop;

end;

Example for bulk collect by 2 variables

---------------------------------------

DECLARE

type t1

IS

  TABLE OF VARCHAR2(50) INDEX BY binary_integer;

  v1 t1;

  v2 t1;

  CURSOR c1

  IS

    SELECT SEGMENT1,attr1 FROM jacmel.jmplan_temp;

BEGIN

  OPEN c1;

  FETCH c1 bulk collect INTO v1,v2;

  CLOSE c1;

  FOR i IN v1.first..v1.last

  LOOP

    dbms_output.put_line(v1(i)||','||v1(i));

  END LOOP;

END;

example with out bulk collect method

declare

vrow VARCHAR2(30);

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 into vrow;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/

example with bulk method

declare

type t1 is table of varchar2(30) index by binary_integer;

v1 t1;

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 bulk collect into v1;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/

Global Temporary Table

Global Temporary Tables (GTT)

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

• Creation of Global Temporary Tables
• Global Temporary Tables and Undo
• Global Temporary Tables and Redo
• Miscellaneous Features

Related articles.

• Temporary Undo in Oracle Database 12c Release 1 (12.1)
• Session Sequences in Oracle Database 12c Release 1 (12.1)
• Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)
• Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
• Global Temporary Tables (GTT) in Oracle 

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction, or the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         0

SQL>

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN test/test

SELECT COUNT(*) FROM my_temp_table;

  COUNT(*)
----------
         0

SQL>

Global Temporary Tables and Undo

Although the data in a GTT is written to the temporary tablespace, the associated undo is still written to the normal undo tablespace, which is itself protected by redo, so using a GTT does not reduce undo and the redo associated with protecting the undo tablespace.

The following code creates a conventional table, populates it and checks the amount of undo used by the transaction.

DROP TABLE my_temp_table PURGE;

-- Create conventional table.
CREATE TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

-- Populate table.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       302       6237

SQL>

We now repeat the previous test, but this time using a GTT.

DROP TABLE my_temp_table PURGE;

-- Create GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

-- Check undo used by transaction.
SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       303       6238

SQL>

TRUNCATE TABLE my_temp_table;

We can see, there is no significant difference in the undo used.

 Oracle 12c introduced the concept of Temporary Undo, allowing the undo for a GTT to be written to the temporary tablespace, thereby reducing undo and redo.

Global Temporary Tables and Redo

If you've read the previous section, you will already know the relationship between global temporary tables and redo. The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior to Oracle 12c, all undo associated with DML against a GTT is written to the normal undo tablespace, which is itself protected by redo. As a result, using a GTT reduces the amount of redo generation, but does not eliminate it. Another why of describing this is, using a GTT removes direct redo generation, but not indirect redo generation cause by undo.

The following code creates a conventional table, populates it and checks the amount of redo generated by the transaction.

DROP TABLE my_temp_table PURGE;

-- Create conventional table.
CREATE TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

SET AUTOTRACE ON STATISTICS;

-- Populate table.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
        106  recursive calls
      20119  db block gets
       2603  consistent gets
         16  physical reads
   23039396  redo size
        853  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

We now repeat the previous test, but this time using a GTT.

DROP TABLE my_temp_table PURGE;

-- Create GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         45  recursive calls
      15333  db block gets
       2381  consistent gets
         16  physical reads
    2944180  redo size
        862  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

TRUNCATE TABLE my_temp_table;

We can see we have created an order of magnitude less redo when using the GTT, but we have not eliminated it.

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

For more information see:

• Temporary Tables
• CREATE TABLE - GLOBAL TEMPORARY
• Temporary Undo in Oracle Database 12c Release 1 (12.1)
• Session Sequences in Oracle Database 12c Release 1 (12.1)
• Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)
• Temporary Undo for Global Temporary Tables (GTTs) in Oracle 12c
• Global Temporary Tables (GTT) in Oracle 

Hope this helps. Regards Tim...

Bulk Binds

You should probably be reading the update of this article here.
Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL engine. There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk binding the whole collection to the DML statement in one operation.
First we create a test table.

CREATE TABLE test1(
  id           NUMBER(10),
  description  VARCHAR2(50));
  
ALTER TABLE test1 ADD (
  CONSTRAINT test1_pk PRIMARY KEY (id));
  
SET TIMING ON

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FOR i IN t_id.first .. t_id.last LOOP
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    DELETE test1
    WHERE  id = t_id(i);
  END LOOP;
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.00

Using the FORALL construct to bulk bind the inserts this time is reduced to 18 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
    
  FORALL i IN t_id.first .. t_id.last
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
    
  FORALL i IN t_id.first .. t_id.last
    DELETE test1
    WHERE  id = t_id(i);
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.05

A collection must be defined for every column bound to the DML which can make the code rather long winded, but the performance improvements more than make up for this.
Bulk binds can also improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To show this we must first load our table with some data.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));

  COMMIT;
END;
/

Populating two collections with 10,000 rows using a FOR..LOOP takes approximately 1.02 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
  
  CURSOR c_data IS
    SELECT *
    FROM   test1;
BEGIN
  FOR cur_rec IN c_data LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := cur_rec.id;
    t_description(t_description.last) := cur_rec.description;
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.02

Using the BULK COLLECT INTO construct reduces this time to approximately 0.01 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description 
  BULK COLLECT INTO t_id, t_description FROM test1;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Bulk binding reduces the context switches between SQL and pl/SQL engines. It enhances the performance but thr memory consumption would be high.

Calling Request Set from PLSQL on Specific Time

CREATE OR REPLACE PROCEDURE XXAP_SUBMIT_REQUEST_SET (
P_errbuf    OUT VARCHAR2,
P_retcode   OUT NUMBER)
AS
V_REQUEST_SET_EXIST   BOOLEAN := FALSE;
req_id                INTEGER := 0;
l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
srs_failed            EXCEPTION;
submitprog_failed     EXCEPTION;
submitset_failed      EXCEPTION;
l_start_date          VARCHAR2(250);
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Calling set_request_set…');
V_REQUEST_SET_EXIST :=
FND_SUBMIT.set_request_set (application   => 'EC',
request_set   => 'JM856ASNOUTBOUND');

IF (NOT V_REQUEST_SET_EXIST)
THEN
RAISE srs_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling submit program first stage');
l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW2',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW1',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'XXJM856OUTBOUND_WLMRT',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

/*l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('XXAP',
'XXAP_FOURTH_PROGRAM',
'STAGE40');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;*/

fnd_file.put_line (fnd_file.LOG, 'Calling submit_set…');

--–l_start_date is to schedule the request
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')
  into l_start_date
FROM dual
WHERE 1                            =1
AND TO_CHAR( SYSdate,'HH24:MI:SS') > '13:00:00'
AND TO_CHAR( SYSdate,'HH24:MI:SS') < '14:30:00'
AND  TO_CHAR(SYSDATE,'DY')         IN('MON','TUE','WED','THU','FRI');

req_id :=
FND_SUBMIT.submit_set (start_time    => l_start_date,
sub_request   => FALSE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;
EXCEPTION
WHEN srs_failed
THEN
p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitprog_failed
THEN
p_errbuf := 'Call to submit_program failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitset_failed
THEN
p_errbuf := 'Call to submit_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN OTHERS
THEN
p_errbuf := 'Request set submission failed – unknown error: ' || SQLERRM;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
END;

Procedures parameters

As we all know there are two mandatory parameters that need to be pased for all the procedures called 
1.ERRBUFF 
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....

PLSQL INTERVIEW QUESTIONS

1) PL/SQL is a procedural language, extension to SQL. It offers modern software engineering features such as,
a) Data Encapsulation
b) Exception handling
c) Information hiding & Overloading using Packages
d) Object orientation

2) PL/SQL is not an Oracle product, but the technology used by the oracle server and by certain Oracle tool. Blocks of PL/SQL are passed to and processed by a PL/SQL Engine, which may reside within the tool or within the Oracle Server. When the blocks of PL/SQL are submitted from Oracle Pre-compiler such as Pro*C or Pro*Cobol program, user exit, iSQL*Plus, or Server Manager, the PL/SQL Engine in the Server processes them. It separates SQL Statements and sends them individually to the ‘SQL Statements Executor’.

3) A single transfer is required to send the block of PL/SQL from the application to the Oracle Server, thus improving performance. PL/SQL Code is stored in Oracle Server as a subprograms that can be referenced by any number of applications connected to the database.

4) Many Oracle Tools like Oracle Forms Developer, Oracle Reports Developer, Oracle Graphics Developer have their own PL/SQL Engine, which is independent of the engine present in Oracle Server.
Procedural Statements are processed by Procedural Statement Executor, which resides in PL/SQL Engine and SQL Statements are processed by SQL Statement Executor, which resides in Oracle Server. This reduces the work that is sent to the oracle server and number of memory cursors that are required.

5) Benefits of PL/SQL:

a) It can be used to group SQL statements together within a single block and to send the entire block to the server in a single call, thereby reducing network traffic. Without PL/SQL, the SQL Statements are sent to the Oracle Server one at a time.

b) PL/SQL can also operate with Oracle Server application development tools such as Forms & Reports. By adding procedural processing power to these tools, PL/SQL enhances performance.

c) Procedures & Functions of Forms & Reports are different from those stored in the Database, although there general structure is same. Because stored Subprograms are database objects and are stored in data dictionary and can be accessed from any number of applications, including Forms & Reports.

6) Oracle Portal is an Oracle Portal Developer tool previously known as WebDB.

7) PL/SQL Contains one or more blocks. They are mainly of two types:

a) Anonymous Blocks: Unnamed block which are declared at the point in an application where they are to be executed and are passed to the PL/SQL Engine for execution at run time.
b) Subprograms: Named PL/SQL Blocks that can accept parameters and can be invoked. We can declare them either as procedures or functions.

8) Block structure for Anonymous Block is:
a) Declare(Optional)
b) Begin(Mandatory)
c) Exception(Optional)
d) End(Mandatory)
Declare, Begin & Exception are not followed by Semicolons. End and all other PL/SQL Statements require semicolons to terminate the statement.

9) Variables: This are used mainly for the following:
a) Temporary storage of Data
b) Manipulation of Stored Values
c) Reusability
d) Ease of Maintenance
Forward references are not allowed, you must declare a variable before referencing it in other statements.

There are 2 main types of Variables:
> PL/SQL Variables:
a) Scalar (Holds a Single Value with Scalar Datatype- Varchar2, Number, Date.etc)
b) Composite (Eg: Record Datatype, Table Datatype)
c) Reference (Hold values called pointers, Eg: Refcursor)
d) LOB (Hold Large objects like Graphic Images)

> Non PL/SQL Variables: Bind and Host Variable

10) Constant Variables Must be initialized in its declaration, otherwise, you get a compilation error. Other variables are also initialized with Default Reserved Word or with assignment operator(:=). It is always a good programming practice to initialize all variables.

11) Following points are important while declaring a variable:
a) Two Variables can have the same name provided they are in different blocks.
b) The Variable name should not be the same as the name of Table Column.
c) Variable name must not be longer than 30 characters. The first character must be a letter; the remaining characters can be letters, numbers, or special symbols

12) %Type Provides the Datatype of Database column to the Scalar Variable
%Rowtype is used to Declare a Record based on a Collection of Table columns.

13) Non PL/SQL Variables declared in Host Environment can be Referenced in PL/SQL Anonymous blocks by prefix Colon (:) but not in Sub-Programs.
While Declaring this Variable Do not specify Size for Datatype Number but Always Specify the Size for Datatype Char & Varchar2

14) Lexical Units can be classified as:
a) Delimeters (Arithmetic, Relational, Assignment & Comment Operators)
b) Identifiers (Variables Name)
c) Literals (Character & Date Literals must Enclosed in Single Quotes)
d) Comments (Starts with /* and Ends with */ )

15) We Cannot use ‘DECODE’ and Group Functions in PL/SQL Statements. However they can be used in SQL Statements of PL/SQL blocks.

16) The Scope of an identifier is that region of a Program unit from which you can reference the identifier. An identifier is visible only in Regions from which you can reference the identifier using unqualified name.
If the Block does not find the identifier declared locally, it looks up to the declarative section of the Enclosing(Parent) blocks. The block never looks down to Enclosed(Child) blocks or sideways to sibling blocks.

17) PL/SQL does not Directly Support DDL & DCL Statements. Select Statement Embedded in PL/SQL must require INTO Clause, Where Clause is optional.

18) Output Variables: PL/SQL Variables through which Values pass from Database to the PL/SQL Blocks
Input Variables: PL/SQL Variables through which Values pass from the PL/SQL Blocks to the Database

19) SQL Cursor: Whenever SQL Statement is issued, Oracle Server opens an area of memory in which the command is Parsed & Executed. This area is called a Cursor.
There are 2 types of Cursor:
a) Implicit Cursor (Used for Single Row Operations, No need to Open & Close)
b) Explicit Cursor (Used for Multiple Row Operations, Declared Explicitly by User and need Opening, Fetching & Closing always)
Explicit Cursor points to the current row in the Active Set. The set of rows returned by a multiple row query is called the Active Set. Close Statement Releases the Active Set of Rows.

20) Cursor Attributes: This are used in PL/SQL Statements only to Gather Information about the Execution of DML Statements. They are mainly,
a) SQL%ROWCOUNT (No. of Rows affected by SQL Statements)
b) SQL%FOUND (Evaluates to True if 1 or more rows are affected)
c) SQL%NOTFOUND (Evaluates to True if no rows are affected)
d) SQL%ISOPEN (Always evaluates to False because PL/SQL Closes Implicit Cursors immediately after they are executed)

21) There are 3 Types of Control Structures:
a) IF Statement
b) Case Expressions
c) Loop Statement

22) There are 3 forms of IF Statement:
a) IF-THEN-END IF
b) IF-THEN-ELSE-END IF
c) IF-THEN-ELSIF-END IF

23) Operations with Null:
a) True AND Null = Null
b) False AND Null = False
c) True OR Null = True
d) False OR Null = Null

24) There are 3 Types of Loops:
a) Basic Loop (Performs Repetitive Actions without Condition)
b) FOR Loop (Performs iterative Actions based on a count)
c) WHILE Loop (Perform iterative Actions based on a Condition)

25) Always Use,
a) Basic Loop when the Statements inside the loop must execute at least once.
b) While Loop if the condition has to be evaluated at the start of each iteration.
c) For Loop if the number of iterations is known.

26) There are 2 types of Composite Datatypes:
a) PL/SQL Records
b) PL/SQL Collections
1) INDEX BY Table
2) Nested Table
3) VARRAY

27) Advanced Explicit Cursor:
a) We can pass parameters to the Cursor. Each Formal Parameters in the Cursor Declarations must have a corresponding Actual Parameter in the Open Statement. When the Cursor is Opened you pass values to each of the Parameter by Position or by name. You can pass values from PL/SQL or Host Variables or from literals.
b) ‘For Update of col_name(s)’ clause in the cursor query is used to lock the affected rows when the cursor is opened, because Oracle Server releases locks at the End of Transaction. This is the last clause in Select statement even after Order by Clause if present. Optional ‘NOWAIT’ Keyword is used to tell the Oracle not to Wait if the requested number of rows are locked by another user, Control immediately returned to the program to do other work before trying to acquire the Lock. If this keyword is not used, Oracle waits until the rows are available.
c) ‘Where Current Of cursor_name’ clause is used for referencing the current row from an Explicit cursor.

28) Exception: An Exception is an identifier in PL/SQL that is raised during the execution of block. A block terminates when PL/SQL raises an exception. If PL/SQL successfully handles the exception, then the exception does not propagate to the enclosing block or calling environment, the block terminates successfully. However if there is no corresponding exception handler, block terminates with failure & exception is propagated to the calling environment
There are 3 types of Exception:
a) Predefined Exception
b) Non Predefined Exception
c) User Defined Exception

29) Most oftenly used predefined exceptions are:
a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occured)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

30) Trapping a Nonpredefined Oracle Server Exception:
a) Declare the name for the exception in Declarative section
b) Associate the declared exception with Standard Oracle Server error using ‘Pragma Exception_Init’, which is the compiler directive that tells the compiler to associate an exception name with Oracle error number
c) Reference the declared exception with corresponding exception handler routine.

31) When an exception is trapped in the When Others exception handler which is the last handler, 2 functions are used to identify those errors:
a) SQLCODE (Returns the error code)
b) SQLERRM (Returns the error message)
This functions are not directly used in SQL statement, instead they must assign their values to local variables and then this variables are used in SQL Statements.

32) Trapping User Defined Exception:
a) Declare the name for the exception in Declarative section
b) Use the ‘Raise’ Statement to raise the exception explicitly within the executable section.
c) Reference the declared exception with corresponding exception handler routine.

33) When a subblock handles an exception, it terminates normally and control resumes in the enclosing block, the remaining executable actions in that block are bypassed.

34) ‘Raise_Application_Error (Error_No, Error_Message)’ procedure communicate a predefined exception interactively by returning a nonstandard error code & message. They can be used either in Executable or Exception section or both. Error Number ranges from –20000 to –20999.

35) Modularization: It is the process of breaking up large blocks of code into smaller groups of called modules. After code is modularized, the modules can be reused by the same program or shared by other programs. It is easier to maintain & debug code of smaller modules than a single large program. Also modules can be easily extended for customization by incorporating more functionality, if required, without affecting the remaining modules of program.

36) Block Structure for PL/SQL Program is,
Header IS/AS
Declarative Section
Begin
Executable Section
Exception
Exception Section
End ;

37) Procedure: It is a type of Subprogram that performs an Action. It can be stored in Database as a Schema object for Repeated Execution. ‘Create Procedure’ Statement Stores the Source Code in the Data Dictionary even if procedure contains compilation Errors. When Procedure is Created, P-Code(Pseudocode) is generated based on Parsed code. PL/SQL Engine Executes this code when Procedure is Invoked.

38) Formal Parameters: Variables Declared in the Parameter list of Subprogram Specification.
Actual Parameters: Variables referenced in Parameter list of Subprogram Call

39) There are 3 Types of Parameters:
a) IN (Passes a Constant value from the Calling Environment into the procedure)
b) OUT (Passes a value from the procedure to the Calling Environment)
c) IN OUT (Passes a Value from the Calling Environment into the procedure & a possibly different value from the procedure back to the calling Environment using the same Parameter)

40) By Default IN Parameters are passed by Reference and OUT & IN OUT are passed by value
41) There are 3 Methods for Passing Parameters:
a) Positional (List Values in the order in which the Parameters are declared)
b) Named Association (List Values in Arbitrary Order by associating each one with its parameter name, using special syntax( =>)
c) Combination (List the first Value positionally, and the remainder using the special syntax of the named method)

42) Function: It is a named PL/SQL Block that can accept parameters and be invoked. It is used always to compute a value. A function is called as part of an expression i.e. we can invoke a function from Select Statement. DML Cannot be used in Functions.

43) Difference between Procedure & Functions:

Procedures
1) Executes as PL/SQL Statement
2) Do not Contain Return Clause in the Header
3) Can Return none, one, or many Values
4) Can Contain a Return Statement

Functions
1) Invokes as part of an Expression
2) Must contain a Return clause in the the Header
3) Must Return a Single Value
4) Must Contain at least one Return Statement

44) Package:

a) A Package usually has a Specification & Body stored separately in the Database. A Specifications is an interface to your Applications. It declares the types, variables, constants, exceptions, cursors, Pragmas which are directive to the compiler & subprograms available for use. The body fully Defines Cursors & Subprograms.

b) When you call a packaged PL/SQL Constructs for the first time, the whole package is loaded into memory. Thus later calls to constructs in the same package require no disk input/output.

c) Public package constructs are those that are declared in package specification & defined in the package body. Private package constructs are those that are defined solely within package body.

d) We can have specification without body but we cannot have body without specification.

e) We must declare a variable before it can be referred and define subprograms before called from other subprograms, because PL/SQL does not allow forward references.

f) Changes to the package body do not require recompilation of dependant constructs, whereas changes to the package specification require recompilation of every stored subprogram that references the package.

g) Data Hiding: The package body hides the definition of the private constructs so that only the package is affected if the definition changes.

h) Overloading: We can overload procedures & functions of package only i.e. We can create Multiple subprograms with the same name in the same package, each taking parameters of different number or datatype.

i) One time only Procedure: It is executed only once, when the package is first invoked within the user session. The keyword END is not used at the end of this procedure.

j) Side Effects: This are changes to database tables or public packaged variables declared in package specification. Side effects delay the execution of Query. We can use Pragma Restrict_References to ask PL/SQL Compiler to verify that function has only the side effects that you expect.

k) The state of a package variable or cursor persists across transaction within a session. The state does not persist from session to session for the same user, nor does it persist from user to user.

45) Oracle Supplied Packages:

a) DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL. Dynamic SQL Statements can be created dynamically at runtime using Variables. Such statements like DML, DDL & DCL cannot be executed statically in PL/SQL.
Eg: You use dynamic SQL to create a procedure that operates on a table whose name is not known until Runtime.
‘Execute Immediate’ statement can also perform dynamic single row Queries.

DBMS_SQL Package uses following procedures or functions:
1) OPEN_CURSOR (Opens a new cursor & assigns a cursor ID number)
2) PARSE (Parses DDL & DML Statement)
3) BIND_VARIABLE (Binds the given value to the variable)
4) EXECUTE (Execute SQL statements & returns no. of rows processed)
5) FETCH_ROWS (Retrieves a row for the specified cursor)
6) CLOSE_CURSOR (Close the specified cursor)

b) DBMS_DDL.ALTER_COMPILE(‘object_type’, ‘owner’, ‘object_name’) is used to recompile our modified PL/SQL program units

c) DBMS_DDL.ANALYZE_OBJECT(‘object_type’, ‘owner’, ‘name’, ‘method’) is used to analyze a single object. Methods includes Compute, Estimate, or Delete.

d) DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

Available Subprograms includes,
1) SUBMIT (Submits a job to a job queue)
2) CHANGE (Alters a specified job that has already been submitted to queue)
3) WHAT (Alters a job description for a specified job)
4) NEXT_DATE (Alters next execution time for a specified job)
5) INTERVAL (Alters the interval between executions for a specified job)
6) BROKEN (Disables job execution)
7) RUN (Forces a specified job to run)
8) REMOVE (Removes a specified job from a job queue)

e) DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.
Available procedures includes,
1) Put(Appends text from Procedure to the current line of line output Buffer)
2) New_Line (Places an end_of_line marker in the output Buffer)
3) Put_Line (Combines the action of Put & New_Line)
4) Get_Line(Retrieves the current line from output buffer into the procedure)
5) Get_Lines (Retrieves an array of lines from output buffer into Procedure)
6) Enable/Disable(Enables or Disables calls to the Dbms_Output Package)

f) UTL_FILE: With this package, you can read from & write to Operating system files. It is available with database version 7.3 and later and PL/SQL version 2.3 and later.
Procedures & functions are,
1) FOPEN(A Function that opens a file for input or output & returns a file handle used in subsequent I/O operations)
2) IS_OPEN(A Function that returns a Boolean value whenever a file handle refers to an open file)
3) GET_LINE(A Procedure that reads a line of text from the opened file & Places the text in the output buffer parameter)
4) PUT, PUT_LINE(A Procedure that writes a text string stored in the buffer parameter to the opened file)
5) PUTF (A formatted put procedure with 2 format specifiers:%s for string & %n for new line character)
6) NEW_LINE ( Procedure that terminates a line in an output file)
7) FFLUSH (Procedure that writes all data buffered in memory to a file)
8) FCLOSE (Procedure that closes an opened file)
9) FCLOSE_ALL (Procedure that closes all opened file handlers for session)

g) UTL_HTTP: This package allows to make HTTP Requests directly from the database. By coupling this package with DBMS_JOBS package, you can easily schedule reoccurring requests from database server out to the web. It contains 2 Functions:
1) Request ( It returns upto first 2000 bytes of data retrieved from the URL)
2) Request_Pieces (Returns a PL/SQL table of 2000 byte pieces of data retrieved from URL)
Both the functions take a string URL as a parameter, contact the site & returns HTML data obtained from the site.

h) UTL_TCP: This package enables PL/SQL applications to communicate with external TCP/IP based servers. It is useful to PL/SQL applications that uses Internet Protocols.
1) OPEN_CONNECTION
2) CLOSE_CONNECTION
3) READ_BINARY (Receives binary, text or text line data from open connection)
4) WRITE_BINARY(Transmits binary, text or text line data to open connection)

46) Trigger: It is a PL/SQL Block or a procedure associated with a table, view, schema or the database that executes implicitly whenever a particular event takes place. They are of mainly 2 types:
a) Application Triggers: Fires whenever a particular DML event occurs within an application
b) Database Triggers: Executes when a data event such as DML on a table, an Instead Of trigger on a view, or DDL statements are issued. It also executes when some user actions or database system action occurs, Eg: User logs on or DBA shuts down the database

Trigger body consists of 3 main components:
a) Trigger Timing: Before, After (For Table), Instead Of (For views)
b) Triggering Event: Insert, Update & Delete
c) Trigger type: Statement & Row
Note: 1) Colon is not used in When Condition in front of qualifier old & new
2) New & Old Qualifiers can be Referenced only in Row Level Triggers

47) A Statement Trigger is fired once on behalf of the triggering event, even if no rows are affected at all, whereas Row trigger fires each time the table is affected by the triggering event, if triggering event affects no rows then Row trigger is not executed.

48) Instead Of Trigger: This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contain joins or Group Functions. This triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

49) Managing Triggers:
a) Alter Trigger trigger_name Disable/Enable
b) Alter Table table_name Disable/Enable All Triggers
c) Alter Trigger trigger_name Compile

50) Triggering User Event:
a) CREATE, ALTER or DROP
Triggering Database or System Event:
a) AFTER SERVERERROR
b) AFTER LOGON
c) BEFORE LOGOFF
d) AFTER STARTUP
e) BEFORE SHUTDOWN

51) Mutating Table: Table under the state of Transition is called Mutating Table. If table is being modified by DML statement and trigger code also queries the same table that is being modified then table goes under Mutation. Mutating Table is not considered for Statement Triggers.
We Cannot Write Commit, Rollback & Savepoint in Triggers, Because Triggers are Implicitly Invoked. Hence Transaction of Triggers are Committed Later.

> Sequence of Firing Database Triggers are,
a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger

52) Dependencies: A stored procedure could contain a Select statement that selects columns from a table. For this reason, the stored procedure is called a dependent object, whereas table is called a referenced object.

53) To Manage Dependencies all schema objects have a status that is recorded in the data dictionary:
a) Valid: Schema object has been compiled & can be used when referenced
b) Invalid: Schema object must be compiled before it can be used

54) It is strongly recommended that you recompile local dependent objects manually, rather than relying on an automatic mechanism.

55) Remote Dependencies: In this case, objects are on separate nodes i.e.Separate Schema
This are governed by the mode chosen by the user,

a) Timestamp: Each PL/SQL Program units carries a timestamp that is set when it is created or compiled. Whenever you alter a PL/SQL program unit or a relevant schema object , all of its dependent program units are marked as invalid & must be recompiled before they can execute

b) Signature: For each PL/SQL program unit, both the timestamp & the signature are recorded. The signature of PL/SQL construct contains following information
1) Name of the construct (Procedure, function or package)
2) Base types of parameters of constructs
3) Modes of the parameters (IN, OUT, INOUT)
4) Number of the parameters
So if a remote program is changed & recompiled but the Signature does not change, then the local procedure can execute the remote procedure. With the timestamp method, an error would have been raised because the timestamps would not have matched.

56) Pragma Autonomous_Transaction is the Compiler Directive that tells the Compiler to Execute Transaction independently in any other Transaction

57) Difference between PL/SQL Table & Nested Table: 
PL/SQL Table> Index by Tables are not Stored in Database
Nested Table
> Nested Tables are Stored in Database as Database Columns

58) Difference between Nested Table & Varray:
Nested Tablea) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

Varraya) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) This are Stored in Tablespaces

Bulk Collect In Oracle PLSQL

We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.

In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.

insert into BULK_COLLECT_TEST

select * from BULK_COLLECT_TEST;

20000 rows created.

--BLOCK1:Using Loops
declare
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
 begin
      open c1;
       loop
       fetch c1 into rec1;
    exit when c1%notfound;
    null;
    end loop;
 end;

total Elapsed Time is : 45 Secs

--BLOCK2: Using Bulk Collecting
declare
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
begin
open c1;
   fetch c1 bulk collect into rec1;
end;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

--Bulk Collect Example using LIMIT :
declare
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
begin
    open c1;
    loop
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    null;
    end loop;
    exit when c1%notfound;
    end loop;
end;

ERRBUF and RETCODE In Concurrent Program

For each Concurrent program we must use mandatory parameters : ERRBUF and RETCODE.

ERRBUF: It return the error message. 
For you program if you get any error in exception block you can assign the error message to this parameter. 
This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf.

RETCODE: This parameter returns the status of the concurrent program.
0- Success --Completed
1- Warning -- Yellow color
2- Error -- Red

These parameters we call as a first parameters for the program.

Ex:
Create procedure  CProgram(ERRBUF out varchar2, RETCODE  out varchar2, person_id in NUmber)
as
begin
<Declaration Goes  Here>
begin
<Code Block1 Here>

exception
when no_data_found then
retcode := 1;
errbuf:= 'RetCode : Warning';
end;
...
<Code Block2 Here>
...
retcode:= 0;
commit;
exception
when others then

retcode := 2;

errbuf:= 'errbuf:= 'RetCode : Error';

end;

PL/SQL Package

Summary: in this tutorial, you will learn how to create a simple PL/SQL package that is a group of related functions, procedures, types, etc.

Introducing to PL/SQL Package

PL/SQL package is a group of related functions, procedures, types, cursors, etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications.

A PL/SQL package has two parts: package specification and package body.

• A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications.
• A package body contains the code that implements the package specification.

PL/SQL Package

Creating PL/SQL Package Specification

The package specification is required when you create a new package. The package specification lists all the objects which are publicly accessible from other applications. The package specification also provides the information that developers need to know in order to use the interface. In short, package specification is the package’s API.

If the package specification does not contain any stored functions, procedures and no private code is needed, you don’t need to have a package body. These packages may contain only type definition and variables declaration. Those variables are known as package data. The scope of package data is global to applications. It is recommended that you should hide as much as package data as possible and use get and set functions to read and write that data. By doing this, you can prevent your package data changed unintentionally.

It is important to note that you must compile the package specification before package body.

Here is the syntax of creating PL/SQL package specification:

1 2 3 4 5 6 7 8 9 10 11

CREATE [OR REPLACE] PACKAGE package_name [ AUTHID { CURRENT_USER | DEFINER } ] { IS | AS }      [definitions of public TYPES    ,declarations of public variables, types, and objects    ,declarations of exceptions    ,pragmas    ,declarations of cursors, procedures, and functions    ,headers of procedures and functions] END [package_name];

The CREATE PACKAGE statement is used to define a new package specification. If you want to rebuild existing package you can use the REPLACE keyword instead of the CREATE keyword. In the package specification, you can define new types, declare global variables, types, objects, exceptions, cursors, procedures, and functions.

Below is an example of creating a new package specification called personnel. The personnelpackage contains two functions: get_fullname() and get_salary() based on employee’s ID.

1 2 3 4 5 6 7 8

CREATE OR REPLACE PACKAGE personnel AS   -- get employee's fullname   FUNCTION get_fullname(n_emp_id NUMBER)     RETURN VARCHAR2;   -- get employee's salary   FUNCTION get_salary(n_emp_id NUMBER)     RETURN NUMBER; END personnel;

Creating PL/SQL Package Body

PL/SQL package body contains all the code that implements stored functions, procedures, and cursors listed in the package specification.

The following illustrates the syntax of creating package body:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE [OR REPLACE] PACKAGE BODY package_name    { IS | AS }      [definitions of private TYPEs    ,declarations of private variables, types, and objects    ,full definitions of cursors    ,full definitions of procedures and functions] [BEGIN    sequence_of_statements   [EXCEPTION    exception_handlers ] ]   END [package_name];

The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification.

The package body can contain an optional package initialization section that appears at the end of the package body. The package initialization sections start with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package. The package initialization section is executed when the application references to the package element at the first time.

The following illustrates package body of the personnel package:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

/*   Package personnel body */ CREATE OR REPLACE PACKAGE BODY personnel AS   -- get employee's fullname   FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS       v_fullname VARCHAR2(46);   BEGIN     SELECT first_name || ',' ||  last_name     INTO v_fullname     FROM employees     WHERE employee_id = n_emp_id;       RETURN v_fullname;     EXCEPTION   WHEN NO_DATA_FOUND THEN     RETURN NULL;   WHEN TOO_MANY_ROWS THEN     RETURN NULL;   END; -- end get_fullname     -- get salary   FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS     n_salary NUMBER(8,2);   BEGIN     SELECT salary     INTO n_salary     FROM employees     WHERE employee_id = n_emp_id;       RETURN n_salary;       EXCEPTION       WHEN NO_DATA_FOUND THEN         RETURN NULL;       WHEN TOO_MANY_ROWS THEN         RETURN NULL;   END; END personnel;

Referencing PL/SQL package elements

You reference to package elements by using dot notation:

1	package_name.package_element

The following code illustrates how to use personnel package by calling its functions:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SET SERVEROUTPUT ON SIZE 1000000; DECLARE   n_salary NUMBER(8,2);   v_name   VARCHAR2(46);   n_emp_id NUMBER := &emp_id; BEGIN     v_name   := personnel.get_fullname(n_emp_id);   n_salary := personnel.get_salary(n_emp_id);     IF v_name  IS NOT NULL AND     n_salary IS NOT NULL   THEN     dbms_output.put_line('Employee: ' || v_name);     dbms_output.put_line('Salary:' || n_salary);   END IF; END;

In this tutorial, you’ve learned how to create a complete PL/SQL package and how to use its functions in another program.