DECLARE
v_user_name VARCHAR2 (100) := upper(‘&Enter_User_Name’);
v_description VARCHAR2 (100) := ‘New User’;
v_app_short_name VARCHAR2 (100);
v_resp_key VARCHAR2 (100);
CURSOR user_cur IS
select a.application_short_name, r.responsibility_key
from fnd_responsibility_vl r, fnd_application_vl a where
r.application_id =a.application_id
and R.responsibility_ID IN (SELECT RESPONSIBILITY_ID FROM fnd_user_resp_groups WHERE USER_ID=&from_userid AND END_dATE IS NULL);
user_rec user_cur%ROWTYPE;
BEGIN
fnd_user_pkg.createuser
(x_user_name => v_user_name,
x_owner => NULL,
x_unencrypted_password => ‘&input_password’,
x_session_number => 0,
x_start_date => SYSDATE,
x_end_date => NULL,
x_last_logon_date => NULL,
x_description => v_description,
x_password_date => NULL,
x_password_accesses_left => NULL,
x_password_lifespan_accesses => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL,
x_fax => NULL,
x_customer_id => NULL,
x_supplier_id => NULL,
x_user_guid => NULL,
x_change_source => NULL
);
COMMIT;

OPEN user_cur;
LOOP
FETCH user_cur INTO user_rec;
EXIT WHEN user_cur%NOTFOUND;
fnd_user_pkg.addresp(username => v_user_name
,resp_app => user_rec.application_short_name
,resp_key => user_rec.responsibility_key
,security_group => ‘STANDARD’
,description => NULL
,start_date => SYSDATE
,end_date => null);
END LOOP;
CLOSE user_cur;
commit;
END;

Input variables required :

&Enter_User_Name   > New username to create

&from_userid               > userid from where all responsibilities needs to be copied

&input_password       > password for the new user

As the name implies a constant is a value used in a PL/SQL Block that
remains unchanged throughout the program. A constant is a user-defined
literal value. You can declare a constant and use it instead of actual
value.

For example: If you want to write a program which will increase the
salary of the employees by 25%, you can declare a constant and use it
throughout the program. Next time when you want to increase the salary
again you can change the value of the constant which will be easier
than changing the actual value throughout the program.
 
The General Syntax to declare a constant is:

constant_name CONSTANT datatype := VALUE; 

• constant_name is the name of the
  constant i.e. similar to a variable name.
• The word CONSTANT is a reserved word
  and ensures that the value does not change.
• VALUE - It is a value which must be
  assigned to a constant when it is declared. You cannot assign a value
  later.

For example, to declare salary_increase, you can write code as
follows:

DECLARE 

salary_increase CONSTANT number (3) := 10; 

You must assign a value to a constant
at the time you declare it. If you do not assign a value to a constant
while declaring it and try to assign a value in the execution section,
you will get a error. If you execute the below Pl/SQL block you will
get error.

DECLARE 

 salary_increase CONSTANT number(3); 

BEGIN 

 salary_increase := 100; 

 dbms_output.put_line (salary_increase); 

END; 

PL/SQL stands for Procedural Language extension of SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

About This PL SQL Programming Tutorial

This Oracle PL SQL tutorial teaches you the basics of programming in PL/SQL with appropriate examples. You can use this tutorial as your guide or reference while programming with PL SQL. I will be making this Oracle PL SQL programming tutorial as often as possible to share my knowledge in PL SQL and help you in learning PL SQL better.

Even though the programming concepts discussed in this tutorial is specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in syntax. This tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.

A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:

The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;


These are the advantages of PL/SQL

• Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.

• Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).

• Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.

•Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

SQL> alter tablespace test01 add datafile ‘DATA1′ size 8G; 

Tablespace altered. 

SQL> select file_id,file_name from dba_data_files where tablespace_name=’test01′; 

FILE# NAME 
———- ————————————————– 
11 +DATA1/askdbaonline/datafile/test01.276.662164269 
17 +DATA1/askdbaonline/datafile/test01.283.660433979 
82 /opt/oracle/product/10.2.0/db/dbs/DG1 

2) We took following step to make it correct. 

SQL> alter database datafile 82 offline; 

rman target /

RMAN> copy datafile 82 to ‘+DATA1/’; 

RMAN> switch datafile 82 to copy;

SQL> alter database datafile 82 online; 

alter database datafile 82 online 
* 
ERROR at line 1: 
ORA-01113: file 82 needs media recovery 
ORA-01110: data file 82: ‘+DATA1/askdbaonline/datafile/test01.372.779132951′ 

SQL> recover datafile 82; 

Media recovery complete. 

SQL> alter database datafile 82 online; 

Database altered. 

SQL> select * from v$recover_file; 

no rows selected 

SQL> select file_id,file_name from dba_data_files where tablespace_name=’test01′; 

FILE# NAME 
———- ————————————————– 
11 +DATA1/askdbaonline/datafile/test01.276.662164269 
17 +DATA1/askdbaonline/datafile/test01.283.660433979 
82 +DATA1/askdbaonline/datafile/test01.372.779132951

CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE BACKUP OPTIMIZATION CLEAR;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR;
CONFIGURE DEVICE TYPE DISK CLEAR;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT  CLEAR;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL DEVICE TYPE SBT CLEAR;
CONFIGURE MAXSETSIZE CLEAR;
CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;

Oracle Database

Oracle Database documentation, 11g Release 2 (11.2).
Installation manuals and platform-specific books are included in this search.

Oracle Database documentation, 11g Release 1 (11.1).
Installation manuals and platform-specific books are included in this search.

Oracle Database documentation, 10g Release 2 (10.2).
Installation manuals and platform-specific books are included in this search.

Oracle Database Express Edition documentation, 10g Release 2 (10.2).
This is a small library specifically for the free Express Edition product. It covers administering the database through Application Express, and developing modern web applications in PHP, .NET, Java, and PL/SQL. Installation covers Windows and Linux only. Books such as the SQL Reference are not included.

Oracle Database documentation, 10g Release 1 (10.1).
Installation manuals and platform-specific books are included in this search.

Oracle9i documentation, Release 2 (9.2).
Oracle9i documentation, Release 1 (9.0.1).
Installation manuals and platform-specific books are included in this search, for 9.2 only.

Oracle8i documentation, Release 8.1.7.
Installation manuals and platform-specific books are not included in this search.

Oracle Application Server and Fusion Middleware

Oracle Fusion Middleware documentation 11g (Release 11.1.1).
Installation manuals and platform-specific books are included in this search.

Oracle Identity Management documentation 10g (Release 10.1.4).
Installation manuals and platform-specific books are not yet included in this search.

Oracle Application Server documentation 10g (Release 10.1.3).
Installation manuals and platform-specific books are not yet included in this search.

Oracle Application Server documentation 10g (Release 10.1.2).
Installation manuals and platform-specific books are not yet included in this search.

Oracle Application Server documentation 10g (Release 9.0.4).
Installation manuals and platform-specific books are not included in this search.

Oracle9iAS documentation (Release 9.0.2).
Installation manuals and platform-specific books are not included in this search.