Pin objects in Oracle shared pool using DBMS_SHARED_POOL package.

DBMS_SHARED_POOL package can be used to pin PL/SQL objects, SQL cursors, triggers, sequence inside shared pool area in Oracle database memory.

Oracle has shared pool inside System Global Area(SGA) where it caches compiled PL/SQL and SQL code to improve response and reduce execution time when the same code is executed again. Whenever we execute a SQL or PL/SQL code then Oracle checks for  compiled version of the code in the shared pool area. If it finds the compiled version then the code is executed from the shared pool area, this is known as soft parse, or else, code is fetched from disk, compiled, executed and stored in the shared pool area for future reference.

Shared pool area has a memory limit, so Oracle keeps moving out objects as new objects come in, i.e. aging process is followed to move older objects out of the memory to make space for new objects. Sometimes, for improving performance, there is a need to keep some objects in the shared pool area and skip the usual aging process irrespective of the new objects coming in, so we pin those objects permanently inside the shared pool area. These objects will then not be removed by usual aging process unless we explicitly ask Oracle to unpin them.

Let's see how to use this package.

This package consists of some procedures which can be called to pin/unpin the objects.

PROCEDURE DBMS_SHARED_POOL.KEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

KEEP procedure can be used to pin object inside shared pool area.
name: Name of the object to be pinned.
flag: Type of the object to be pinned.

Value of flag can be
P : Procedure/Function/Package
T : Type
Q : Sequence
C : Cursor
R : Trigger

PROCEDURE DBMS_SHARED_POOL.UNKEEP
(name IN VARCHAR2
,flag IN CHAR DEFAULT 'P');

UNKEEP procedure can be used to unpin object from shared pool area. Object will follow normal aging process once unpinned.
name: Name of the object to be unpinned.
flag: Type of the object to be unpinned.

PROCEDURE DBMS_SHARED_POOL.SIZES
(minsize IN NUMBER);

SIZES procedure can be used to get information about all objects which are currently in shared pool area and exceed the size specified in parameter of the procedure.

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD
(threshold_size IN NUMBER);

ABORTED_REQUEST_THRESHOLD procedure can be used to avoid Oracle flushing old objects in order to make space for a big new object. When threshold is set then any object greater than threshold size will not be allowed in memory if sufficient memory space is not available in shared pool.

NOTE: DBMS_SHARED_POOL is not accessible by a normal user, also there is no public synonym referencing the package. This package is mostly used by the Oracle DBAs.

For more info visit

Execute dynamic queries using Native Dynamic SQL(NDS) in PL/SQL.

Dynamic SQL statements are a powerful way to execute dynamic queries in PL/SQL. In this approach, queries are designed as the program proceeds or when the conditions arise. There are two tools in PL/SQL to design and execute dynamic queries.

1) Native Dynamic SQL (NDS)

2) DBMS_SQL package

NDS is relatively easy to use when compared to DBMS_SQL package. DBMS_SQL has a complex structure for making dynamic queries.

Using NDS, inputs can be passed to a query statement and outputs can be collected.

While writing dynamic queries, we can follow two approaches. Parts and inputs of a dynamic query can be attached to each other dynamically using string concatenation and then the dynamic query can be executed, or , placeholders can be used to pass parameters/inputs to a dynamic query. Using placeholder technique is a safer approach as it prevents SQL injection attacks.

Now let's see how to use NDS

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2)
 IS 
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; 
query_text := 'UPDATE employee_data SET salary=1.1*salary WHERE id = :1 RETURNING name INTO :2'; 
EXECUTE IMMEDIATE query_text USING user_id RETURNING INTO p_name;
COMMIT; 
END;

Create/replace the above procedure and call it. This dynamic query executes the update statement by taking id as input and returns the name of the employee who has got the hike.

Let's see one more example with select statement.

CREATE OR REPLACE PROCEDURE proc(p_id IN number, p_name OUT varchar2) 
IS
user_id number(6); 
query_text varchar2(500); 
BEGIN user_id := p_id; query_text := 'SELECT name FROM employee_data WHERE id = :1'; 
EXECUTE IMMEDIATE query_text INTO p_name USING user_id;
END;

This select statement saves the name of the employee in p_name variable for a given id.

For more info visit

Records in PL/SQL.

For more info visit

A record, as the name signifies, is a collection of more than one fields in a variable. For those of you who are familiar with language "C", you can compare a record to a structure in "C". The fields of a record can be of sql  or pl/sql type.

There are two ways in which a record can be declared.

1) Using Anchor Declarations(Table Based or Cursor Based Records).

ex:   employee_details employee%ROWTYPE;

Here employee is a table . Suppose, employee table has structure as shown below.

 EMP_ID                                             NOT NULL                 NUMBER
 EMP_NAME                                    NOT NULL                 VARCHAR2(10)

 We can collect the values corresponding to some row of a table in the record as

SELECT * into employee_details FROM employee WHERE emp_id  = 1;

and after that, we can access or manipulate the fields in the record using ".".

employee_details.emp_name

In the same way, we can also declare a record of Cursor type

CURSOR employee_cursor is SELECT * into employee_details FROM employee WHERE emp_id  = 1;

emp_cur employee_cursor%ROWTYPE;

We can directly fetch values from cursor into our record as

OPEN employee_cursor;

FETCH employee_cursor INTO emp_cur;

2) Programmer Defined Records:

In a programmer defined records, we can decide the field of our records. It doesn't have to be dependent on any table or cursor for its structure. These type of records are declared as

TYPE emp_rec IS RECORD

( name employee.emp_name%TYPE,

age NUMBER(3),

salary NUMBER(10) := 0

);

employee_record emp_rec;

In this type of record, we are free to define our own fields with different datatypes. We can always specify [NOT NULL]:=[DEFAULT] with the fields of a record. These types of records are useful when we want to collect data from different tables or when our record has nothing to do with tables or cursors.

We can manipulate the fields of these type of records in a number of ways, for example

employee_record.name := 'John';

select name into employee_record.name from employee where emp_id = 1;

 Note:

1) Two records cannot be compared as rec1=rec2, even if they are of same record type. Comparision of two records can only be done on field basis.

2) Values of a record can be assigned to other record if they are of same record type.

rec1 := rec2;

3) Records can be passed as parameters or can be returned from a function.

4) IS NULL cannot be used to check if all the fields of a record are NULL.

5) NULL can be assigned to a record as

rec1 := NULL;

6) We can insert the whole record into a table(without using individual fields), only if the record is declared using anchored declaration.

emp_rec employee%ROWTYPE;

insert into employee values emp_rec;

Your first simple Pl/SQL program...

How to write your first PL/SQL program. Writing a PL/SQL program is not difficult. This post deals with writing a simple anonymous PL/SQL block. Let me remind you, anonymous block is a block without any name, i.e. you cannot call an anonymous by name. You can only execute an anonymous block while writing it, it cannot be stored and executed later.

The program written below assigns a string to a variable and prints it.

Before writing your PL/SQL block set your serveroutput on in Sql plus. We set serveroutput on, so that we could see output on sql plus.

SET SERVEROUTPUT ON

Now we will write the block/program.


DECLARE
v_name varchar2(20);
BEGIN
v_name:='John';
DBMS_OUTPUT.PUT_LINE('My name is '||v_name);
END;
/


Above block is divided into 3 sections, i.e.

DECLARE: We have declared a variable "v_name" in this section.


BEGIN: In this section we assign a value to v_name and later print it using PUT_LINE procedure of DBMS_OUTPUT package. "||" is the concatenation operator as we have "+'' in java or "." in PHP. Also notice the line of code after BEGIN, we use ":=" for assignment in oracle instead of "=".


END: END is used to end the block and after that "/" is used to execute the block.

If everything goes right, you will see the output "John" on Sql plus.