Oracle PL/SQL FAQ

Difference between CHAR and VARCHAR2

The difference between CHAR and VARCHAR2 is CHAR(n) will always be n bytes long, it wilbe blank padded upon insert.But VARCHAR2(N) will be 1 to n bytes long but will not be blank padded.

Using a CHAR on a varying field would be a pain due to the search semantics on CHAR.

PLSQL Code for sending an e-mail

This bit of code is a quicky grasp when there is a a need that you need to send an e-mail from an Oracle Apps Alert, Oracle Concurrent program to an user, Administrator, Supplier etc.,


CREATE OR REPLACE PROCEDURE send_mail
( sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(300) := 'your-mail-host-server';
mail_conn utl_smtp.connection;
mesg VARCHAR2( 1000 );
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg:= 'Test Message';
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
END;


NO COPY Hint Usage

Prior to Oracle8i release, the IN/OUT parameters in PL/SQL routines were passed using copy-in and copy-out semantics.
Oracle will copy the value being passed to parameters in seperate buffer cache and on completion of the routine it copies back to the variables in the calling program resulting in multiple buffers being opened in memory and the overhead of copying data back and forth.
The IN parameter is passed by reference i.e a pointer to the actual parameter is passed to the corresponding formal parameter and both of them point to the same location and there is no extra memory buffers and also no copy of values back and forth.

From Oracle 8i onwards the NOCOPY parameter HINT had been introduced for OUT and INOUT parameters by which Oracle makes the call by reference approach for both OUT and INOUT parameters.

NOCOPY is the ideal hint for OUT and INOUT parameters when the original value is not to be preserved.

Drawbacks:
-----------
NOCOPY is an HINT and Oracle does not guarantee passing a parameter as reference in the following situations:
1. When the call is a remote procedure call.
2. When the actual parameter being passed is an expression.
3. When there is an implicit conversion involved.


DB Link to a Remote Database

Whenever we have a need to access tables,views etc from a remote database in a Local database we can create a DB link and access the tables and views.

Syntax:
CREATE DATABASE LINK
CONNECT TO "DBId"
IDENTIFIED BY "Pwd"
USING 'Sch_ma_name';
We will be able to INSERT/UPDATE/DELETE data of the remote database from the local database.
And the rest of the operations depend on the avilability of the access to the current user.
We can create PUBLIC/SHARED DB Links.

Autonomous Transactions

In PL/SQL transaction processing takes place serially i.e a new transaction can begin only when an earlier transaction ends, in which case the user issues an explicit COMMIT/ROLLBACK.

But often in our applications we may require to commit or rollback changes without effecting the main transaction in the session.
From PL/SQL 8.1 onwards it is possible to achieve the goal of having to rollback some changes in the main transaction while committing the log entry( the transaction history) by issuing the PL/SQL compiler directive AUTONOMOUS_TRANSACTION.

So inorder to use Autonomous transactions we simple need to use the following directive in the delaration section of the procedure:PRAGMA AUTONOMOUS_TRANSACTION

Create or Replace procedure kiran_p(text VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into kiran(text) values(text);
COMMIT;
END;

The block in this example becomes an independent transaction and the COMMIT issued in this block will not affect the SQL statements in the main transaction.
Rules of Autonomous transactions:
----------------------------------
1. Nested blocks cannot be declared as autonomous transactions.
2. Autonomous transaction must have COMMIT or ROLLBACK before it completes its
execution, otherwise it returns the following error:
ORA-06519: active autonomous transaction detected and rolled back.
3. As the Autonomous transaction is an independent transaction you cannot Rollback to the
savepoint defined in the main transaction.
4. Again calling one autonomous transaction from the main transaction in your session implies
the session is concurrently running two sessions and so on.And the number of those sessions
possible were determined by the init.ora parameter LIST.
5. You cannot declare package as an autonomous transaction to make all procedures/functions
in the package as autonomous transactions.
6. If an autonomous transaction tries to access the resource held by the main transaction that is
suspended it will result in a deadlock.


Print This Post