Writing PL/SQL CURSOR
To write a Cursor to find employee with given job and deptno.
INPUT
DECLARE
cursor c1(j varchar2, dn number) is select empno, ename from emp where job=j and deptno=dn;
row1 emp%rowtype;
jb emp.job%type;
d emp.deptno%type;
BEGIN
jb:='&jb';
d:=&d;
open c1(jb,d);
fetch c1 into row1.empno,row1.ename;
if c1%notfound then
dbms_RESULT.put_line('Employee does not exist');
else
dbms_RESULT.put_line('empno is:'||row1.empno||' ' ||'employee name is:'||row1.ename);
end if;
END;
RESULT:
SQL> @CUR
Enter value for jb: MANAGER
old 7: jb:='&jb';
new 7: jb:='MANAGER';
Enter value for d: 20
old 8: d:=&d;
new 8: d:=20;
empno is:7566 employee name is:JONES
PL/SQL procedure successfully completed.
SQL> /
Enter value for jb: CLERK
old 7: jb:='&jb';
new 7: jb:='CLERK';
Enter value for d: 40
old 8: d:=&d;
new 8: d:=40;
Employee does not exist
PL/SQL procedure successfully completed.
Writing PL/SQL BLOCK using string functions.
To write a PL/SQL block to apply String Functions on a given input String.
INPUT
DECLARE
a varchar2(20);
l number(5);
BEGIN
a:='&a';
l:=length(a);
dbms_RESULT.put_line('Using Lower Function:' || lower(a));
dbms_RESULT.put_line('Using UPPER Function:' || upper(a));
dbms_RESULT.put_line('Using Initcap Function:' || initcap(a));
dbms_RESULT.put_line('Using Substring Function:' || substr(a,l,1));
dbms_RESULT.put_line('Using Substring Function:' || substr(a,1,3));
dbms_RESULT.put_line('Using Ltrim function for xxxabcxxxx:' || ltrim('xxxabcxxxx','x'));
dbms_RESULT.put_line('Using Rtrim function for xxxabcxxxx:'|| rtrim('xxxabcxxxx','x'));
dbms_RESULT.put_line('Using Lpad function :'|| lpad(a,l+4,'*'));
dbms_RESULT.put_line('Using Rpad function :'|| rpad(a,l+4,'*'));
END;
RESULT:
SQL>@STR
Enter value for a: santosh reddy
old 5: a:='&a';
new 5: a:='santosh reddy';
Using Lower Function:santosh reddy
Using UPPER Function:SANTOSH REDDY
Using Initcap Function:Santosh Reddy
Using Substring Function:y
Using Substring Function:san
Using Ltrim function for xxxabcxxxx:abcxxxx
Using Rtrim function for xxxabcxxxx:xxxabc
Using Lpad function :****santosh reddy
Using Rpad function :santosh reddy****
PL/SQL procedure successfully completed.
SQL> /
Enter value for a: UMA SHANKAR
old 5: a:='&a';
new 5: a:='UMA SHANKAR';
Using Lower Function:uma shankar
Using UPPER Function:UMA SHANKAR
Using Initcap Function:Uma Shankar
Using Substring Function:R
Using Substring Function:UMA
Using Ltrim function for xxxabcxxxx:abcxxxx
Using Rtrim function for xxxabcxxxx:xxxabc
Using Lpad function :****UMA SHANKAR
Using Rpad function :UMA SHANKAR****
PL/SQL procedure successfully completed
Writing PL/SQL triggers
To write a TRIGGER to ensure that DEPT TABLE does not contain duplicate of null values in DEPTNO column.
INPUT
CREATE OR RELPLACE TRIGGER trig1 before insert on dept for each row
DECLARE
a number;
BEGIN
if(:new.deptno is Null) then
raise_application_error(-20001,'error::deptno cannot be null');
else
select count(*) into a from dept where deptno=:new.deptno;
if(a=1) then
raise_application_error(-20002,'error:: cannot have duplicate deptno');
end if;
end if;
END;
RESULT:
SQL> @trigger
Trigger created.
SQL> select * from dept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(&deptnp,'&dname','&loc');
Enter value for deptnp: null
Enter value for dname: marketing
Enter value for loc: hyd
old 1: insert into dept values(&deptnp,'&dname','&loc')
new 1: insert into dept values(null,'marketing','hyd')
insert into dept values(null,'marketing','hyd')
*
ERROR at line 1:
ORA-20001: error::deptno cannot be null
ORA-06512: at "SCOTT.TRIG1", line 5
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
SQL> /
Enter value for deptnp: 10
Enter value for dname: manager
Enter value for loc: hyd
old 1: insert into dept values(&deptnp,'&dname','&loc')
new 1: insert into dept values(10,'manager','hyd')
insert into dept values(10,'manager','hyd')
*
ERROR at line 1:
ORA-20002: error:: cannot have duplicate deptno
ORA-06512: at "SCOTT.TRIG1", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
SQL> /
Enter value for deptnp: 50
Enter value for dname: MARKETING
Enter value for loc: HYDERABAD
old 1: insert into dept values(&deptnp,'&dname','&loc')
new 1: insert into dept values(50,'MARKETING','HYDERABAD')
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING HYDE
Locking Table.
AIM: To learn commands related to Table Locking
LOCK TABLE Statement Manually lock one or more tables.
Syntax:
Syntax:
LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT]
LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT]
Options:
PARTITION (partition)
SUBPARTITION (subpartition)
@dblink
lockmodes:
EXCLUSIVE
SHARE
ROW EXCLUSIVE
SHARE ROW EXCLUSIVE
ROW SHARE* | SHARE UPDATE*
If NOWAIT is omitted Oracle will wait until the table is available.
Several tables can be locked with a single command - separate with commas
e.g. LOCK TABLE table1,table2,table3 IN ROW EXCLUSIVE MODE;
Default Locking Behaviour :
A pure SELECT will not lock any rows.
INSERT, UPDATE or DELETE's - will place a ROW EXCLUSIVE lock.
SELECT...FROM...FOR UPDATE NOWAIT - will place a ROW EXCLUSIVE lock.
Multiple Locks on the same rows with LOCK TABLE
Even when a row is locked you can always perform a SELECT (because SELECT does not lock any rows) in addition to this, each type of lock will allow additional locks to be granted as follows.
ROW SHARE = Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks to be granted to the locked rows.
ROW EXCLUSIVE = Allow ROW EXCLUSIVE or ROW SHARE locks to be granted to the locked rows.
SHARE ROW EXCLUSIVE = Allow ROW SHARE locks to be granted to the locked rows.
SHARE = Allow ROW SHARE or SHARE locks to be granted to the locked rows.
EXCLUSIVE = Allow SELECT queries only
Although it is valid to place more than one lock on a row, UPDATES and DELETE's may still cause a wait if a conflicting row lock is held by another transaction.
Default Locking Behaviour :
A pure SELECT will not lock any rows.
INSERT, UPDATE or DELETE's - will place a ROW EXCLUSIVE lock.
SELECT...FROM...FOR UPDATE NOWAIT - will place a ROW EXCLUSIVE lock.
Multiple Locks on the same rows with LOCK TABLE
Even when a row is locked you can always perform a SELECT (because SELECT does not lock any rows) in addition to this, each type of lock will allow additional locks to be granted as follows.
ROW SHARE = Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks to be granted to the locked rows.
ROW EXCLUSIVE = Allow ROW EXCLUSIVE or ROW SHARE locks to be granted to the locked rows.
SHARE ROW EXCLUSIVE = Allow ROW SHARE locks to be granted to the locked rows.
SHARE = Allow ROW SHARE or SHARE locks to be granted to the locked rows.
EXCLUSIVE = Allow SELECT queries only
Although it is valid to place more than one lock on a row, UPDATES and DELETE's may still cause a wait if a conflicting row lock is held by another transaction.
Generation of Forms using ORACLE FORM BUILDER
To design a form using Oracle Developer 2000
Introduction
Use Form Builder to simplify for the creation of data-entry screens, also known as Forms. Forms are the applications that connect to a database, retrieve information requested by the user, present it in a layout specified by Form designer, and allow the user to modify or add information. Form Builder allows you to build forms quickly and easily.
In this Hands-On, you learn how to: Create a Data block for the “Customer” table, Create a layout, Use “content” canvas, Use “execute query”, Navigate a table, Use next, previous record, Enter query, Manipulate table’s record, Insert, Update, Delete and Save record.
Form Builder Tool
Open the "Form Builder" tool.
Welcome window
You will get the ‘Welcome to the Form Builder’ window. If you don’t want to get this window anymore uncheck the ‘Display at startup’ box. You can start your work with any of the following options:
· Use the data Block Wizard
· Build a new form manually
· Open an existing form
· Build a form based on a template
The default is ‘Use the data Block Wizard.’ If you want to build a new form manually, click on "Cancel” or check ‘Build a new form manually’ and click ‘OK.’
Connect to database
In the ‘Object Navigator’ window, highlight "Database Objects." Go to the Main menu and choose "File," then "Connect."
In the ‘Connect’ window, login in as “scott” password “tiger,” then click “CONNECT.”
Notice that the box next to ‘Database Objects’ is not empty anymore and it has a ‘+’ sign in it. That will indicate that this item is expandable and you are able to see its entire objects.
Click on the ‘+’ sign next to the ‘Database Objects’ to expand all database schemas.
Create a Module
In the ‘Object Navigator’ window, highlight module1. This is a default name. Go to the Main menu and choose “File,” select “Save as” to store the new object in the “iself” folder and save it as customer data entry. "c:_de." In this example the ‘DE’ abbreviation stands for Data Entry.
Create a Data Block
In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon. The ‘Create’ icon is in the vertical tool bar in the ‘Object Navigator’ window. It is a green ‘+’ sign. If you drag your cursor on the icon a tooltip will show ‘Create.’
New Data Block
In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."
Welcome Data Block
In the ‘Welcome Data Block Wizard’ window click on the “NEXT” icon.
Type of Data Block
Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.
Selecting Tables
Click on “browse.” In the ‘Tables’ window, highlight the "cust11” table; then click "OK."