To Retriew the records from Org dependent Views

To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus

begin
fnd_client_info.set_org_context(Org_id);
end;

R12 AR Fax Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_fax_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_fax_rec.phone_area_code := '104';
l_fax_rec.phone_number := '234-565';
l_fax_rec.phone_line_type := 'FAX';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_fax_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Email Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'EMAIL';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_email_rec.email_address := 'test@xxx.com';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Phone Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_phone_rec.phone_area_code := '703';
l_phone_rec.phone_number := '8441213';
l_phone_rec.phone_line_type := 'GEN';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Org Contact Creation

DECLARE
l_org_contact_id NUMBER;
l_party_rel_id_contactrel NUMBER;
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
---THIS IS THE SUBJECT ID (PERSON INFO)
l_org_contact_rec.party_rel_rec.subject_id := 308026;
l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
---THIS IS THE OBJECT ID (ORGANIZATION INFO)
l_org_contact_rec.party_rel_rec.object_id := 308013;
l_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
l_org_contact_rec.created_by_module := 'TCA_MIGRATION';
hz_party_contact_v2pub.create_org_contact
(p_init_msg_list => 'T',
p_org_contact_rec => l_org_contact_rec,
x_org_contact_id => l_org_contact_id,
x_party_rel_id => l_party_rel_id_contactrel,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_org_contact_id ' || l_org_contact_id);
DBMS_OUTPUT.put_line ( 'l_party_rel_id_contactrel '
|| l_party_rel_id_contactrel
);
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' || l_party_number);
END IF;
END;

R12 AR Person Creation

DECLARE
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_profile_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_create_person_rec hz_party_v2pub.person_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_create_person_rec.person_last_name := 'Rohith';
l_create_person_rec.person_first_name := 'Kannur';
l_create_person_rec.created_by_module := 'TCA_MIGRATION';
hz_party_v2pub.create_person
(p_init_msg_list => 'T',
p_person_rec => l_create_person_rec,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line ('l_msg_count ' || l_msg_count);
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);

FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' || l_party_number);
DBMS_OUTPUT.put_line ('l_profile_id ' || l_profile_id);
END IF;
END;

R12 AR Customer Account Site Creation

DECLARE
l_cust_acct_site_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
mo_global.init ('AR');
mo_global.set_policy_context ('S', '85');
l_cust_acct_site_rec.cust_account_id := 96682;
l_cust_acct_site_rec.party_site_id := 179420;
l_cust_acct_site_rec.created_by_module := 'TCA_MIGRATION';
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_cust_acct_site_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_cust_acct_site_id ' || l_cust_acct_site_id);
END IF;
END;

R12 AR customer Party Site Creation

DECLARE
l_party_site_id NUMBER;
l_party_site_number NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_party_site_rec.party_id := 308013;
l_party_site_rec.location_id := 25254;
l_party_site_rec.created_by_module := 'TCA_MIGRATION';
hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_site_id ' || l_party_site_id);
DBMS_OUTPUT.put_line ('l_party_site_number ' || l_party_site_number);
END IF;
END;

Triggers Concepts - 2

Row Level Trigger With Referencing Clause
CREATE TABLE person (
fname VARCHAR2(15),
lname VARCHAR2(15));

CREATE TABLE audit_log (
o_fname VARCHAR2(15),
o_lname VARCHAR2(15),
n_fname VARCHAR2(15),
n_lname VARCHAR2(15),
chng_by VARCHAR2(10),
chng_when DATE);

CREATE OR REPLACE TRIGGER referencing_clause
AFTER UPDATE
ON person
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
act VARCHAR2(1);
BEGIN
INSERT INTO audit_log
(o_fname, o_lname, n_fname, n_lname, chng_by, chng_when)
VALUES
(:OLD.fname, :OLD.lname, :NEW.fname, :NEW.lname, USER, SYSDATE);
END referencing_clause;
/

INSERT INTO person (fname, lname) VALUES ('Dan', 'Morgan');

SELECT * FROM person;
SELECT * FROM audit_log;

UPDATE person
SET lname = 'Cline';

SELECT * FROM person;
SELECT * FROM audit_log;

UPDATE person
SET fname = 'Mark', lname = 'Townsend';

SELECT * FROM person;
SELECT * FROM audit_log;

Follows Clause CREATE TABLE test (
testcol VARCHAR2(15));

INSERT INTO test VALUES ('dummy');

CREATE OR REPLACE TRIGGER follows_a
AFTER UPDATE
ON test
FOR EACH ROW
BEGIN
dbms_output.put_line('A');
END follows_a;
/

CREATE OR REPLACE TRIGGER follows_b
AFTER UPDATE
ON test
FOR EACH ROW
BEGIN
dbms_output.put_line('B');
END follows_b;
/

set serveroutput on

UPDATE test SET testcol = 'a';

CREATE OR REPLACE TRIGGER follows_b
AFTER UPDATE
ON test
FOR EACH ROW
FOLLOWS uwclass.follows_a
BEGIN
dbms_output.put_line('B');
END follows_b;
/

UPDATE test SET testcol = 'a';

Compound Triggers (new 11g)

Compound triggers allow for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER CREATE TRIGGER
FOR ON
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
...
END AFTER STATEMENT;

AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
END compound_trigger;
/
CREATE TABLE test AS
SELECT table_name, tablespace_name
FROM user_tables;

set serveroutput on

CREATE OR REPLACE TRIGGER compound_trig
FOR INSERT ON test
COMPOUND TRIGGER
-------------------------------
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('BEFORE STATEMENT LEVEL');
END BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line('BEFORE ROW LEVEL');
END BEFORE EACH ROW;
-------------------------------
AFTER STATEMENT IS
BEGIN
dbms_output.put_line('AFTER STATEMENT LEVEL');
END AFTER STATEMENT;
-------------------------------
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('AFTER ROW LEVEL');
END AFTER EACH ROW;
END compound_trig;
/

SELECT trigger_name, trigger_type
FROM user_triggers;

INSERT INTO test
(table_name, tablespace_name)
VALUES
('MORGAN', 'UWDATA');

Triggers concepts

Data Dictionary Views Related
To DDL Triggers trigger$

dba_triggers all_triggers user_triggers

System Privileges Related To Table Triggers create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger
Table Trigger Firing Options -- before constraints are applied
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE

-- after constraints are applied
AFTER INSERT
AFTER UPDATE
AFTER DELETE
Transaction Model Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
Maximum trigger size 32K - but you can call procedures and function in triggers to perform processing

Create Statement Level Triggers (the default)

Statement Level Trigger With A Single Action CREATE OR REPLACE TRIGGER
[]
[OR OR ]
ON
[FOLLOWS ]

DECLARE

BEGIN

EXCEPTION

END ;
/
CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol NUMBER(10),
datecol DATE);

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'XYZ';

Statement Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;

Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible.

Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE TRIGGER
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW

DECLARE

BEGIN

EXCEPTION

END ;
/
CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;

CREATE SEQUENCE seq_t;

CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW

BEGIN
SELECT seq_t.NEXTVAL
INTO:NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/

INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');

SELECT * FROM t;

Row Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END row_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');

SELECT * FROM orders;

UPDATE orders SET somecolumn = 'XYZ';

Row Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' On Insert');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' On Update');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' On Delete');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders
SET somecolumn = 'ZZT';

DELETE FROM orders WHERE rownum < 4;

Row Level Trigger With OF Clause
CREATE OR REPLACE TRIGGER of_clause
BEFORE UPDATE
OF numbercol
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(40) := 'Update Will Change numbercol Column';
BEGIN
dbms_output.put_line(vMsg);
END of_clause;
/

set serveroutput on

UPDATE orders
SET numbercol = 8;

Oracle Built-in Functions

Below is the useful link for Oracle Built in Functions
http://www.psoug.org/reference/builtin_functions.html

String Functions

ASCII
Get The ASCII Value Of A Character
ASCII(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;

CASE Related Functions
Upper Case UPPER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT UPPER('Dan Morgan') FROM dual;
Lower Case LOWER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT LOWER('Dan Morgan') FROM dual;
Initial Letter Upper Case INITCAP(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT INITCAP('DAN MORGAN') FROM dual;
NLS Upper Case NLS_UPPER()
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Lower Case NLS_LOWER()
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
NLS Initial Letter Upper Case NLS_INITCAP()
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;

CHR
Character CHR(n PLS_INTEGER) RETURN VARCHAR2;
SELECT(CHR(68) CHR(65) CHR(78)) FROM dual;

SELECT(CHR(68) CHR(97) CHR(110)) FROM dual;

COALESCE

Returns the first non-null occurrence COALESCE(, , , ...)
CREATE TABLE test (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT COALESCE(col1, col2, col3) FROM test;

CONCAT
Concatenate
Overload 1
standard.CONCAT(
lef VARCHAR2 CHARACTER SET ANY_CS,
right VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET;
SELECT CONCAT('Dan ', 'Morgan') FROM dual;

Concatenate

Overload 2 CONCAT(left IN CLOB, right IN CLOB) RETURN CLOB
set serveroutput on

DECLARE
c1 CLOB := TO_CLOB('Dan ');
c2 CLOB := TO_CLOB('Morgan');
c3 CLOB;
BEGIN
SELECT CONCAT('Dan ', 'Morgan')
INTO c3
FROM dual;

dbms_output.put_line(c3);
END;
/

CONVERT
Converts From One Character Set To Another CONVERT(,,
)
SELECT CONVERT('Ġʠ͠ՠؠA B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value DUMP( [,[,[,]]])

8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name

set linesize 121
col dmp format a50

SELECT table_name, DUMP(table_name) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;

INSTR
See the post in my Blog on this

INSTRB
Location of a string, within another string, in bytes INSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRB('Dan Morgan', ' ', 1, 1) FROM dual;

INSTRC
Location of a string, within another string, in Unicode complete characters INSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRC('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR2
Location of a string, within another string, in UCS2 code points INSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR2('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR4
Location of a string, within another string, in UCS4 code points INSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR4('Dan Morgan', ' ', 1, 1) FROM dual;

LENGTH
String Length LENGTH()
SELECT LENGTH('Dan Morgan') FROM dual;

LENGTHB
Returns length in bytes LENGTHB()
SELECT table_name, LENGTHB(table_name) FROM user_tables;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.

LPAD
Left Pad

Overload 1 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER,
PAD VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25) FROM dual;
Overload 3 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len NUMBER,
PAD CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD

LTRIM
Left Trim

Overload 1 LTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' LTRIM(' Dan Morgan ') '<-' FROM dual; Overload 2 LTRIM( STR1 VARCHAR2 CHARACTER SET ANY_CS := ' ') RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' LTRIM('xxx Dan Morgan ') '<-' FROM dual; SELECT '->' LTRIM('xxxDan Morgan ', 'x') '<-' FROM dual; MAX The Maximum String based on the current sort parameter MAX()
SELECT MAX(table_name)
FROM user_tables;

MIN
The Minimum String based on the current sort parameter MIN()
SELECT MIN(table_name)
FROM user_tables;

NLSSORT

Returns the string of bytes used to sort a string.

The string returned is of RAW data type NLSSORT(, 'NLS_SORT = );
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G⢥rd');
COMMIT;

SELECT * FROM test ORDER BY name;

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');

Quote Delimiters

q used to define a quote delimiter for PL/SQL q'';
set serveroutput on

DECLARE
s1 VARCHAR2(20);
s2 VARCHAR2(20);
s3 VARCHAR2(20);
BEGIN
s1 := q'[Isn't this cool]';
s2 := q'"Isn't this cool"';
s3 := q'Isn't this cool';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;
/

REVERSE
Reverse REVERSE()
SELECT REVERSE('Dan Morgan') FROM dual;

SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;

RPAD
Right Pad

Overload 1 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER,
pad VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25) '<-' FROM dual; RTRIM Right Trim Overload 1 RTRIM( str1 VARCHAR2 CHARACTER SET ANY_CS := ' ', tset VARCHAR2 CHARACTER SET STR1%CHARSET) RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' RTRIM(' Dan Morganxxx') '<-' FROM dual; SELECT '->' RTRIM(' Dan Morganxxx', 'xxx') '<-' FROM dual; Overload 2 RTRIM( str1 VARCHAR2 CHARACTER SET ANY_CS := ' ') RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' RTRIM(' Dan Morgan ') '<-' FROM dual; SOUNDEX Returns Character String Containing The Phonetic Representation Of Another String Rules: Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y Assign numbers to the remaining letters (after the first) as follows: b, f, p, v = 1 c, g, j, k, q, s, x, z = 2 d, t = 3 l = 4 m, n = 5 r = 6 If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first. Return the first four bytes padded with 0. SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET ch%CHARSET; CREATE TABLE test ( namecol VARCHAR2(15)); INSERT INTO test (namecol) VALUES ('Smith'); INSERT INTO test (namecol) VALUES ('Smyth'); INSERT INTO test (namecol) VALUES ('Smythe'); INSERT INTO test (namecol) VALUES ('Smither'); INSERT INTO test (namecol) VALUES ('Smidt'); INSERT INTO test (namecol) VALUES ('Smick'); INSERT INTO test (namecol) VALUES ('Smiff'); COMMIT; SELECT name, SOUNDEX(namecol) FROM test; SELECT * FROM test WHERE SOUNDEX(namecol) = SOUNDEX('SMITH'); SUBSTR See links at page bottom SUBSTRB Returns a substring counting bytes rather than characters SUBSTRB( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTRC( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTR2( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTR4( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
TREAT Changes The Declared Type Of An Expression TREAT ( AS REF schema.type))
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;

TRIM (variations are LTRIM and RTRIM)
Trim Spaces TRIM()
SELECT ' Dan Morgan ' FROM dual;

SELECT TRIM(' Dan Morgan ') FROM dual;
Trim Other Characters TRIM( FROM )
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
Trim By CHR value TRIM()
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;

Vertical Bars
Also known as Pipes
SELECT 'Dan' ' ' 'Morgan' FROM dual;

with alias

SELECT 'Dan' ' ' 'Morgan' NAME FROM dual;
or
SELECT 'Dan' ' ' 'Morgan' AS NAME FROM dual;

VSIZE
Byte Size VSIZE(e IN VARCHAR2) RETURN NUMBER
SELECT VSIZE('Dan Morgan') FROM dual;

SUBSTR And INSTR String Functions

SUBSTR (Substring) Built-in String Function

SUBSTR (overload 1)
SUBSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

SUBSTR (overload 2)
SUBSTR(
STR1 CLOB CHARACTER SET ANY_CS,
POS NUMBER, -- starting position
LEN NUMBER := 2147483647) -- number of characters
RETURN CLOB CHARACTER SET STR1%CHARSET;

Substring Beginning Of String
SELECT SUBSTR(, 1, )
FROM dual;
SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
FROM dual;

Substring Middle Of String
SELECT SUBSTR(, , )
FROM dual.
SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOUR
FROM dual;

Substring End of String
SELECT SUBSTR(, )
FROM dual;

SELECT SUBSTR('Take the first four characters', 16) SIXTEEN_TO_END
FROM dual;

SELECT SUBSTR('Take the first four characters', -4) FINAL_FOUR
FROM dual;

INSTR (Instring) Built-in String Function

INSTR (overload 1)
INSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;

INSTR (overload 2)
INSTR(
STR1 CLOB CHARACTER SET ANY_CS, -- test string
STR2 CLOB CHARACTER SET STR1%CHARSET, -- string to locate
POS INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN INTEGER;

Instring For Matching First Value Found
SELECT INSTR(, , ,
FROM dual;
SELECT INSTR('Take the first four characters', 'a', 1, 1) FOUND_1
FROM dual;

Instring If No Matching Second Value Found
SELECT INSTR('Take the first four characters', 'a', 1, 2) FOUND_2
FROM dual;

Instring For Multiple Characters
SELECT INSTR('Take the first four characters', 'four', 1, 1) MCHARS
FROM dual;

Reverse Direction Search
SELECT INSTR('Take the first four characters', 'a', -1, 1) REV_SRCH
FROM dual;

Reverse Direction Search Second Match
SELECT INSTR('Take the first four characters', 'a', -1, 2) REV_TWO
FROM dual;

String Parsing By Combining SUBSTR And INSTR Built-in String Functions

List parsing first value

Take up to the character before the first comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;

List parsing center value

Take the value between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;

List parsing last value

Take the value after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual;

GL Tables which stores parent and child segments information

FND_FLEX_VALUES and
FND_FLEX_VALUE_HIERARCHIES tables.

Below link gives the information on parent and child segments in General Ledger

http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/gl/acctgf06.htm

Pl-Sql: Null Values in Comparisons and Conditional Statements

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

Comparisons involving nulls always yield NULL

Applying the logical operator NOT to a null yields NULL

In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN -- yields NULL, not TRUE
dbms_output.put_line('x != y'); -- not executed
ELSIF x = y THEN -- also yields NULL
dbms_output.put_line('x = y');
ELSE
dbms_output.put_line('Can''t tell if x and y are equal or not...');
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN -- yields NULL, not TRUE
dbms_output.put_line('a = b'); -- not executed
ELSIF a != b THEN -- yields NULL, not TRUE
dbms_output.put_line('a != b'); -- not executed
ELSE
dbms_output.put_line('Can''t tell if two NULLs are equal');
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.


NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR('');
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != '');
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

'apple' || NULL || NULL || 'sauce'

returns the following value:

'applesauce'

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to DECODE. In this case, manager_id is null
-- and the DECODE function returns 'nobody'.
SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by ' || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to NVL. In this case, manager_id is null
-- and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by employee #' || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := 'Apples and oranges';
my_string string_type%TYPE := 'more apples';
-- NULL is a valid argument to REPLACE, but does not match
-- anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line('Old string = ' || old_string);
dbms_output.put_line('New string = ' || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := 'Gold-i-locks';
-- When the substitution text for REPLACE is NULL,
-- the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, '-', NULL);
BEGIN
dbms_output.put_line('Dashed name = ' || dashed);
dbms_output.put_line('Dashes removed = ' || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.

PL/SQL -Advantages

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.

What Is Dynamic SQL?

What Is Dynamic SQL?
Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

How to Fetch Data Using Cursors in Packages and Procedures

Fetching Data

The following package defines a PL/SQL cursor variable type Emp_val_cv_type, and two procedures. The first procedure, Open_emp_cv, opens the cursor variable using a bind variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches rows from the Emp_tab table using the cursor variable.

CREATE OR REPLACE PACKAGE Emp_data AS
TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type,
Dept_number IN INTEGER);
PROCEDURE Fetch_emp_data (emp_cv IN Emp_val_cv_type,
emp_row OUT Emp_tab%ROWTYPE);
END Emp_data;

CREATE OR REPLACE PACKAGE BODY Emp_data AS
PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type,
Dept_number IN INTEGER) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number;
END open_emp_cv;
PROCEDURE Fetch_emp_data (Emp_cv IN Emp_val_cv_type,
Emp_row OUT Emp_tab%ROWTYPE) IS
BEGIN
FETCH Emp_cv INTO Emp_row;
END Fetch_emp_data;
END Emp_data;


The following example shows how to call the Emp_data package procedures from a PL/SQL block:

DECLARE
-- declare a cursor variable
Emp_curs Emp_data.Emp_val_cv_type;
Dept_number Dept_tab.Deptno%TYPE;
Emp_row Emp_tab%ROWTYPE;

BEGIN
Dept_number := 20;
-- open the cursor using a variable
Emp_data.Open_emp_cv(Emp_curs, Dept_number);
-- fetch the data and display it
LOOP
Emp_data.Fetch_emp_data(Emp_curs, Emp_row);
EXIT WHEN Emp_curs%NOTFOUND;
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ');
DBMS_OUTPUT.PUT_LINE(Emp_row.Sal);
END LOOP;
END;

Implementing Variant Records

The power of cursor variables comes from their ability to point to different cursors. In the following package example, a discriminant is used to open a cursor variable to point to one of two different cursors:

CREATE OR REPLACE PACKAGE Emp_dept_data AS
TYPE Cv_type IS REF CURSOR;
PROCEDURE Open_cv (Cv IN OUT cv_type,
Discrim IN POSITIVE);
END Emp_dept_data;

CREATE OR REPLACE PACKAGE BODY Emp_dept_data AS
PROCEDURE Open_cv (Cv IN OUT cv_type,
Discrim IN POSITIVE) IS
BEGIN
IF Discrim = 1 THEN
OPEN Cv FOR SELECT * FROM Emp_tab WHERE Sal > 2000;
ELSIF Discrim = 2 THEN
OPEN Cv FOR SELECT * FROM Dept_tab;
END IF;
END Open_cv;
END Emp_dept_data;


You can call the Open_cv procedure to open the cursor variable and point it to either a query on the Emp_tab table or the Dept_tab table. The following PL/SQL block shows how to fetch using the cursor variable, and then use the ROWTYPE_MISMATCH predefined exception to handle either fetch:

DECLARE
Emp_rec Emp_tab%ROWTYPE;
Dept_rec Dept_tab%ROWTYPE;
Cv Emp_dept_data.CV_TYPE;

BEGIN
Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch
Fetch cv INTO Dept_rec; -- but fetch into Dept_tab record
-- which raises ROWTYPE_MISMATCH
DBMS_OUTPUT.PUT(Dept_rec.Deptno);
DBMS_OUTPUT.PUT_LINE(' ' || Dept_rec.Loc);

EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
BEGIN
DBMS_OUTPUT.PUT_LINE
('Row type mismatch, fetching Emp_tab data...');
FETCH Cv INTO Emp_rec;
DBMS_OUTPUT.PUT(Emp_rec.Deptno);
DBMS_OUTPUT.PUT_LINE(' ' || Emp_rec.Ename);
END;

PL/SQL Packages

PL/SQL Packages
A package is an encapsulated collection of related program objects (for example, procedures, functions, variables, constants, cursors, and exceptions) stored together in the database.

Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over standalone procedures and functions. For example, they:

Let you organize your application development more efficiently.
Let you grant privileges more efficiently.
Let you modify package objects without recompiling dependent schema objects.
Enable Oracle Database to read multiple package objects into memory at once.
Can contain global variables and cursors that are available to all procedures and functions in the package.
Let you overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example of a PL/SQL Package Specification and Body
The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:

CREATE PACKAGE BODY Employee_management AS
FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2,
Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
Deptno NUMBER) RETURN NUMBER IS
New_empno NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the call to this function.

BEGIN
SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual;
INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr,
Hiredate, Sal, Comm, Deptno);
RETURN (New_empno);
END Hire_emp;

PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update
-- salary with increase.

BEGIN
UPDATE Emp_tab
SET Sal = Sal + Sal_incr
WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END Sal_raise;
END Employee_management;


--------------------------------------------------------------------------------
Note:
If you want to try this example, then first create the sequence number Emp_sequence. Do this with the following SQL*Plus statement:

SQL> CREATE SEQUENCE Emp_sequence
> START WITH 8000 INCREMENT BY 10;


Creating Packages
Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public procedures and functions declared in the package specification.

You can also define private, or local, package procedures, functions, and variables in a package body. These objects can only be accessed by other procedures and functions in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements would then be the following:

CREATE OR REPLACE PACKAGE Package_name AS ...


and

CREATE OR REPLACE PACKAGE BODY Package_name AS ...


Creating Packaged Objects
The body of a package can contain include:

Procedures and functions declared in the package specification.
Definitions of cursors declared in the package specification.
Local procedures and functions, not declared in the package specification.
Local variables.
Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, or used, by external users that have EXECUTE permission for the package or that have EXECUTE ANY PROCEDURE privileges.

When you create the package body, make sure that each procedure that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.

Privileges to Create or Drop Packages
The privileges required to create or drop a package specification or package body are the same as those required to create or drop a standalone procedure or function.

Procedures %TYPE and %ROWTYPE Attributes Usage

Procedures %TYPE and %ROWTYPE Attributes Usage

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)


This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

Dept_number number(2);
...
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);


Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:


--------------------------------------------------------------------------------
Caution:
To execute the following, use CREATE OR REPLACE PROCEDURE...

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


PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab
WHERE Empno = Emp_number;
END;


You could call this procedure from a PL/SQL block as follows:

DECLARE
Emp_row Emp_tab%ROWTYPE; -- declare a record matching a
-- row in the Emp_tab table
BEGIN
Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno);
DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr);
DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal);
DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno);
DBMS_OUTPUT.NEW_LINE;
END;


Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
RETURN Emp_tab%ROWTYPE IS ...