SQL:
Alter: Chnage the defination object.
Syntax: ALTER table
Keyword
-------------------------,
------------------------
);
Keywords: 1) Modify 2) Add 3) Disable 4) Enable 5) Drop.
Ex: a) Modify is to 1) to increase or decrease the size of width of the column.
2) Change the datatype. (for this the column should be empty)
3) to change null to NOTNULL.
Ex: Alter table employee modify(Empname varchar2(50));
Note: Column to be modify must be empty. But we can increase the size or to change the
data type.[ assume the salary is empty. alter table employee modify (salary varchar(10)); ]
--- Exception: Not posible in date type. Because date is fixed we cannot give the size.
[ Alter table employee modify (doj date(10));
Note: If any constraint is there on the column to change then 1st we have to remove the
constraint and then do modification.
-- Alter table Employee modify(sal number(7,2) NULL);
---Alter table Employee modify( sal number(7,2) NOT NULL);
note: Verify that null values are there in the data already present in table.
ADD: Is 1) to add the new column to the existing table.
2) to add new constraint.
--Alter table emp21 add (address varchar(25));
--Alter table emp21 add(Phone_no number(16), Email varvarchar(30));
Note: We can add constraint with the new columns provided the table must be empty.
ORACLE PL/SQL AND SQL LOADER
-- to add primary key on Emp_id:
Alter table Emp21 add Primarykey(Emp_id); We cannot write like{ alter table
Emp21 add(Emp_id number(2) Primarykey);}
-Alter table Emp21 add unique(Ename);
-Alter table Emp21 add check(sal between 1200 and 1800);
--Add foreign key for Emp21 deptno which refers to Deptno of Dept table
Alter table Emp21 Add Foreign Key(Deptno) references Dept(Deptno) on delete Cascade;
--- Add not null constraint to Ename column of Emp21 table.
Alter tabel Emp21 Modify (Ename NOTNULL);
-To add NotNull constraint use modify class.[ because bydefault Every cell is null].
Disable, Enable and Drop:These classes are to change the constraints specifications.
Syntax: Alter table
Constraint
- Alter table Emp21
Disable Constraint SYS_coo1234;
Note: Assume the contraint Name : SYS_c001234.
Note: Parent Keys cannot be disabled.
i.e Primary key linked with Foreign keys.
For PrimaryKey: Alter table Emp21 Disable Primary key;
Note: From 8.1 onwards
Alter table
-Delete for Rows.
-Drop for columns.
Drop: (DDL Command)
Syntax: Drop table
Note: provided there are no dependent tables.
-- Drop table
tables, but the constraints are dropped not dependent tables)
DML COMMANDS:(SUDI) [select, update, delete, Insert]
Insert: is to add rows to the database table.
ORACLE PL/SQL AND SQL LOADER
Syntax: Insert into
[(
(
EMP:
Empno N(4),
Ename VC2(10),
Job VC2(10),
MRG N(4),
Hiredate Date,
Sal N(7.2),
Com N(7,2),
Deptno N(2).
-Insert into Emp [optional] values (100,'Karthik','cleark',1700,'10-sep-04', 500, 200, 10);
Note: columns are optional only when we are inserting all values of the table in the same order
donot need columns otherwise you need to give column names.
Sysdate is the function to get the current date.
Null should be without single quotes. [ 'null' X] [ null Currect].
- Insert into Emp (Empno, Ename, Sal, Deptno) values(2,'raju',sal,200,30);
Even if we send 4 values in the above Ex but in actuval the remaining cols values are sent as
nulls. Because Rule No (3).
-Note: To enter the date value of B.C.
-Insert into Emp(------) values (----------, To-Date('01-Jan-1972 B.C','DD-MM-YYYY B.c'));
To enter the values BY user in the prompt message:
--insert into emp (Empno, Ename, Sal, Deptno, Hiredate) values (&empno,'&ename', &sal,
&deptno,'&Hiredate');
enter value
-1 row created message u will get.
Copying the Rows from one table to another table:
-Insert into
Ex:Insert into Emp1
ORACLE PL/SQL AND SQL LOADER
Select Empno, Ename Sal, Deptno from Emp;
Create Another table like Emp along with DAtA.
- Create table
Create Another table like Emp without DATA:
- Create table
Note: We can insert a record with all null values and we can create no. of records (no
limitation max).
-Insert into Emp (empno) values (null);
- set verify off = (not to errors verify)
Insert into Emp (Emono, Ename, Sal, Deptno) values (&emono,'&ename',&sal,&dno);
Query:
Select: Is to retrive rows of information from one or more tables.
Select * [
Select * from Emp;
select all from Emp;
Projection: Listing the specified columns information is projection.
-Display the empno,ename,sal,deptno of all the employees.
- select empno, ename, sal , Deptno from Emp;
-Selection od Unique rows using Distinct Class.
Syntax: Select Distinct
Select distinct job from emp;
select distinct( deptno , job) from emp ;
Select distinct all from emp;
select distinct * from emp;
-Sorting the output using order by clause:
-select [distinct]
from
ORACLE PL/SQL AND SQL LOADER
order by
Select * from emp order by sal DESC;
Select * from emp order by Hiredate ASC;
Note: old small date is considered as small date; i.e. today date is big compard to yesterdays in
oracle.
Note: By default sort order is ascending in oracle.
Display the deptno, job,ename,sal of the employees in asc order of deptno and the desc
order of job.
- select * from emp order by Deptno ASC, job Desc;
Note: NULL is considered as the biggest value for sorting order.
Note: single byte overhead mechanism. i.e null is assigned as the near ( available values)+1
byte. i.e. biggest not null values memory size + 1 byte is assigned to null values. so that null
is considered as to be the biggest.
-Select [distinct]
ASC | desc,
ASC | DESC...;
- select * from emp where job='clerk' order by sal Desc;
Note: Distinct clause sort the data in assecending order by default even if we would not
specify the order clause.
- List the emp who joined on after 1st April 1980.
-Selecr * from emp where hirdate is >= '01-Apr-1980';
Operators:
Arthematic : +,-,*,/.
Relational: <,>,>=, <=, in, between, like, is null, like, is null.
!=, <>, ^= these all are notequal opertions.
Not in, Not between, Not like, Is not NULL.
Logical Operators: AND, OR, NOT.
Set Operators: Union, Union all, Intersect, minus.
ORACLE PL/SQL AND SQL LOADER
- Select * from emp where sal>1800;
- select sal, sal*12 annualsal from emp;
-select eno, ename, sal, (sal*12)/365 Dailysal, from emp order by Dailysal Desc;
- Note: Alias name is used only for order by clause. In where group by clause is invalid .i e.
order by clause is executed final.
-Select emono, ename, sal, (sal/100)*30 TA, (sal/100)*40 DA, (sal/100)*30+(sal/
100)*40+(sal/100)*50 HRA, TA+DA+HRA totall, (sal/100)*12 PF, (sal/100)*2 IT, Grosssal -
(sal/100)*18 - 80 netsal from emp Order by netsal Desc;
Note: Set NUM 4; [to change the disply size].
Set linesize 1000; [ 1000 charcters in of screen];
>showall; [all line selects]
- Display who annual sal is > 3000. - Select * from em sal*12 >3000;
- Display whos exp is >20 years. -- select * from emp where (sysdate-Hiredate)/365 > 20
order by Hiredate Desc;
- In operator: is used to define the list of values. all the elements ( values) are always
enclosed within paranthesis ();
- Select * from emp where deptno in ( 10, 20 , 30);
- Note: Between numbers and dates only.
-select * from emp where (sysdate-Hitedate)/365 between 10 and 30; [ no brackets]
- select * from emp where Hiredate between '01-Jan-1981' and '31-dec-2009';
- Like operator: operators is match a patterns of charactors %= any.
- select * from emp where ename like'____';
-select * from emp where ename like's%k%'; [ it select the name starts with s and in between k is
avilable names. like sivakumar, shivak, sks,sk].
-select * from emp where ename like's_K%'; [ it select the name starts with s next any charector
next k next any char(s) like ssksdsa, smk].
-select * from emp where ename like'%kumar';
-select * from emp where ename like'%h'; [ it select the name ends with H].
- select * from emp where eno like'%989%'; { selects number consiats 989].
- select * from emp where hiredate like'%jan%' or '___jan____'; selects who joined in jan.
- select * from emp where hiredate like'0%'; [who joined in first 10 days].
ORACLE PL/SQL AND SQL LOADER
Is null operator used to match the null values.
-Display all the employee who are not receiving the Commision.
select * from emp where commision is null.
Note: Is null is 2 words.
select * from emp where (sysdate-Hiredate)/365 between 10 and 30 and deptno in(10,90)
and sal*12 not between 20,000 and 37000 and ename like'S%';
Joins: Retrieval of data from more than one table.
Class: 07-18-2009 11-7pm:
- Same data bases stores data in multidimensional arrays.
-Data base objects: tables, views, triggers etc.
- blobs, clobs data types.
- Materialized views [MVS]: New concept. It stores data physically. CDC Change data
Capture. Any inserts, update, delete. MV using incremental refresh, full refresh.
-[when u build we use full refresh is used more than 50 of table changes every day.].
Incremental refresh use the cdc. U can specify the time to refresh at that time. Incremental
refresh is get only modifications of the table. where full refresh it delete all data in MV next
it fetches the data from tables.
- Delete, Drop, Truncate.
- delete is just mark as not available for see. The space is still being use. It delete the record
where the condition is satisfied. we can rollback delete.
- Truncate is deletes all the records of table. Space is available. It is faster than delete.
Structure will be available. Truncate run on table we can not give where condition. Doesn't
need commit it treated as DDL. we cannot rollback truncate.
- Drop It delete the hole table with structure.
- If u run Reorg command. Then u will get the space of records what u deleted.
DDL: it has Auto Commit[implicit]. It deals with Structure.
DML: It doesn't have auto commit. We need to do manually.
Why we need sqlplus:
>set serverout put;
>just u for debugging. It shows message in the screen what u write in program.
ORACLE PL/SQL AND SQL LOADER
-Set screen size to visible u r screen.
-Select *[all] from emp where rownum<100; {u need to write in this only select 100 rows. ]
-we can end the command : or / .
- Create emp1 as select * from emp; [to create same table] and truncate table table name. it
creates only structure not constrints. we need to make constraints.
- desc table name.[ description of table structure].
-Rdbms: Primary key in every table. we can identify unique any row using primary key. we can
make primary key with more than one column. Only one PK in one table.
- Foreign key is the primary key of other table. We can have many foreign keys.
- select * from all_objects where rownum<20;
-select * from all_tables where rownum<20;
-select * from user_tables where rownum<20;
-select * from all_views where rownum<20;
-select * from all_objects where object_type='TABLE' and object_name='EMP';
all objects are stored in UPPEER case these are case sensitive.
-
Create, insert, delete.
- ed a.sql--> yes--> write sql command---> file exit--> run this like @a.sql.
- insert into emp select * from emp1;all the data will copy from emp1 to emp;
--Commit.[ like save]
--VIew.
Grant the privilages:
cmd prompt: sqlplus
connect as sysdba
password blank
grant create view to scott;
next u will get greant succed.
--Donot use delete command with out where class.
-- donot insert data in view;
--every time write create or replace for what ever u create except table;
- select count(*) from emp; it takes more time. so use Select count(1) from emp;
ORACLE PL/SQL AND SQL LOADER
Count do in primarykey column. because it cannot read null.
Home work: create MV, emp1 table all row insert in to MV. Refresh every one hour. Add
employee in emp. next see in mv. After one hour delete the same row next see in MV.
Concatenation:
select || '&' || lastname.
splitting is: substring, insrting.
Operators: In, notin, between, >, <, <=, >=, Like,
Joins: Equi join{ compares between primary key in one table, foreign kay in onother table}
Self join: Select * from emp e,emp e1 where e.managerno(+)=e1.empno; [hierarchycal
relationship]
Nonequi join:
outer join, left outer join, right outer join.( we need to put the + simble)
Note: A table can only be outer join with only one other table. 2 table cannot be outer join
to each other.
Ex: I=C(+), C=C1(+)
-- CONNECT/ AS SYSDBA ;
-- GRANT CREATE MATERIALIZED VIEW TO SCOTT;
--- In MV FAST= incremental refresh, other is FULL refresh.
--SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f'); PL/SQL procedure
successfully completed.
UNION, UNION ALL:
--UNION: [empactive, empretaire]. It retrives only distinct records.
-- UNION ALL . It retrives all record from the both tables.
Note: We can not use the union or union all for differenet column structure in one table with
other table structure. For that we do if 4th column is not avilable then u can put NULL.
EMP_active:
eno ename sal
ORACLE PL/SQL AND SQL LOADER
Emp_retaire:
eno ename sal retaire_date
so we for union to above table like
select * from emp_active union select * from emp_retaire; --- It gives error.
so we use like select eno, ename,sal, null from emp_active UNION select eno,
ename,sal,retaire_date from emp_retaire;
so we use like select eno, ename,sal, null from emp_active UNION ALL select eno,
ename,sal,retaire_date from emp_retaire;
so we use like select eno, ename from emp_active UNION select eno, ename from emp_retaire;
so we use like select eno, ename,sal, null from emp_active UNION select eno,
ename,null,retaire_date from emp_retaire;
select empno,null from emp union select empno,bonus from emp1;
select empno,ename from emp union all select empno, ename from emp1;
select empno,ename,null from emp union select empno,ename,bonus from emp1;
select empno,ename, bonus from emp1 union all select empno,ename,null from emp1;
MINUS:
SQL> select empno from emp1 minus select empno from emp;
EMPNO
----------
111
SQL> select empno from emp minus select empno from emp1;
no rows selected
we use minus in primary key column.
Aggregate Operations:
SUM, AVG, MAx, MIN.
ORACLE PL/SQL AND SQL LOADER
-- select deptno,sum(sal) from emp group by deptno;
how many columans are there in aggregate before all columns are need to be in group by.
This displays the manager no, manaher no, how many employees with manager and avg sal of
that employess.
-select e.mgr,e1.ename, count(e.empno),avg(e.sal) from emp e,emp e1 where
e.mgr=e1.empno group by e.mgr,e1.ename;
output:
MGR ENAME COUNT(E.EMPNO) AVG(E.SAL)
---------- ---------- -------------- ----------
7566 JONES 2 3000
7782 CLARK 1 1300
7839 KING 3 2758.33333
7698 BLAKE 5 1310
7902 FORD 1 800
7788 SCOTT 1 1100
6 rows selected.
--Display Columnnames different then existion column names:
select deptno department_no, sum(sal) sum_of_sal, avg(sal) Average_of_sal , max(sal)
Maximam_sal, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP from emp
group by deptno;
output:
DEPARTMENT_NO SUM_OF_SAL AVERAGE_OF_SAL MAXIMAM_SAL
MINIMUM_SAL COUNT_OF_EMP
------------- ---------- -------------- ----------- ----------- ------------
30 9400 1566.66667 2850 950 6
20 10875 2175 3000 800 5
10 8750 2916.66667 5000 1300 3
Note:Order of qurey: Select from where, group by, Having.
Having Class:
SQL> select deptno department_no, sum(sal) sum_of_sal, avg(sal) Average_of_sal , max(sal)
Maximam_sa
ORACLE PL/SQL AND SQL LOADER
l, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP from emp group by deptno
having sum(sal)>10000;
DEPARTMENT_NO SUM_OF_SAL AVERAGE_OF_SAL MAXIMAM_SAL
MINIMUM_SAL COUNT_OF_EMP
------------- ---------- -------------- ----------- ----------- ------------
20 10875 2175 3000 800 5
SQL> select deptno department_no, sum(sal) sum_of_sal, avg(sal) Average_of_sal , max(sal)
Maximam_sa
l, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP from emp group by deptno
having sum(sal) between
9000 and 12000;
DEPARTMENT_NO SUM_OF_SAL AVERAGE_OF_SAL MAXIMAM_SAL
MINIMUM_SAL COUNT_OF_EMP
------------- ---------- -------------- ----------- ----------- ------------
30 9400 1566.66667 2850 950 6
20 10875 2175 3000 800 5
SQL> select deptno department_no, sum(sal) sum_of_sal, avg(sal) Average_of_sal , max(sal)
Maximam_sa
l, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP from emp group by deptno
having sum(sal)>10000 an
d count(empno) <5;
no rows selected.
FLOOR(9.53)=9; [ it give round to below value.]
CEILING(9.53)=10;[ it gives round to above value]
ROUND: ROUND(8.45)=8;
ROUND(8.6)=9;
Round(8.5)=9;
NOTE: IF ROUND THE ROW BY ROW AND IF U ADD U WILL GET ONE RESULT.
IF U SUM FIRST NEXT ROUND U WILL GET DIFFERENT RESULT. SO U NEED TO
BE CAREFULL ABOUT CLIENT REQUIREMENT. OTHERWISE U WILL BE GET
PROBLEM WITH U R RESULTS.
ex: select deptno department_no, round(sum(sal)) sum_of_sal, ceil(avg(sal)) Average_of_sal ,
floor(max(sal)) Maximam_sal, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP
from emp group by deptno;
ORACLE PL/SQL AND SQL LOADER
output: DEPARTMENT_NO SUM_OF_SAL AVERAGE_OF_SAL MAXIMAM_SAL
MINIMUM_SAL COUNT_OF_EMP
------------- ---------- -------------- ----------- ----------- ------------
30 9400 1567 2850 950 6
20 10875 2175 3000 800 5
10 8750 2917 5000 1300 3
--with out ceil, round, floor:
select deptno department_no, sum(sal) sum_of_sal, avg(sal) Average_of_sal , max(sal)
Maximam_sal, min(sal) MINIMUM_SAL, count(empno) COUNT_OF_EMP from emp
group by deptno;
DEPARTMENT_NO SUM_OF_SAL AVERAGE_OF_SAL MAXIMAM_SAL
MINIMUM_SAL COUNT_OF_EMP
------------- ---------- -------------- ----------- ----------- ------------
30 9400 1566.66667 2850 950 6
20 10875 2175 3000 800 5
10 8750 2916.66667 5000 1300 3
sub queries[sq]:
- we can write sub queries in select , from, where.
-If u write subquery in select is called inline subquery.
-emp table:
eno ename mgrno
Mgr table:
mgrno mname
we need eno, ename,mname:
ex:
Q) Display employee no, name, manager name .
Ans) select eno,ename, (select mname from mgr where emp.mgrno=mgr.mgrno) from
emp;
Note: we should to write select class inline query with outer table.
ex:select e.empno,e.ename,e.deptno,e1.ename Manager,
ORACLE PL/SQL AND SQL LOADER
(select dname from dept where e.deptno=dept.deptno) dept_name,
(select grade from salgrade s where e.sal>s.losal and e.sal
from emp e,emp e1
where e.mgr=e1.empno;
output:
EMPNO ENAME DEPTNO ENAME DEPT_NAME SAL_GRADE
---------- ---------- ---------- ---------- -------------- ----------
7902 FORD 20 JONES RESEARCH
7788 SCOTT 20 JONES RESEARCH
7900 JAMES 30 BLAKE SALES 1
7844 TURNER 30 BLAKE SALES 3
7654 MARTIN 30 BLAKE SALES 2
7521 WARD 30 BLAKE SALES 2
7499 ALLEN 30 BLAKE SALES 3
7934 MILLER 10 CLARK ACCOUNTING 2
7876 ADAMS 20 SCOTT RESEARCH 1
7782 CLARK 10 KING ACCOUNTING 4
7698 BLAKE 30 KING SALES 4
EMPNO ENAME DEPTNO ENAME DEPT_NAME SAL_GRADE
---------- ---------- ---------- ---------- -------------- ----------
7566 JONES 20 KING RESEARCH 4
7369 SMITH 20 FORD RESEARCH 1
In the above query i missed >= and <= at salgrade. so i wrote again.
Q) Display employee no,name,deptno,manger name, deptname,salgrade ?
Ans) select e.empno,e.ename,e.deptno,e1.ename,
(select dname from dept where e.deptno=dept.deptno) dept_name,
(select grade from salgrade s where e.sal>s.losal and e.sal
from emp e,emp e1
where e.mgr=e1.empno;
output:
EMPNO ENAME DEPTNO ENAME DEPT_NAME SAL_GRADE
--------- ---------- ---------- ---------- -------------- ----------
7902 FORD 20 JONES RESEARCH 4
7788 SCOTT 20 JONES RESEARCH 4
7900 JAMES 30 BLAKE SALES 1
ORACLE PL/SQL AND SQL LOADER
7844 TURNER 30 BLAKE SALES 3
7654 MARTIN 30 BLAKE SALES 2
7521 WARD 30 BLAKE SALES 2
7499 ALLEN 30 BLAKE SALES 3
7934 MILLER 10 CLARK ACCOUNTING 2
7876 ADAMS 20 SCOTT RESEARCH 1
7782 CLARK 10 KING ACCOUNTING 4
7698 BLAKE 30 KING SALES 4
EMPNO ENAME DEPTNO ENAME DEPT_NAME SAL_GRADE
--------- ---------- ---------- ---------- -------------- ----------
7566 JONES 20 KING RESEARCH 4
7369 SMITH 20 FORD RESEARCH 1
Note: inline subquery we cannot get more than one column.
Sub query in where class:
Invoice:
inv_id Chk_id we cannot put
1 chk1
1 this is pK chk2
Checks:
chk_id Inv_id i cannot put
chk1 1,2 ()
If we have all data in one table and select condition is in other table we using where subquery.
Here we can use >,<,<=,>=IN/notin, Exist, not Exist.
EX:
Q) Display al the employee informantion where employee department name starts with A.
A)select * from emp1 where deptno=(select deptno from dept where dname like('A%'));
outut:
set linesize 200;
@m.sql;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
BONUS
------ ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9999
ORACLE PL/SQL AND SQL LOADER
7839 KING PRESIDENT 17-NOV-81 5000 10 9999
7934 MILLER CLERK 7782 23-JAN-82 1300 10 9999
22 kumar head 1234 17-MAR-03 1239.5 233 10 55.5
Ex:
Q) Display al the employee informantion where employee department loc contain lla.
A) select * from emp1 where deptno in
(select deptno from dept where Loc like('%lla%'));
ans:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
BONUS
------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9999
7839 KING PRESIDENT 17-NOV-81 5000 10 9999
7934 MILLER CLERK 7782 23-JAN-82 1300 10 9999
22 kumar head 1234 17-MAR-03 1239.5 233 10 55.5
Exists:
SUBQUERY IN FROM CLAUSE: INLINE VIEW: IF U ARE U VIEW SING SELFJOIN
WE CAN GO FOR ONE TABLE AS SMALL SO WE CAN REDUSE THE COMPARISIONS.
ex: select e.empno,e.ename,e1.ename Manager from emp e,
(select empno,ename from emp where mgr is not null) e1
where e.mgr=e1.empno;
ANS:
EMPNO ENAME MANAGER
-------- ---------- ----------
7369 SMITH FORD
7499 ALLEN BLAKE
7521 WARD BLAKE
7654 MARTIN BLAKE
7788 SCOTT JONES
7844 TURNER BLAKE
7876 ADAMS SCOTT
7900 JAMES BLAKE
7902 FORD JONES
7934 MILLER CLAR
Rule based optimizer:before oracle older versions. The tables where u put left side of where
or right side of where . It will take query run time is different.
ORACLE PL/SQL AND SQL LOADER
cost based optimizer:After 8i,to day we have this. The tables where u put left side of where
or right side of where . It will take query run time is same.
we donot do custom indexs. because oracle doesnot support.
index are good for serching and retrival only. But if u want to insert or update will be slow.
So when u are inserting a bulk of data u need to disable and insert data next enable indexs.
Indexes:
1) Clusterd index
2) Non clusterd index
3) bit map index
Select * from all_indexes.
Note: If the column has alredy data we cannot change the data type of column. But we can
increase the size of datatype.
Explain PLan:[plan_table]
Compute statistics.
Estimate statistics.
ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary
data. This decision can be made using one of two methods:
• Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics
relating to the objects referenced by the statement. This method is no longer favoured by
Oracle and will be desupported in future releases.
• Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The
CBO checks several possible execution plans and selects the one with the lowest cost,
where cost relates to system resources.
If new objects are created, or the amount of data in the database changes the statistics will no
longer represent the real state of the database so the CBO decision process may be seriously
impaired. The mechanisms and issues relating to maintenance of internal statistics are explained
below:
• Analyze Statement
• DBMS_UTILITY
• DBMS_STATS
• Scheduling Stats
ORACLE PL/SQL AND SQL LOADER
• Transfering Stats
• Issues
Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster.
The statistics can be computed exactly, or estimated based on a specific number of rows, or a
percentage of rows:
ANALYZE TABLE employees COMPUTE STATISTICS; ANALYZE INDEX
employees_pk COMPUTE STATISTICS; ANALYZE TABLE employees
ESTIMATE STATISTICS SAMPLE 100 ROWS; ANALYZE TABLE employees
ESTIMATE STATISTICS SAMPLE 15 PERCENT;
DBMS_UTILITY
The DBMS_UTILITY package can be used to gather statistics for a whole schema or database.
Both methods follow the same format as the analyze statement:
EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE'); EXEC
DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent
=> 15); EXEC DBMS_UTILITY.analyze_database('COMPUTE'); EXEC
DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100); EXEC
DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);
DBMS_STATS
The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of
gathering object statistics. Oracle list a number of benefits to using it including parallel
execution, long term storage of statistics and transfer of statistics between servers. Once again, it
follows a similar format to the other methods:
EXEC DBMS_STATS.gather_database_stats; EXEC
DBMS_STATS.gather_database_stats(estimate_percent => 15); EXEC
DBMS_STATS.gather_schema_stats('SCOTT'); EXEC
DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15); EXEC
DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES'); EXEC
DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent =>
15); EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
ORACLE PL/SQL AND SQL LOADER
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK',
estimate_percent => 15);
This package also gives you the ability to delete statistics:
EXEC DBMS_STATS.delete_database_stats; EXEC
DBMS_STATS.delete_schema_stats('SCOTT'); EXEC
DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES'); EXEC
DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');
Scheduling Stats
Scheduling the gathering of statistics using DBMS_Job is the easiest way to make sure they are
always up to date:
SET SERVEROUTPUT ON DECLARE l_job NUMBER; BEGIN
DBMS_JOB.submit(l_job, 'BEGIN
DBMS_STATS.gather_schema_stats(''SCOTT''); END;', SYSDATE, 'SYSDATE +
1'); COMMIT; DBMS_OUTPUT.put_line('Job: ' || l_job); END; /
The above code sets up a job to gather statistics for SCOTT for the current time every day. You
can list the current jobs on the server using the DBS_JOBS and DBA_JOBS_RUNNING views.
Existing jobs can be removed using:
EXEC DBMS_JOB.remove(X); COMMIT;
Where 'X' is the number of the job to be removed.
Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between
servers with varying amounts of data. First the statistics must be collected into a statistics table.
In the following examples the statistics for the APPSCHEMA user are collected into a new table,
STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC
DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE'); SQL>
ORACLE PL/SQL AND SQL LOADER
EXEC
DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import,
SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
SQL> EXEC
DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');
Issues
• Exclude dataload tables from your regular stats gathering, unless you know they will be
full at the time that stats are gathered.
• I've found gathering stats for the SYS schema can make the system run slower, not
faster.
• Gathering statistics can be very resource intensive for the server so avoid peak workload
times or gather stale stats only.
• Even if scheduled, it may be necessary to gather fresh statistics after database
maintenance or large data loads.
For more information see:
• Refreshing Stale Statistics
• DBMS_STATS
• DBMS_UTILITY
• ANALYZE
Sehema:
synonym:
Connect to SCOTT:
GRANT SELECT ON EMP TO
create synonym synonym-name for object;
create public synonym synonym-name for object;
note: Update, delete we can use with out where clause.
note:alter session set current_schema='scott';[ then we are can use like same as scott user
no need to give scott.emp . we can call a object directly.
how to see synonym:
ORACLE PL/SQL AND SQL LOADER
select * from all_objects
where object_type='SYNONYM'
and object_name='EMP';
Explisit convertion:
To-num, To-char, To-date;
Absolute:[abs]
MOD:
SQL> select * from emp where deptno= &deptno and ename=&ename;
Enter value for deptno: 10
Enter value for ename: 'MILLER'
old 1: select * from emp where deptno= &deptno and ename=&ename
new 1: select * from emp where deptno= 10 and ename='MILLER'
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------se--- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10
select * from emp where deptno = &deptno or deptno= &siav;
select * from emp where deptno in(&deptno,&siva,&ravi);
DUAL : empty table. It is used functions and caliculations.
note: alter session set current schema=scott; [ then u donot need to create synonym]
-Order by: ASC/DESC. order by we can use group by before after before we can use. By
default asc.
ASCII():
CHAR():
Lower():
Upper():
Inilcap:
ORACLE PL/SQL AND SQL LOADER
Ltrim():
Rtrim():
Substr():
INsrt():
Lpad:
Rpad:
Translate():
Replace():
Decode():
Soundex():
Concat():
Length():
ABS():
Round():
Trunc():
SIGN():
MOD():
SQRT():
POWER():
GREATEST():
LEAST():
NVL():
NVL2();
CEIL():
FLOOR():
MONTH_BETWEEN():
ADD_month():
lAST_DAY():
NEXT_DAY():
SYSDATE():
ROWID():
ROWNUM:
CURRVAL():
NEXTVAL():
LEVEL():
USER():
scr();
SAVEPOINT();
COMMIT()
ROLLBACK();
dcl: GRANT, REVOKE.
Friday: 24-july-2009:
ORACLE PL/SQL AND SQL LOADER
Decode: In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE
statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted,
then the decode statement will return null (if no matches are found).
Note: we can update the multiple columns at a time.
Note: docode it will do only comparison and do something. It cannot do (<,>,<=,>+,in,not).
Note: we can do char comparison. like operator.
ex:select deptno,ename,
decode(deptno, 10, sal*1.1, 20, sal*1.6, 30, sal*2)result
from emp3;
Update using decode:
update emp3 set sal=decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3,sal*1.5);
Case: same like switch case in C. If u want use comparison with >, <, we use case.
Basic syntax
CASE expression syntax is similar to an IF-THEN-ELSE statement. Oracle checks each
condition starting from the first condition (left to right). When a particular condition is satisfied
(WHEN part) the expression returns the tagged value (THEN part). If none of the conditions are
matched, the value mentioned in the ELSE part is returned. The ELSE part of the expression is
not mandatory-- CASE expression will return null if nothing is satisfied.
case when
Examples
The following examples will make the use of CASE expression more clear.
E.g.: Returning categories based on the salary of the employee.
select sal, case when sal < 2000 then 'category 1' when sal < 3000 then 'category 2' when sal <
4000 then 'category 3' else 'category 4' end from emp;
Eg 2:select count(case when sal < 2000 and comm is not null then 1 else null end), count(case
when sal < 2000 and comm is null then 1 else null end), count(case when sal < 5000 and comm
ORACLE PL/SQL AND SQL LOADER
is not null then 1 else null end), count(case when sal < 5000 and comm is null then 1 else null
end), count(case when sal > 5000 then 1 else null end) from emp;
Write explain paln for case and decode check cost_cpu:
explain plan set statement_id='decode' for select sal, decode(deptno,10, 'category 1'
,30,'category 2','category 3' ) from emp3;
explain plan set statement_id='case' for select sal, case when deptno <= 20 then 'category 1'
when deptno <= 30 then 'category 2'
when deptno <= 40 then 'category 3'
else 'category 4'
end from emp3;
select * from plan_table;
u can see the cost of those queries.
Updating using case statement:
ex: emp3 set sal= case when deptno <= 20 then sal*1.2
when deptno <= 30 then sal*1.3
when deptno <= 40 then sal*1.1
else sal*2
end ;
We acn delete the using case also.
delete from emp3 where ename=case when deptno=20 then 'CLERK'
when deptno = 30 then 'ALLEN' end ;
FILE:[XXXX]
We can create file using functions, prosedures .
Note: we cannot generate file with different structure line in file using with sql queries. If
linear structure we can do with sql queries.
High water level mark:
If any table the rows are daily inerting and deleting more records. so we go for high water
level mark.
This question can ask in perpormance tuning.
to do this estimate statistics, compute statistics. It will give the idea how to use those empty
records optimal plan.
ex: ANALYZE table emp3 compute statistics;
ORACLE PL/SQL AND SQL LOADER
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;
DBMS_UTILITY.ANALYZE_SCHEMA
With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables,
clusters and indexes of a schema.Code examples exec
DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE'); exec
DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000); exec
DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25); exec
DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE'); Note: It's also possible to
analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
command.
LPAD & RPAD:
lpad ('string', n [, 'string_pad') rpad ('string', n [, 'string_pad')
string is left padded to length n with string_pad. If string_pad is ommited, a space will be
used as default
rpad is similar, but pads right instead of left.
ex:
select lpad('string with 25 characters', 20) lpad, rpad('string with 25 characters', 20) rpad from
dual;
ex:
begin for i in 1 .. 15 loop dbms_output.put_line( lpad('string', i) || '<' ); end loop; end;
Note: We can use for performance tuning is hints.
HINTS:
Fisrt row, last row, index we commanly use hints.
syntax:select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias;
we are forcefully doing index on table.
detals
note:If we hav e 2 tables. If u campare two columns where that doesnot have index on that
column. then we can compare with inedx column also.
Note: Forceble index : we use +0.
ex: e.deptno+0=d.deptno;
Note: select statement like this:
select
,
ORACLE PL/SQL AND SQL LOADER
,,
from
---,
,,
where 1=1 and
,
---,
,,
; So we can comment easily.
Always use after where 1=1 and next u r conditions.
Note: ROWID.
NVL2():
Difference between NVL TO NVL2.
NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.
COALESCE(expr[,expr]…)
Returns the first non-null expr in the expression list.
Translate Function():
translate( string1, string_to_replace, replacement_string )
For example:
translate('1tech23', '123',
'456); would return '4tech56'
translate('222tech, '2ec',
'3it'); would return '333tith'
1) How to get all the record of every day we store into temp table. Every day table populate. All
the data of old data is removed from temp table.
so we go pl/sql.
Procedures, functions, packages.
ORACLE PL/SQL AND SQL LOADER
-function returns somthing . But procedure does not return any thing.
- package is made with functions and procedures.
Procedure:
1) Create or replace proceduretoday_invoices is/as
begin
excute truncate table
insert ---;
commit;
end;
2) alter procedure
{it will check syntax error}
3) execute procedure
ex:
create or replace procedure proc_emp_to_emp4 as
begin
execute immediate 'truncate table emp4';
insert into emp4 values(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20,0);
insert into emp4 values(7368,'siva','salesman',7901,'17-DEC-81',1200,NULL,10,0);
insert into emp4 values(7367,'kumar','Analyst',7903,'17-DEC-81',1500,NULL,20,0);
insert into emp4 values(7902,'ravi','MANAGER',null,'15-DEC-80',1800,NULL,10,0);
update emp4 set bonus=decode(deptno,10,sal*0.5,20,sal*0.2);
commit;
end;
note: set linesize 300;
Send the parameter for procedure:
create procedure prc_emp(P_deptno in number)
as
......
calling:exec prc_emp(10);
ex: create or replace procedure proc_emp4(p_deptno in number) as
begin
execute immediate 'truncate table emp4';
insert into emp4 values(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20,0);
insert into emp4 values(7368,'siva','salesman',7901,'17-DEC-81',1200,NULL,10,0);
insert into emp4 values(7367,'kumar','Analyst',7903,'17-DEC-81',1500,NULL,20,0);
ORACLE PL/SQL AND SQL LOADER
insert into emp4 values(7902,'ravi','MANAGER',null,'15-DEC-80',1800,NULL,10,0);
update emp4 set comm=200 where deptno=p_deptno;
commit;
end;
exec proc_emp4(10);
ex:
create or replace procedure proc_emp5
(p_deptno1 in number,
p_deptno2 in number,
p_date1 in varchar2,p_date2 in varchar2) as
begin
update emp5 set comm=decode(deptno,p_deptno1,sal*1.1,p_deptno2,sal*1.2,30,sal*1.3,sal*1.5);
update emp5 set comm=9999 where to_char(hiredate,'YYYY')=p_date1 or
to_char(hiredate,'YYYY')=p_date2;
commit;
end;
ex: 3)
create or replace procedure proc_emp5
(p_date1 in number,p_date2 in number) as
begin
--update emp5 set
comm=decode(deptno,p_deptno1,sal*1.1,p_deptno2,sal*1.2,30,sal*1.3,sal*1.5);
update emp5 set comm=7777 where to_char(hiredate,'YYYY') between p_date1 and p_date2;
commit;
end;
IF ELSE:
create or replace procedure proc_emp5
(p_deptno in number) as
begin
ORACLE PL/SQL AND SQL LOADER
if p_deptno = 10 then
update emp5 set comm=6666 where deptno=p_deptno;
else
update emp5 set comm=2222 where deptno=p_deptno;
end if;
commit;
end;
ifelse nested loop:
create or replace procedure proc_emp5
(p_deptno in number) as
begin
if p_deptno = 10 then
update emp5 set comm=1111 where deptno=p_deptno;
elsif p_deptno=20 then
update emp5 set comm=2222 where deptno=p_deptno;
elsif p_deptno=30 then
update emp5 set comm=3333 where deptno=p_deptno;
else
update emp5 set comm=4444 where deptno in(10,20,30);
end if;
commit;
end;
Calling sub procedure in main procedure:
we can create big proc into small proc and we can call into main proc and do action. SO the
maintenance is easy, debugging is easy, re usability of small proc in different procs.
ORACLE PL/SQL AND SQL LOADER
proc main
begin
proc1;
proc2;
proc3;
commit;
end;
message: dbms_output.put_line('i proc1 finished');
we can put messages in different places so we can debug easily.
ex:
create or replace procedure proc_main
as
begin
sub _proc1;
dbms_output.put_line('p1 finished');
sub_proc2;
dbms_output.put_line('p2 finished');
sub_proc3;
dbms_output.put_line('p3 finished');
commit;
end;
ex2:
sending para meter in main proc to sub procedure:
create or replace procedure proc_main(p_deptno in number)
as
begin
proc1;
dbms_output.put_line('p1 finished');
proc2;
dbms_output.put_line('p2 finished');
proc3(p_deptno);
dbms_output.put_line('p3 finished');
commit;
end;
ex3: different parameters
create or replace procedure proc_main(p_deptno1 in number,p_job1 in varchar2)
as
begin
proc1;
ORACLE PL/SQL AND SQL LOADER
dbms_output.put_line('p1 finished');
proc2(p_deptno=>p_deptno1);
dbms_output.put_line('p2 finished');
proc3(p_job=>p_job1);
dbms_output.put_line('p3 finished');
commit;
end;
Cursors:
Explicit cursor, implisit cursor.
note: fastest way to move data from one table to other table is explicit cursor.
Explicit cursor: if u declare cursor in between proc and begin are explicit cursor;
procedure proc1(-----) as
cursor c1_emp as
select * from emp;
begin
for x_emp in c1 loop
if x_emp.deptno=10
------------------------------
-----------------
els if x_emp .deptno=20
--------------
----------------
elsif x_emp.deptno =30
------------
-----------
else
--------
-------
end if;
end loop;
commit;
end;
Ex for explicit cursor;
create or replace procedure proc_cursor1 as
cursor c_emp is
select * from emp5;
begin
for x_emp in c_emp loop
if x_emp.deptno=10 then
update emp5 set comm=1111 where deptno=10;
ORACLE PL/SQL AND SQL LOADER
elsif x_emp.deptno=20 then
update emp5 set comm=2222 where deptno=20;
else
update emp5 set comm=3333 where deptno=x_emp.deptno;
end if;
end loop;
commit;
end;
************************************************************************
Declaration of cursor:
For example, you might declare cursors named c1 and c2, as follows:
DECLARE
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10;
or
DECLARE
CURSOR c1 (low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS SELECT ...
******************************************************************************
1) write the proc to update newsal column with sal+1000+(sal*exp/100) ?
create or replace procedure proc_salupdate as
v_year number:=0;
cursor c_emp is
select * from emp5;
begin
for x_emp in c_emp loop
--we need to do reset for all variables at starting in begin loop also.
v_year :=0;
v_year := to_char(sysdate,'YYYY') - to_char(x_emp.hiredate,'YYYY');
if x_emp.deptno=10 then
update emp5 set newsal= x_emp.sal+1000+(sal*v_year/100) where empno=x_emp.empno ;
dbms_output.put_line('The employee:'||x_emp.ename||' is updated with new sal');
ORACLE PL/SQL AND SQL LOADER
elsif x_emp.deptno=20 then
update emp5 set newsal= x_emp.sal+2000+(sal*v_year/100) where empno=x_emp.empno;
dbms_output.put_line('The employee:'||x_emp.ename||' is updated with new sal');
else
update emp5 set newsal= x_emp.sal+3000+(sal*v_year/100) where empno=x_emp.empno;
dbms_output.put_line('The employee:'||x_emp.ename||' is updated with new sal');
end if;
end loop;
commit;
end;
******************************************************************************
ex:
FUNCTION generate_test_file RETURN NUMBER
IS
CURSOR curEmployee IS
SELECT employee_id,
employee_name
employee_salary,
FROM Emp;
--Line to output
sOutputLine VARCHAR2(4000);
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
sEmployeeid varchar2(30);
sEmployeeName varchar2(50);
sEmployeeSalary varchar2(30);
sOutputLine varchar2(120);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='c:\testdata\';
c_comma VARCHAR2(2) :=',' ; -- Delimiter
BEGIN
OPEN curEmployee
sFileName := 'test.csv';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
LOOP
FETCH curEmployee INTO sEmployeeId,sEmployeeName,sEmployeeSalary;
EXIT WHEN curEmployee%NOTFOUND;
sOutputLine := sEmployeeId || c_comma ||
ORACLE PL/SQL AND SQL LOADER
sEmployeeName || c_comma ||
sEmployeeSalary;
UTL_FILE.PUT_LINE(uFileHndl,sOutputLine);
END LOOP;
CLOSE curEmployee;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(uFileHndl);
CLOSE curEmployee;
RETURN SQLCODE;
END generate_test_file;
********************************************************************
ex: Generate the file of dept and emp details:
********************************************
create or replace procedure p1
AS
CURSOR c_emp IS
select * FROM Emp5;
cursor c_dept is
select * from dept1;
--Line to output
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
sdept varchar(200);
semp varchar(300);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='TEST2';
c_comma VARCHAR2(2) :=',' ; -- Delimiter
BEGIN
sFileName := 'test'||sysdate||'.xls';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x_dept in c_dept loop
sdept := 'D'||x_dept.deptno||lpad(x_dept.dname,10,'0')||lpad(x_dept.loc,12,'0');
UTL_FILE.PUT_LINE(uFileHndl,sdept);
for x_emp in c_emp loop
ORACLE PL/SQL AND SQL LOADER
if x_emp.deptno=x_dept.deptno then
semp:='E'||x_emp.empno||lpad(x_emp.ename,20,'0')||lpad(x_emp.job,10,'0')||lpad(nvl(x_emp.mgr,0),4,'0')||x_emp.UTL_FILE.PUT_LINE(uFileHndl,semp);
end if;
end loop;
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
end;
**********************************************************************
Ex: Insert in text file data into tables:
create or replace procedure p3 as
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('TEST2','25-JUL-09test.txt','R');
loop
utl_file.get_line(f,s);
if length(s)=25 then
insert into dept2 values(substr(s,2,2),replace(substr(s,4,10),'0',''),replace(substr(s,14,12),'0',''));
else
insert into emp6
values(substr(s,2,4),replace(substr(s,6,20),'0',''),replace(substr(s,26,10),'0',''),substr(s,36,4),substr(s,40,9),substr(s,end if;
dbms_output.put_line(s);
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
end;
******************************************************************************
TRIM ():
In Oracle/PLSQL, the trim function removes all specified characters either from the
beginning or the ending of a string.
The syntax for the trim function is:
http://www.techonthenet.com/oracle/functions/lnnvl.phptrim( [ leading |
trailing | both [ trim_character ] ] string1 )
ORACLE PL/SQL AND SQL LOADER
leading - remove trim_string from the front of string1.
trailing - remove trim_string from the end of string1.
both - remove trim_string from the front and end of string1.
If none of these are chosen (ie: leading, trailing, both), the trim function will
remove trim_string from both the front and end of string1.
trim_character is the character that will be removed from string1. If this parameter is
omitted, the trim function will remove all leading and trailing spaces from string1.
string1 is the string to trim.
Applies To:
• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
trim(' tech ')would return 'tech'trim(' ' from ' tech ')would return 'tech'trim(leading '0'
from '000123')would return '123'trim(trailing '1' from 'Tech1')would return 'Tech'trim(both
'1' from '123Tech111')would return '23Tech'
GOOD Examples in http://www.techonthenet.com/oracle/index.php
ASCII:
ascii('t') would return 116.
ascii('T') would return 84.
ascii('T2') would also return 84.
LNNVL():
reference: good examples
Oracle/PLSQL: LNNVL Function
In Oracle/PLSQL, the LNNVL function is used in the WHERE clause of an SQL statement to
evaluate a condition when one of the operands may contain a NULL value.
The syntax for the LNNVL function is:
LNNVL( condition )
The LNNVL function will return the following:
Condition Evaluates
To
LNNVL Return
Value
TRUE FALSE
ORACLE PL/SQL AND SQL LOADER
FALSE TRUE
UNKNOWN TRUE
So if we had two columns called qty and reorder_level where qty = 20 and reorder_level IS
NULL, the LNNVL function would return the following:
Condition Condition Evaluates
To
LNNVL Return
Value
qty = reorder_level UNKNOWN TRUE
qty IS NULL FALSE TRUE
reorder_level IS
NULL TRUE FALSE
qty = 20 TRUE FALSE
reorder_level = 20 UNKNOWN TRUE
Applies To:
• Oracle 10g, Oracle 11g
For example:
Let's take a look at an example. If we had an products table that contained the following data:
PRODUCT_ID QTY REORDER_LEVEL
1000 20 NULL
2000 15 8
3000 8 10
4000 12 6
5000 2 2
6000 4 5
And we wanted to find all of the products whose qty was below their respective reorder levels,
we would run the following SQL statement:
ORACLE PL/SQL AND SQL LOADER
select * from products
where qty < reorder_level;
This would return the following result:
PRODUCT_ID QTY REORDER_LEVEL
3000 8 10
6000 4 5
However, if we wanted to see the products that were below their reorder levels as well as NULL
reorder levels, we would use the LNNVL function as follows:
select * from products
where LNNVL(qty >= reorder_level);
This would return the following result:
PRODUCT_ID QTY REORDER_LEVEL
1000 20 NULL
3000 8 10
6000 4 5
In this example, the result set also contains the product_id of 1000 which has a NULL reorder
level.
******************************************************************************
JULY- 26-2009:
Sending paramer to cursor:
create or replace procedure p4
AS
CURSOR c_emp IS
select * FROM Emp5;
cursor c_dept is
select * from dept2;
cursor c_salgrade(p_sal in number) is
select grade from salgrade where p_sal>losal and p_sal < hisal;
--Line to output
ORACLE PL/SQL AND SQL LOADER
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
sdept varchar(200);
semp varchar(300);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='siva';
v_salg number :=0;
BEGIN
sdept :=null;
semp :=null;
v_salg :=0;
sFileName := 'test1'||sysdate||'.txt';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x_dept in c_dept loop
sdept := 'D'||x_dept.deptno||lpad(x_dept.dname,10,'0')||lpad(x_dept.loc,12,'0');
UTL_FILE.PUT_LINE(uFileHndl,sdept);
for x_emp in c_emp loop
if x_emp.deptno=x_dept.deptno then
semp:='E'||x_emp.empno||lpad(x_emp.ename,20,'0')||lpad(x_emp.job,10,'0')||lpad(nvl(x_emp.mgr,'0'),4,'0')||x_emp.open c_salgrade(p_sal=>x_emp.sal);
fetch c_salgrade into v_salg;
close c_salgrade;
--concat(semp,v_salg);
semp:= semp||v_salg;
UTL_FILE.PUT_LINE(uFileHndl,semp);
end if;
end loop;
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
end;
********************************************************************************
ex 2: sending parameter to C-emp cursor also
create or replace procedure p5
AS
CURSOR c_emp(p_deptno in number) IS
select * FROM Emp5 where deptno=p_deptno;
cursor c_dept is
select * from dept2;
cursor c_salgrade(p_sal in number) is
select grade from salgrade where p_sal>losal and p_sal < hisal;
--Line to output
uFileHndl UTL_FILE.FILE_TYPE;
ORACLE PL/SQL AND SQL LOADER
sFileName VARCHAR2(30);
sdept varchar(200);
semp varchar(300);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='siva';
v_salg number :=0;
BEGIN
sdept :=null;
semp :=null;
v_salg :=0;
sFileName := 'test1'||sysdate||'.txt';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x_dept in c_dept loop
sdept := 'D'||x_dept.deptno||lpad(x_dept.dname,10,'0')||lpad(x_dept.loc,12,'0');
UTL_FILE.PUT_LINE(uFileHndl,sdept);
for x_emp in c_emp(p_deptno=>x_dept.deptno) loop
semp:='E'||x_emp.empno||lpad(x_emp.ename,20,'0')||lpad(x_emp.job,10,'0')||lpad(nvl(x_emp.mgr,'0'),4,'0')||x_emp.open c_salgrade(p_sal=>x_emp.sal);
fetch c_salgrade into v_salg;
close c_salgrade;
--concat(semp,v_salg);
semp:= semp||v_salg;
UTL_FILE.PUT_LINE(uFileHndl,semp);
end loop;
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
end;
******************************************************************************
How to delete records for table using rowid.
First find the location of row with rowid copy that one next write delete command as
delete from tablename where rowid>'copied rowid';
*********************************************************************************
ex 4:
declare
cursor emp cur is select from EMP;
emp rec EMP%ROWTYPE;
emp sal EMP.SAL%TYPE;
begin
open emp cur;
loop
fetch emp cur into emp rec;
exit when emp cur%NOTFOUND;
emp sal := emp rec.sal;
ORACLE PL/SQL AND SQL LOADER
end loop;
close emp cur;
. . .
end;
OR
for
- )] loop
end loop [
A comment on a column can be created using the command
comment on column
To change the password of user:
alter user
--host ls -la *.sql lists all SQL files in the current directory.
Copy the table from one user to other
copy from scott/tiger create EMPL using select from EMP;
comment in program:
Comments are introduced by the clause rem[ark] (only allowed between SQL statements),
or - - (allowed within SQL statements).
--set linesize
--set pagesize
ORACLE PL/SQL AND SQL LOADER
• SYS: This is the owner of all data dictionary tables and views. This user has the highest
privileges to manage objects and structures of an Oracle database such as creating new
users.
• SYSTEM: is the owner of tables used by different tools such SQL*Forms, SQL*Reports etc.
This user has less privileges than SYS.
• PUBLIC: This is a “dummy” user in an Oracle database. All privileges assigned to this
user are automatically assigned to all users known in the database.
--select from TAB;
retrieves the names of all tables owned by the user who issues this command.
-select from COL;
returns all information about the columns of one’s own tables.
-USER TABLES all tables with their name, number of columns, storage
information, statistical information etc. (TABS)
USER CATALOG tables, views, and synonyms (CAT)
USER COL COMMENTS comments on columns
USER CONSTRAINTS constraint definitions for tables
USER INDEXES all information about indexes created for tables (IND)
USER OBJECTS all database objects owned by the user (OBJ)
USER TAB COLUMNS columns of the tables and views owned by the user
(COLS)
USER TAB COMMENTS comments on tables and views
USER TRIGGERS triggers defined by the user
USER USERS information about the current user
USER VIEWS views defined by the user
• ALL : Rows in the ALL views include rows of the USER views and all information about
objects that are accessible to the current user. The structure of these views is analogous
to the structure of the USER views.
24
ALL CATALOG owner, name and type of all accessible tables, views, and
synonyms
ALL TABLES owner and name of all accessible tables
ALL OBJECTS owner, type, and name of accessible database objects
ALL TRIGGERS . . .
ALL USERS . . .
ALL VIEWS . . .
• DBA : The DBA views encompass information about all database objects, regardless of the
owner. Only users with DBA privileges can access these views.
DBA TABLES tables of all users in the database
DBA CATALOG tables, views, and synonyms defined in the database
DBA OBJECTS object of all users
DBA DATA FILES information about data files
DBA USERS information about all users known in the database
ORACLE PL/SQL AND SQL LOADER
****************************************************************************
Date: 31-JUL-09
******************************************************************************
EMP.Empno%TYPE
DEPT%ROWTYPE
Ex:
cursor employee cur (start date date, dno number) is
select JOB, ENAME from EMP E where HIREDATE > start date
and exists (select from EMP
where E.MGR = EMPNO and DEPTNO = dno);
Ex: for row type and column type;
declare
employee rec EMP%ROWTYPE;
max sal EMP.SAL%TYPE;
begin
select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO
into employee rec
from EMP where EMPNO = 5698;
select max(SAL) into max sal from EMP;
. . .
end;
Example:
for sal rec in (select SAL + COMM total from EMP) loop
. . . ;
end loop;
Exception handling:
when
Exception name Number Remark
CURSOR ALREADY OPEN ORA-06511 You have tried to open a cursor which is
already open
INVALID CURSOR ORA-01001 Invalid cursor operation such as fetching
from a closed cursor
NO DATA FOUND ORA-01403 A select . . . into or fetch statement returned
no tuple
TOO MANY ROWS ORA-01422 A select . . . into statement returned more
than one tuple
ZERO DIVIDE ORA-01476 You have tried to divide a number by 0
Example:
declare
ORACLE PL/SQL AND SQL LOADER
emp sal EMP.SAL%TYPE;
emp no EMP.EMPNO%TYPE;
too high sal exception;
begin
select EMPNO, SAL into emp no, emp sal
from EMP where ENAME = ’KING’;
if emp sal 1.05 > 4000 then raise too high sal
else update EMP set SQL . . .
end if ;
exception
when NO DATA FOUND – – no tuple selected
then rollback;
when too high sal then insert into high sal emps values(emp no);
commit;
end;
--drop procedure
Ex : For function:
create function get dept salary(dno number) return number is
all sal number;
begin
all sal := 0;
for emp sal in (select SAL from EMP where DEPTNO = dno
and SAL is not null) loop
35
all sal := all sal + emp sal.sal;
end loop;
return all sal;
end get dept salary;
Check Constraints:
[constraint
EX:
create table EMP
( . . . ,
ENAME varchar2(30) constraint check name
check(ENAME = upper(ENAME) ),
SAL number(5,2) constraint check sal check(SAL >= 500),
DEPTNO number(3) constraint check deptno
check(DEPTNO between 10 and 100) );
If a check constraint is specified as a table constraint,
Triggers:
create or replace trigger check salary EMP
ORACLE PL/SQL AND SQL LOADER
after insert or update of SAL, JOB on EMP
for each row
when (new.JOB != ’PRESIDENT’) – – trigger restriction
declare
minsal, maxsal SALGRADE.MAXSAL%TYPE;
begin
– – retrieve minimum and maximum salary for JOB
select MINSAL, MAXSAL into minsal, maxsal from SALGRADE
where JOB = :new.JOB;
– – If the new salary has been decreased or does not lie within the salary range,
– – raise an exception
if (:new.SAL < minsal or :new.SAL > maxsal) then
raise application error(-20225, ’Salary range exceeded’);
elsif (:new.SAL < :old.SAL) then
raise application error(-20230, ’Salary has been decreased’);
elsif (:new.SAL > 1.1 :old.SAL) then
raise application error(-20235, ’More than 10% salary increase’);
end if ;
end;
--alter table
Sql loader from one control file to two basic tables:
Data:
D|10|ACCOUNTING|NEW YORK
E|7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
E|7839|KING|PRESIDENT||17-NOV-81|5000||10
E|7934|MILLER|CLERK|7782|23-JAN-82|1300||10
D|20|RESEARCH|DALLAS
E|7369|SMITH|CLERK|7902|17-DEC-80|800||20
E|7566|JONES|MANAGER|7839|02-APR-81|2975||20
E|7788|SCOTT|ANALYST|7566|19-APR-87|3000||20
E|7876|ADAMS|CLERK|7788|23-MAY-87|1100||20
E|7902|FORD|ANALYST|7566|03-DEC-81|3000||20
D|30|SALES|CHICAGO
E|7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
E|7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
E|7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
E|7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
E|7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
E|7900|JAMES|CLERK|7698|03-DEC-81|950||30
D|40|OPERATIONS|BOSTON
ORACLE PL/SQL AND SQL LOADER
controlfile:
LOAD DATA
INFILE 'C:\siva\empdept28-JUL-091.dat'
APPEND INTO TABLE empnew
WHEN recid = 'E'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY '|'
,empno TERMINATED BY '|'
, ename TERMINATED BY '|'
, job TERMINATED BY '|'
, mgr TERMINATED BY '|'
, hiredate TERMINATED BY '|'
, sal TERMINATED BY '|'
, comm TERMINATED BY '|'
, deptno TERMINATED BY '|'
)
INTO TABLE deptnew
WHEN recid = 'D'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY '|'
,deptno TERMINATED BY '|'
, dname TERMINATED BY '|'
, loc TERMINATED BY '|'
)
or
LOAD DATA
INFILE 'C:\siva\empdept28-JUL-091.dat'
APPEND INTO TABLE empnew
WHEN (1) = 'E'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY '|'
,empno
, ename
ORACLE PL/SQL AND SQL LOADER
, job
, mgr
, hiredate
, sal
, comm
, deptno
)
INTO TABLE deptnew
WHEN (1) = 'D'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1) INTEGER EXTERNAL TERMINATED BY '|'
,deptno
, dname
, loc )
or with delimeter is ' ' for only first column:
LOAD DATA
INFILE 'C:\siva\empdept28-JUL-09.dat'
REPLACE INTO TABLE empnew
WHEN recid = 'E'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1:1) INTEGER EXTERNAL TERMINATED BY ' '
,empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
)
INTO TABLE deptnew
WHEN recid = 'D'
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(recid FILLER POSITION(1:1) INTEGER EXTERNAL TERMINATED BY ' '
,deptno
, dname
, loc )
ORACLE PL/SQL AND SQL LOADER
Ex:
I have added created date, updated date to emp table.
Write the text file to send emp information to main office every month. write procedure.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
CREATED_D UPDATED_D
------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------- ----
7369 SMITH inserted 7902 17-DEC-80 800 20 17-JUL-09 17-JUL-09
7499 ALLEN updated 7698 20-FEB-81 1600 300 30 17-JUN-09
17-JUL-09
7521 WARD inserted 7698 22-FEB-81 1250 500 30 17-JUL-09
17-JUL-09
7566 JONES MANAGER 7839 02-APR-81 2975 20 17-DEC-80
17-DEC-80
7654 MARTIN inserted 7698 28-SEP-81 1250 1400 30 17-JUL-09
17-JUL-09
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 17-DEC-80
17-DEC-80
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 17-DEC-80
17-DEC-80
7788 SCOTT updated 7566 19-APR-87 3000 20 17-MAR-09
17-JUL-09
7839 KING PRESIDENT 17-NOV-81 5000 10 17-DEC-80
17-DEC-80
7844 TURNER inserted 7698 08-SEP-81 1500 0 30 17-JUL-09
17-JUL-09
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 17-DEC-80
17-DEC-80
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
CREATED_D UPDATED_D
------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------- ----
7900 JAMES updated 7698 03-DEC-81 950 30 17-FEB-09 17-JUL-09
7902 FORD ANALYST 7566 03-DEC-81 3000 20 17-DEC-80
17-DEC-80
7934 MILLER inserted 7782 23-JAN-82 1300 10 17-JUL-09 17-JUL-09
*****************************************************************************
-- Writing a file data by using delimeter with '|' in Procedure.
create or replace procedure p_empupdates
AS
CURSOR c_empnew IS
select * FROM Emp1 ;
--Line to output
ORACLE PL/SQL AND SQL LOADER
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
semp varchar2(300);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='siva';
BEGIN
semp :=null;
sFileName := 'empupdatenew'||sysdate||'.txt';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x_emp in c_empnew loop
if ( (to_char(x_emp.CREATED_DATE,'YYYYMM')=to_char(sysdate,'YYYYMM')) )
then
semp:='I'||'|'||x_emp.empno||'|'||x_emp.ename||'|'||x_emp.job||'|'||x_emp.mgr||'|'||x_emp.hiredate||'|'||x_emp.sal||'|'||UTL_FILE.PUT_LINE(uFileHndl,semp);
elsif ( (to_char(x_emp.UPDATED_DATE,'YYYYMM')!=to_char(sysdate,'YYYYMM')) )
then
semp:='U'||'|'||x_emp.empno||'|'||x_emp.ename||'|'||x_emp.job||'|'||x_emp.mgr||'|'||x_emp.hiredate||'|'||x_emp.UTL_FILE.PUT_LINE(uFileHndl,semp);
end if;
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
end;
FTP TRANSFER FILES TO SERVER AND RETRIEVE :
REFERENCE:
http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php
With examples
EX: THE TEXT FILE CREATE IN MY DIRECTRY AND SAME FILE SENDS TO
SERVER AT THE SAME TIME.
ANS:
create or replace procedure p_ftpsend
AS
CURSOR c_empnew IS
select * FROM Emp1 ;
uFileHndl UTL_FILE.FILE_TYPE;
ORACLE PL/SQL AND SQL LOADER
sFileName VARCHAR2(30);
semp varchar2(300);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(50):='siva';
l_conn UTL_TCP.connection;
BEGIN
semp :=null;
sFileName := 'empupdatenew'||sysdate||'.txt';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x_emp in c_empnew loop
if ( (to_char(x_emp.CREATED_DATE,'YYYYMM')=to_char(sysdate,'YYYYMM')) ) then
semp:='I'||'|'||x_emp.empno||'|'||x_emp.ename||'|'||x_emp.job||'|'||x_emp.mgr||'|'||x_emp.hiredate||'|'||
x_emp.sal||'|'||x_emp.comm||'|'||x_emp.deptno;
UTL_FILE.PUT_LINE(uFileHndl,semp);
elsif ( (to_char(x_emp.UPDATED_DATE,'YYYYMM')!=to_char(sysdate,'YYYYMM')) ) then
semp:='U'||'|'||x_emp.empno||'|'||x_emp.ename||'|'||x_emp.job||'|'||x_emp.mgr||'|'||x_emp.hiredate||'|'||
x_emp.sal||'|'||x_emp.comm||'|'||x_emp.deptno;
UTL_FILE.PUT_LINE(uFileHndl,semp);
end if;
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
--CREATE OR REPLACE DIRECTORY sivaftp AS 'siva';
l_conn := ftp.login('ftp.drivehq.com', '21', 'siva711', 'ammulu');
ftp.ascii(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => 'siva',
p_from_file => 'empupdatenew'||sysdate||'.txt',
p_to_file => 'My Documents/empupdates.txt');
ftp.logout(l_conn);
ORACLE PL/SQL AND SQL LOADER
END;
*********************************************************************************
3) Retrive the file from ftp server
**************************
create or replace procedure p_ftprecive
AS
--DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.drivehq.com', '21', 'siva711', 'ammulu');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
-- p_from_file is the fto server documents files folders
p_from_file => 'My Documents/empupdates.txt',
--siva directry is creatrd on oracle where u store u r file utl_fil directory
p_to_dir => 'siva',
p_to_file => 'empupdatesfromftp'||sysdate||'.txt');
ftp.logout(l_conn);
END;
/
*********************************************************************************
1) send file to ftp server:
***************************
create or replace procedure p_ftpsend
AS
--CREATE OR REPLACE DIRECTORY sivaftp AS 'siva';
--DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.drivehq.com', '21', 'siva711', 'ammulu');
ftp.ascii(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => 'siva',
p_from_file => 'empdept28-JUL-09.txt',
p_to_file => 'test.txt');
ftp.logout(l_conn);
END;
************************************************************************************************************
ORACLE PL/SQL AND SQL LOADER
columntype variable:
vename emp.ename%type;
vemp emp.row%type;
*********************************************************************************
Instr in procedure:
**************************
create or replace procedure pempupdates as
f utl_file.file_type;
s varchar2(200);
v1 number :=0;
v2 number :=0;
v3 number :=0;
v4 number :=0;
v5 number :=0;
v6 number :=0;
v7 number :=0;
vcou number :=0;
begin
f := utl_file.fopen('siva','empupdatenew30-JUL-09.txt','R');
loop
v1 :=instr(s,'|',1,2);
v2 :=instr(s,'|',1,3);
v3 :=instr(s,'|',1,4);
v4 :=instr(s,'|',1,5);
v5 :=instr(s,'|',1,6);
v6 :=instr(s,'|',1,7);
v7 :=instr(s,'|',1,8);
vcou:=vcou+1;
utl_file.get_line(f,s);
if substr(s,1,1)='U' or substr(s,1,1)='I' then
insert into emp6(empno,ename,job)
values(substr(s,3,4),substr(s,v2+1,v3-v2-1),substr(s,v3+1,v4-v3-1));
--else
--insert into emp6 --
values(substr(s,2,4),replace(substr(s,6,20),'0',''),replace(substr(s,26,10),'0',''),substr(s,36,4),su--bs
tr(s,40,9),substr(s,49,4),substr(s,53,4),substr(s,57,2));
end if;
if mod(vcou,5)=0 then
ORACLE PL/SQL AND SQL LOADER
commit;
dbms_output.put_line('commited');
end if;
dbms_output.put_line(s);
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
end;
*********************************************************************************
Pickup txt file from ftp server and put in u r systam and insert values into table directly.
***************************
create or replace procedure p_ftprecive
AS
--DECLARE
l_conn UTL_TCP.connection;
f utl_file.file_type;
s varchar2(200);
v1 number :=0;
v2 number :=0;
v3 number :=0;
v4 number :=0;
v5 number :=0;
v6 number :=0;
v7 number :=0;
v8 number :=0;
BEGIN
l_conn := ftp.login('ftp.drivehq.com', '21', 'siva711', 'ammulu');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
-- p_from_file is the fto server documents files folders
p_from_file => 'My Documents/empupdates.txt',
--siva directry is creatrd on oracle where u store u r file utl_fil directory
p_to_dir => 'siva',
p_to_file => 'empupdatesfromftpnew'||sysdate||'.txt');
ftp.logout(l_conn);
f := utl_file.fopen('siva','empupdatesfromftpnew'||sysdate||'.txt','R');
loop
utl_file.get_line(f,s);
v1 :=instr(s,'|',1,1);
v2 :=instr(s,'|',1,2);
v3 :=instr(s,'|',1,3);
v4 :=instr(s,'|',1,4);
v5 :=instr(s,'|',1,5);
ORACLE PL/SQL AND SQL LOADER
v6 :=instr(s,'|',1,6);
v7 :=instr(s,'|',1,7);
v8 :=instr(s,'|',1,8);
dbms_output.put_line(v1||v2||v3||v4||v5||v6);
if substr(s,1,1)='U' or substr(s,1,1)='I' then
insert into emp6
values(substr(s,3,4),substr(s,v2+1,v3-v2-1),substr(s,v3+1,v4-v3-1),substr(s,v4+1,v5-v4-1),substr(s,v5+1,v6-v5-1),,substr(s,v7+1,v8-v7-1),substr(s,v8+1));
end if;
dbms_output.put_line(s);
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
end;
********************************************************************************
%rowtype , %type examples:
create or replace procedure proc1 as
CURSOR c_emp IS
select * FROM Emp;
cursor c_salgrade(p_sal in number) is
select * from salgrade where p_sal>=losal and p_sal <= hisal;
vsal salgrade%rowtype;
vempno emp.empno%type;
vemp emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into vemp;
exit when c_emp%NOTFOUND;
vempno:=vemp.empno;
insert into emp6
values(vemp.empno,vemp.ename,vemp.job,vemp.mgr,vemp.hiredate,vemp.sal,vemp.comm,vemp.deptno);
dbms_output.put_line(vempno);
ORACLE PL/SQL AND SQL LOADER
open c_salgrade(p_sal=>vemp.sal);
fetch c_salgrade into vsal;
close c_salgrade;
--dbms_output.put_line('siva');
--dbms_output.put_line(vemprow);
dbms_output.put_line(vsal.grade);
end loop;
commit;
end;
*********************************************************************************
Default Parameter in cursors:
**************************
create or replace procedure proc3(p_dept in number default null) as
cursor c1 is
select * from emp where deptno=nvl(p_dept,deptno);
begin
for x in c1 loop
insert into emp6 values(x.empno,x.ename,x.job,x.mgr,x.hiredate,x.sal,x.comm,x.deptno);
dbms_output.put_line(x.deptno);
end loop;
commit;
end;
*********************************************************************************
TWO or more Default parameters:
***************************
create or replace procedure proc3(p_dept in number default null,p_depno in number default null)
as
cursor c1 is
select * from emp where deptno=nvl(p_dept,deptno);
cursor c2 is
select * from dept where deptno=nvl(p_depno,deptno);
begin
for x in c1 loop
insert into emp6 values(x.empno,x.ename,x.job,x.mgr,x.hiredate,x.sal,x.comm,x.deptno);
dbms_output.put_line('in emp'||x.deptno);
ORACLE PL/SQL AND SQL LOADER
end loop;
for y in c2 loop
dbms_output.put_line('on dept'||y.deptno);
end loop;
commit;
end;
We exec like:
exec proc3;
or
exec proc3(10);
or
exec proc3(null,20);
*********************************************************************************
The no of records are update we can do like this:
**************************
create or replace procedure proc4 as
begin
insert into emp6 select * from emp;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
if(SQL%ROWCOUNT>0) then
DBMS_OUTPUT.PUT_LINE('more than 10');
end if;
commit;
end;
or___________________
create or replace procedure proc4 as
begin
insert into emp6 select * from emp;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
if(SQL%ROWCOUNT>0) then
DBMS_OUTPUT.PUT_LINE('more than 10');
end if;
delete from emp6 where deptno=10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
update emp6 set comm=1111 where deptno=20;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
commit;
end;
****************************************************************
Home work mask and demask of Data;
ORACLE PL/SQL AND SQL LOADER
****************************************************************
Functions:
ex:
create or replace function fun1(p_empno in number)
return varchar2
IS
cursor c1 is
select ename from emp where empno=p_empno;
name varchar2(20) :=null;
begin
open c1;
fetch c1 into name;
if c1%notfound then
name := 'siva';
end if;
close c1;
RETURN name;
commit;
end;
compile function.
Calling function:
variable n varchar2(20);
exec :n:=fun1(721);
print n;
*********************************************************************************
From empno we get mgr name through function:
****************************************************************************
create or replace function fun1(p_empno in number)
return varchar2
IS
cursor c1 is
--select ename from emp where empno=p_empno;
select e1.ename from emp e, emp e1 where e.mgr=e1.empno and e.empno=p_empno;
name varchar2(20) :=null;
begin
open c1;
fetch c1 into name;
if c1%notfound then
name := 'siva';
ORACLE PL/SQL AND SQL LOADER
end if;
close c1;
RETURN name;
commit;
end;
Calling function:
select empno, ename, fun1(empno) from emp;
*********************************************************************************
Calling function in procedure
**************************************************************
create or replace procedure proc5 as
cursor c1 is
select * from emp;
mgrname varchar2(20):=null;- this we call wraper
begin
for x in c1 loop
mgrname:=fun1(x.mgr);
dbms_output.put_line(x.empno||'---'||'----'||x.ename||'---'||mgrname);
end loop;
commit;
end;
********************************************************************************
Creating package with that function and calling procedure:
*************************************************************************
create or replace package pack1 as
function fun2(p_mgr in number)
return varchar2;
procedure proc5;
end pack1;
/
CREATE OR REPLACE PACKAGE BODY pack1
IS
--------------------------------------------------------
function fun2(p_mgr in number)
return varchar2
IS
cursor c1 is
--select ename from emp where empno=p_mgr;
select e1.ename from emp e, emp e1 where e.mgr=e1.empno and e.empno=p_mgr;
name varchar2(20) :=null;
begin
ORACLE PL/SQL AND SQL LOADER
open c1;
fetch c1 into name;
if c1%notfound then
name := 'siva';
end if;
close c1;
RETURN name;
commit;
end;
----------------------------------------------------------------
procedure proc5 as
cursor c1 is
select * from emp;
mgrname varchar2(20):=null;
begin
for x in c1 loop
mgrname:=fun1(x.mgr);
dbms_output.put_line(x.empno||'---'||'----'||x.ename||'---'||mgrname);
end loop;
commit;
end;
END pack1;
/
______________________________________
*****************************************************************************
calling function:
variable n varchar2(20);
exec :n:=pack1.fun2(721);
print n;
*********************************************************************************
Above package the function giving as private function :
******************************************************************************
Function in Private class not decare in header only in body.
create or replace package pack2 as
procedure proc5;
end pack2;
/
CREATE OR REPLACE PACKAGE BODY pack2
IS
--------------------------------------------------------
function fun2(p_mgr in number)
ORACLE PL/SQL AND SQL LOADER
return varchar2
IS
cursor c1 is
select ename from emp where empno=p_mgr;
--select e1.ename from emp e, emp e1 where e.mgr=e1.empno and e.empno=p_mgr;
name varchar2(20) :=null;
begin
open c1;
fetch c1 into name;
if c1%notfound then
name := 'siva';
end if;
close c1;
RETURN name;
commit;
end;
----------------------------------------------------------------
procedure proc5 as
cursor c1 is
select * from emp;
mgrname varchar2(20):=null;
begin
for x in c1 loop
mgrname:=fun1(x.mgr);
dbms_output.put_line(x.empno||'---'||'----'||x.ename||'---'||mgrname);
end loop;
commit;
end;
END pack2;
/
----------------------
Execution:
alter package pack2 compile;
exec pack2.proc5;
*********************************************************************************
ORACLE PL/SQL AND SQL LOADER
Triggers:
Ref Explained with examples
1) examples
2) Good one
3) Good Examples
Audit table or shadow table:
Note: Triggers. we give audit table
Ex:
CREATE OR REPLACE TRIGGER
BEFORE or AFTER DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :OLD.sal);
dbms_output.put(' New salary: ' || :NEW.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
NOTE: Mutation problem interview.
NOTE: Triggering order
************************************************************
Trigger and insert the modified data with date in Audit Table:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHCIJHB
create or replace trigger trig1
BEFORE DELETE OR INSERT OR UPDATE ON emp6
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
insert into emp6_a(empno,oldsal,newsal,modifieddate) values
(:new.empno, :old.sal, :new.sal, sysdate);
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :OLD.sal);
dbms_output.put(' New salary: ' || :NEW.sal);
ORACLE PL/SQL AND SQL LOADER
dbms_output.put_line(' Difference ' || sal_diff);
END;
/
_____________________
Ans:
Trigger created.
SQL> alter trigger trig1 compile;
Trigger altered.
SQL> insert into emp6(empno,ename,sal) values(3333,'manish',4000);
1 row created.
SQL> insert into emp6(empno,ename,sal) values(2222,'ssss',5001);
Old salary: New salary: 5001 Difference
1 row created.
SQL> insert into emp6(empno,ename,sal,mgr) values(1111,'rere',1111,1234);
1 row created.
SQL> update emp6 set deptno=10 where empno=2222;
Old salary: 5001 New salary: 5001 Difference 0
1 row updated.
SQL> update emp6 set mgr=122 where empno=2222;
Old salary: 5001 New salary: 5001 Difference 0
1 row updated.
**********************************************************************
Mutation Problem:
Reference: What is mutation good example
2)one more ex for mutation
****************************************************************************
Data Masking and demasking:
Good Reference from oracle:
1) from oracle
2) with example and code
Referance:
ORACLE PL/SQL AND SQL LOADER
1) Defination and What is the use
2) faq on masking
******************************************************************************
01-AUG-09:
Discoverer:
Reference: From Oracle site good pdfs and vedios exapmles
1) Adhoc Report (Discover and OBI)
2) Picture perfect or formated Report.( they give layout of report) (This id report 6i, Xml
publisher, Bi pubilisher)
Discover start as 3i (support is finished), 4i, 9iAS, 10G(2009)(famous)
4 Ways to access didcoverer:
1) Discoverer Administrator: Admin control the user privileges.
2) Desktop: Tool to build reports. This one is used by Developer. Installable version.
3)Plus: Exactly same as desktop but this is a web version.
4)Viewer: He can view all reports . He can change values and sees output.
Data Base Discoverer
database
EUL(end user layer)(layer to connect with data
base.Every EUL with one database. WE need
min one eul to run discoverer.
Schema BA (business Area) One EUL can have any no
of BA. like AP, AR.
Table Folders
Columns Items
We have Joins, Hierarchy, Conditions, Sorting, Item Clauses.
Admin: Creating all things like EUL, BA, only do by ADMIN. Report we call discover work
book or discoverer report.
ORACLE PL/SQL AND SQL LOADER
Step 1: Connect as Discoverer Admin then give user name password and database orcl1 then it
will as to create one EUL then select YES and create eul for any user (give user name) and press
ok. Some times the user didn't have grants to create view it will give error as user doesn't have
privilege to create EUL.
That time GRANT CREATE VIEW TO SCOTT. Then click next it will create in EUL.
Step 2: Create BA.
Note: In plus we cannot save to desktop we can save only in database.
Item classes: Distinct values of items called loves.
3 types of folders:
simple folders- table to folder.
Complex folder- if a folder has more than one folders is called complex f.
Custom folder-- we can write our own sql.
We can create mutiple sheet.
Export options in file tab.
Note: If any changes has done in database that changes are not directly reflected to u r BA of the
folders. U need do refresh the folders then only u will get the change. Still if u didnot get u need
to login again and refresh.
- We open discoverer desktop. file create u will get 4 types of workbooks to create
1) table
2) Page detail report ( only u can give the group by columns )
- U can create condition at folder level in Admin so u can get filter out data there also.
-Check All tools Conditions, Totals, Parameter, Sort , Percentage, Caliculation in that functions
like decode.
--View --> inspector u will get query then u can run in sqlplus u can debug the sql staement.
- SUmmary Folders:- MV: This uses the performance improvement.
-- Grant select v_$parameter to Scott.
-- How to email discoverer report output.
--how to schedule the workbook.
-- Query Governor AT admin level and desktop level. --Tools --> options--> query Governor.
--Discoverer stand way we can not do. We have other way to do this. search in Google and do
that.
ORACLE PL/SQL AND SQL LOADER
-- Hierarchy how to create.-
--Item classes.
- parameters.
-- DDL we cannot do i discoverer.
--Ref cursor.
---sqlldr conventional path loading, direct path loading.
IMP Queries:
1)UPDATE IDTdetails AS i, Batch_table AS b SET i.Map_24hAccknowledgementtime =
b.Map_checkoutindate, i.Map_24hAcc = 'Y'
WHERE (Map_checkoutinflag In ('O','I','A') And i.Batch_no=b.bnwd);
2) Find out duplicates:
SELECT First(IDTdetails.DOH_SSN) AS [DOH_SSN Field], Count(IDTdetails.DOH_SSN) AS
NumberOfDups
FROM IDTdetails
GROUP BY IDTdetails.DOH_SSN
HAVING (((Count(IDTdetails.DOH_SSN))>1));
3)Duplicate in IDTdetails on DOH_SSN column.
SELECT COUNT(*), DOH_SSN
FROM IDTdetails
GROUP BY DOH_SSN
HAVING COUNT(*)>1;
4) Without mathing records from batch_table to IDTdetails table
SELECT Batch_table.*
FROM Batch_table LEFT JOIN IDTdetails ON Batch_table.bnwd = IDTdetails.Batch_no
WHERE (((IDTdetails.Batch_no) Is Null));
5) Without mathing records from IDTdetails to batch_table table
SELECT IDTdetails.*
FROM IDTdetails LEFT JOIN Batch_table ON IDTdetails.Batch_no = Batch_table.bnwd
WHERE (((Batch_table.bnwd) Is Null));
ex:
You are inserting records from TABLE B into TABLE A, and the
primary keyon both tables is COLUMN A and COLUMN B. Issue the
following query tofind thte duplicate records:
ORACLE PL/SQL AND SQL LOADER
SELECT * FROM TABLE_BWHERE (COLUMN_A||COLUMN_B) IN(SELECT
COLUMN_A||COLUMN_B FROM TABLE_A);
To find if there are any duplicate values in TABLE_B alone (if
there is aprimary key then there will be no duplicates) type the
following:
SELECT COUNT(*), COLUMN_A, COLUMN_BFROM TABLE_BGROUP BY
COLUMN_A, COLUMN_BHAVING COUNT(*)>1;
FTP FILE SCHEDULE:
http://www.deskshare.com/Resources/articles/afm_automate_FTPtasks.aspx
http://www.tbsoftinc.com/turboftp/schedule-file-transfer.htm
Report schedule:
Batch file:
dis51usr.exe /connect Scott/tiger@orcl1 /open "D:\siva\Discoverer\Pagedatailsreport.dis" /sheet
"Sheet_1" /export xls C:\test.xls /batch
febootimail -smtp smtp.gmail.com -starttls -user shiva.kumar711 -pass
from shiva.kumar711@gmail.com -to shiva.kumar711@gmail.com -attach C:\test.xls -subj my
report secheduled -body this is first report scheduleing using febooti software
How to send mail:
In command prompt:
febootimail -from shiva.kumar711@gmail.com -to shiva.kumar711@gmail.com -subj program -
body this is first report to send -attach
shiva.kumar711 -pass
Schedule the procedure useing sqlplus:
u can schedule your Jobs in Oracle using DBMS_JOB.
CREATE TABLE Test_Dbms_Job (N_No Number, Today_dt Date);
CREATE SEQUENCE TEST_DBMSJOB START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE PROCEDURE TEST_DBMS AS
BEGIN
ORACLE PL/SQL AND SQL LOADER
INSERT INTO TEST_DBMS_JOB (N_NO, TODAY_DT)
VALUES (TEST_DBMSJOB.NEXTVAL,TO_DATE(SYSDATE,'DD-MON-YY HH24:MI:SS'));
COMMIT;
END;
UNDER SQLPLUS
VARIABLE V_JOBFIRE NUMBER
BEGIN
DBMS_JOB.SUBMIT(:V_JOBFIRE,'TEST_DBMS;',SYSDATE, 'SYSDATE + (10/(24*60*60))');
END;
13. Query to find the nNth Highest salary
select min(sal) from (select distinct sal from emp order by sal desc) where rownum < n;
FAQS ON PLSQL:
http://www.oraclepassport.com/Oracle%20Interview%20Questions%20-%20General.html
Sucedule procedure:
http://forums.devx.com/showthread.php?t=17291
http://www-camden.rutgers.edu/HELP/Documentation/Oracle/server.815/a68001/dbms_job.htm
http://www.oracle-base.com/articles/10g/Scheduler10g.php
and we can do with batch file and windows schedule task.
172) DISPLAY 9th FROM THE EMP TABLE?
A) select ename, sal
from (
select ename,sal,dense_rank() over(order by sal desc) dr
from emp) where dr = 9;
171) DISPLAY TOP 3 SALARIES FROM EMP;
ANS) select ename,sal from (select ename, sal from emp order by sal desc nulls last) where
rownum <4
SQL> /
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
ORACLE PL/SQL AND SQL LOADER
FORD 3000
DATE: 9-AUG-09 :
REF CURSOR:
ex:create or replace procedure p_ref as
--declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
/
Query Governor:
ORACLE PL/SQL AND SQL LOADER
Discoverer:
Tools, privilages, select user and give privilege.
In privileges we can give ADMIN and desktop privileges
Security we can define on business area level only. TOOLS-> SECURITY.
ORACLE PL/SQL AND SQL LOADER
You can share a workbook to other user.
Generic user.
First create a workbook and share to scott(generic user) first next he will sahre to all the people.
then we delete the first workbook. File manage workbook delete.
Control on data layer. VPD: vertual private database.
HOME WORK VPD on EMP1. add scott then select * from emp u will get only scott record.
Discover for vpd in eul_$postlogin_trigger name for this function.
VPD is imp interview question.
Identifier: File , manage folder, properties. This idetifier didnot allow spaces.
DEV DB TEST DB
WB1 wb1
wb2 wb2
ORACLE PL/SQL AND SQL LOADER
Next created wb3, wb4.---------------------->
first here created wb3, wb4.
import wb3, wb4 gives error. There we over
write the identifier.
Migrate WBS from one instance to other instance:
DEV TEST
file EXPORT File IMPORT
BA->f1 BA-->f1
-->f2 import only folder f2
2) BA1-->f1, f2 import BA1
3) only item class export.
Note: The workbook saves on 2 extentions .eex, .dis.
Fantrap Detection: This is when Cartesian join it kills automatically. This one is in tools--
>options--> advanced --> fantrap detection check that. It will delete Cartesian joins.
Note: We can send a parameter to the existing function what u created. This is tools,
caliculations functions select the functions the parameter select from left items deptno parameter
paste. u can use like this.
Note: We cannot send a parameter value to admin layer.
Note: If u share the WB to so many peoples u can not give save to database option to that users.
Other wise u will get so many WBS in database based on each user. For this in DISCOVERER
ADMIN tools privilages , select user , uncheck Save to database option.
Home work : SQLLDR: Conventional path loading, Direct path loading.
Creating a SEQUENCE:
Syntax: CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
ORACLE PL/SQL AND SQL LOADER
Performance problems if u do cache it in ram 1-20 seq nos. IF u do cache power loss u will lose
sequence nos.
ex:create sequence emp7_seq start with 1 increment by 3 cache 100;
Next value we can see:select StudentNumSeq.NEXTVAL from DUAL;
Current value: select StudentNumSeq.CURRVAL from DUAL;
Updating Table emp7 row_id column with sequence :
create or replace procedure p_emp7seq
AS
CURSOR c_emp IS
select * FROM Emp;
BEGIN
for x_emp in c_emp loop
update emp7 set row_id = emp7_seq.nextval where empno=x_emp.empno;
end loop;
commit;
end;
/
Alter Sequence
ALTER SEQUENCE
Change Increment
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;
ALTER SEQUENCE
Change Max Value
ALTER SEQUENCE seq_maxval MAXVALUE 10;
ALTER SEQUENCE
Change Cycle
ALTER SEQUENCE seq_cycle NOCYCLE;
ALTER SEQUENCE
Change Cache NOCACHE
ALTER SEQUENCE seq_cache NOCACHE;
ALTER SEQUENCE
Change Order
ALTER SEQUENCE seq_order NOORDER;
Drop Sequence
DROP SEQUENCE
Drop Sequence
DROP SEQUENCE seq_cache;
OUT parameter in Variable:
Ex:
ORACLE PL/SQL AND SQL LOADER
create or replace procedure p_outpara(p_empno in number, p_ename out varchar2, p_sal out
number)
IS
BEGIN
select ename,sal into p_ename,p_sal from emp where empno=p_empno;
commit;
end;
/
calling output parameterifs:
create or replace procedure p_callout
AS
CURSOR c_emp IS
select * FROM Emp;
p_ename varchar2(20);
p_sal number;
BEGIN
for x_emp in c_emp loop
p_outpara(x_emp.empno,p_ename,p_sal);
dbms_output.put_line(p_ename||'-'||p_sal);
end loop;
commit;
end;
/
EX: 2:
create or replace procedure p_outpara1(p_empno in number, p_ename out varchar2,
p_deptno out number)
IS
BEGIN
select ename,deptno into p_ename,p_deptno from emp where empno=p_empno;
commit;
end;
/
create or replace procedure p_callout1
AS
CURSOR c_emp IS
select * FROM Emp;
p_ename varchar2(20);
v1 number:=0;
ORACLE PL/SQL AND SQL LOADER
v2 number:=0;
v3 number:=0;
p_deptno number;
BEGIN
for x_emp in c_emp loop
p_outpara1(x_emp.empno,p_ename,p_deptno);
if p_deptno = 10 then
v1 := v1+1;
elsif p_deptno = 20 then
v2:=v2+1;
else
v3:= v3+1;
end if;
end loop;
dbms_output.put_line('dept no 10 has'||v1||' employees');
dbms_output.put_line('dept no 20 has'||v2||' employees');
dbms_output.put_line('dept no 30 has'||v3||' employees');
commit;
end;
/
IN OUT PARAMETER:
if we are giving same sal and getting same sal then we use in out p_sal;
Home work: VPD , EUL$ POSTLOIGNTRIGGER.
http://oraclebizint.wordpress.com/2007/07/23/bi-publisher-10132-and-vpd/
********************************************************************************
SELECT
REGEXP_COUNT ('Oracle PL/SQL By Example Updated for Oracle 11g',
'ora', 1, 'i')
FROM dual;
REGEXP_COUNT('ORACLEPL/
SQLBYEXAMPLEUPDATEDFORORACLE11G','ORA',1,'I')
The REGEXP_COUNT function returns how many times the search pattern 'ora' appears in
the source string 'Oracle PL/SQL...' 1 indicates the position of the source string where the
search begins, and 'i' indicates case-insensitive matching.
FOR EXAMPLE
SELECT
REGEXP_INSTR ('Oracle PL/SQL By Example Updated for Oracle 11g',
'((ora)(cle))', 1, 2, 0, 'i')
FROM dual;
REGEXP_INSTR('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G',...)
ORACLE PL/SQL AND SQL LOADER
------------------------------------------------------------
The REGEXP_INSTR function returns the position of the first character in the source string
'Oracle PL/SQL…' corresponding to the second occurrence of the first subexpression 'ora'
in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search
begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the
position returned corresponds to the position of the first character where the match occurs, and
'i' indicates case-insensitive matching and REGEXP_SUBSTR.
Note: v_start_time := DBMS_UTILITY.GET_TIME;
8/13/09
AOL: Application object library:
sysadmin Application Developer
- Concurrent programs
- Register printers
- Users
- Response set
ETC
Left side:
Responsebilities --> functions --> more than 2 function combined MENU
AP SUPPLIER -> Create Suplier------
AP INVOICES -> CREATE INVOICES
AP CHECKS ->CHECK PROCESING
NOTE : SOCKS LAW, IT AUDIT
IN APPS ALL FORMS BASED ON VIEW.
Note: Never write code on view. Write on table.
Concurent programs :
Help->daignostics->
AOL or FND or SYSADMIN these all starts with view name.
Views terminated with _V or
Tables terminated with _ALL, _B, _F, _TL
TL means translation table.
FNDCONCURETPROGRAM: || MAke a tranlationtable _TL
ORACLE PL/SQL AND SQL LOADER
1212 1212 US
1212 CN
1212 FA
IN this senario we write query like that ---------------------------where ------- and LANGUAGE=
USERENV('LANG');
EX: Programs, Concurrent
SELECT
T.USER_CONCURRENT_PROGRAM_NAME
, B.CONCURRENT_PROGRAM_NAME
, decode(a.application_short_name,'SQLGL','General Ledger','AR','ACCOUNT RECIVEBLES')
Application
, T.DESCRIPTION
, B.EXECUTABLE_ID
, B.ENABLE_TRACE
, e.executable_name
,e.execution_method_code
,b.execution_options
,b.request_set_flag
,b.increment_proc
,b.mls_executable_app_id
,b.run_alone_flag
,b.srs_flag
,b.restart
,b.nls_compliant
,b.output_file_type
,b.save_output_flag
,b.print_flag
,b.minimum_width
,b.minimum_length
,b.printer_name
,b.required_style
FROM FND_CONCURRENT_PROGRAMS_TL T
, FND_CONCURRENT_PROGRAMS B
,FND_APPLICATION A
,FND_EXECUTABLES e
,FND_EXECUTABLES_TL el
,FND_APPLICATION_TL AL
WHERE B.APPLICATION_ID = T.APPLICATION_ID
AND B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID
AND T.LANGUAGE = USERENV('LANG')
AND T.USER_CONCURRENT_PROGRAM_NAME LIKE '%siva%'
AND a.application_id= b.application_id
and a.application_id = al.application_id
ORACLE PL/SQL AND SQL LOADER
and al.language=USERENV('LANG')
and b.executable_application_id = e.application_id
and b.executable_id = e.executable_id
and e.application_id= el.application_id
and e.executable_id= el.executable_id
and el.language=USERENV('LANG');
CREATING TABLE:
CREATE TABLE XXSIVA AS SELECT
T.USER_CONCURRENT_PROGRAM_NAME UCPN
, B.CONCURRENT_PROGRAM_NAME CPN
, decode(a.application_short_name,'SQLGL','General Ledger','AR','ACCOUNT RECIVEBLES')
APP
, T.DESCRIPTION DESCR
, B.EXECUTABLE_ID EI
, B.ENABLE_TRACE ET
, e.executable_name EN
,e.execution_method_code EMC
,b.execution_options EO
,b.request_set_flag RSF
,b.increment_proc IP
,b.mls_executable_app_id MEAI
,b.run_alone_flag RAF
,b.srs_flag SF
,b.restart RESTART1
,b.nls_compliant NC
,b.output_file_type OFT
,b.save_output_flag SOF
,b.print_flag PF
,b.minimum_width MW
,b.minimum_length ML
,b.printer_name PN
,b.required_style RS
FROM FND_CONCURRENT_PROGRAMS_TL T
, FND_CONCURRENT_PROGRAMS B
,FND_APPLICATION A
,FND_EXECUTABLES e
,FND_EXECUTABLES_TL el
,FND_APPLICATION_TL AL
WHERE B.APPLICATION_ID = T.APPLICATION_ID
AND B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID
AND T.LANGUAGE = USERENV('LANG')
AND T.USER_CONCURRENT_PROGRAM_NAME LIKE '%siva%'
AND a.application_id= b.application_id
and a.application_id = al.application_id
ORACLE PL/SQL AND SQL LOADER
and al.language=USERENV('LANG')
and b.executable_application_id = e.application_id
and b.executable_id = e.executable_id
and e.application_id= el.application_id
and e.executable_id= el.executable_id
and el.language=USERENV('LANG');
INSERT THE DATE TO TABLE XXSIVA PROCEDURE:
CREATE OR REPLACE PROCEDURE XXSIVA_P
AS
CURSOR C_XXSIVA IS
SELECT
T.USER_CONCURRENT_PROGRAM_NAME UCPN
, B.CONCURRENT_PROGRAM_NAME CPN
, decode(a.application_short_name,'SQLGL','General Ledger','AR','ACCOUNT RECIVEBLES')
APP
, T.DESCRIPTION DESCR
, B.EXECUTABLE_ID EID
, B.ENABLE_TRACE ET
, e.executable_name EN
,e.execution_method_code EMC
,b.execution_options EO
,b.request_set_flag RSF
,b.increment_proc IP
,b.mls_executable_app_id MEAI
,b.run_alone_flag RAF
,b.srs_flag SF
,b.restart RESTART1
,b.nls_compliant NC
,b.output_file_type OFT
,b.save_output_flag SOF
,b.print_flag PF
,b.minimum_width MW
,b.minimum_length ML
,b.printer_name PN
,b.required_style RS
FROM FND_CONCURRENT_PROGRAMS_TL T
, FND_CONCURRENT_PROGRAMS B
,FND_APPLICATION A
,FND_EXECUTABLES e
,FND_EXECUTABLES_TL el
,FND_APPLICATION_TL AL
ORACLE PL/SQL AND SQL LOADER
WHERE B.APPLICATION_ID = T.APPLICATION_ID
AND B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID
AND T.LANGUAGE = USERENV('LANG')
AND T.USER_CONCURRENT_PROGRAM_NAME LIKE '%siva%'
AND a.application_id= b.application_id
and a.application_id = al.application_id
and al.language=USERENV('LANG')
and b.executable_application_id = e.application_id
and b.executable_id = e.executable_id
and e.application_id= el.application_id
and e.executable_id= el.executable_id
and el.language=USERENV('LANG');
BEGIN
FOR R_XXSIVA IN C_XXSIVA LOOP
INSERT INTO XXSIVA VALUES(R_XXSIVA.UCPN
,R_XXSIVA.CPN
,R_XXSIVA.APP
,R_XXSIVA.DESCR
,R_XXSIVA.EID
,R_XXSIVA.ET
,R_XXSIVA.EN
,R_XXSIVA.EMC
,R_XXSIVA.EO
,R_XXSIVA.RSF
,R_XXSIVA.IP
,R_XXSIVA.MEAI
,R_XXSIVA.RAF
,R_XXSIVA.SF
,R_XXSIVA.RESTART1
,R_XXSIVA.NC
,R_XXSIVA.OFT
,R_XXSIVA.SOF
,R_XXSIVA.PF
,R_XXSIVA.MW
,R_XXSIVA.ML
,R_XXSIVA.PN
,R_XXSIVA.RS);
END LOOP;
COMMIT;
END;
ORACLE PL/SQL AND SQL LOADER
VPD POLICY:
create role cust_role;
grant select on scott.emp1 to cust_role;
grant select, update on orders to cust_role;
grant cust_role to scott;
CREATE OR REPLACE FUNCTION
emp_sec
(scott IN varchar2, emp1 IN varchar2)
RETURN VARCHAR2 AS
BEGIN
RETURN
'ename='siva''
||
sys_context(
'userenv',
'session_user')
||
'ename='ravi'';
END emp_sec;
/
************************************************************************************************
CREATE OR REPLACE PACKAGE usage_security AS
FUNCTION Usage_Profile (p_obj_schema IN VARCHAR2
,p_obj_name IN VARCHAR2) RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY usage_security AS
FUNCTION Usage_Profile ( p_obj_schema IN VARCHAR2
,p_obj_name IN VARCHAR2) RETURN VARCHAR2
IS
D_predicate VARCHAR2 (2000);
BEGIN
ORACLE PL/SQL AND SQL LOADER
-- D_predicate := 'ENAME = NVL(SYS_CONTEXT(''USERENV'', ''SESSION_USER''),0)';
D_predicate := 'UPPER(ENAME) = NVL(upper(SYS_CONTEXT(''USERENV'',
''SESSION_USER'')),''siva'')';
RETURN D_predicate;
EXCEPTION
WHEN OTHERS THEN
RETURN '1 = 2'; -- Return so that no rows are visible for secuity reason
END Usage_Profile;
END usage_security;
Begin
DBMS_RLS.ADD_POLICY ('SCOTT', 'EMP1', 'emp_policy', 'SCOTT',
'usage_security.Usage_Profile', 'select');
end;
---------
Begin
DBMS_RLS.DROP_POLICY ('scott'
,'emp1'
,'emp_policy'
);
end;
--grant execute on dbms_rls to scott;
*******************************************************************************************
CREATE OR REPLACE FUNCTION Usage_Profile2 ( p_obj_schema IN VARCHAR2
,p_obj_name IN VARCHAR2) RETURN NUMBER
IS
D_predicate VARCHAR2 (2000);
P_EMPNO NUMBER;
BEGIN
D_predicate := 'ENAME = NVL(SYS_CONTEXT(''USERENV'', ''SESSION_USER''),0)';
--D_predicate := 'UPPER(ENAME) = NVL(upper(SYS_CONTEXT(''USERENV'',
''SESSION_USER'')),0)';
ORACLE PL/SQL AND SQL LOADER
SELECT mgr INTO P_EMPNO FROM EMP WHERE MGR IN(SELECT EMPNO FROM
EMP WHERE ENAME = D_predicate);
--SELECT JOB INTO D_predicate FROM EMP WHERE ENAME='ENAME =
NVL(SYS_CONTEXT(''USERENV'', ''SESSION_USER''),0)';
--DBMS_OUTPUT.PUT_LINE(D_predicate);
--Select JOB INTO D_predicate FROM EMP1 WHERE ENAME =
SYS_CONTEXT('USERENV', 'session_user');
RETURN P_EMPNO;
EXCEPTION
WHEN OTHERS THEN
RETURN '1 = 2'; -- Return so that no rows are visible for secuity reason
END Usage_Profile2;
______________________________________________________________
Begin
DBMS_RLS.ADD_POLICY ('SCOTT', 'EMP1', 'emp_policy1', 'SCOTT',
'Usage_Profile2', 'select');
end;
-----
Begin
DBMS_RLS.DROP_POLICY ('scott'
,'emp1'
,'emp_policy1'
);
end;
*********************************************************************************
CREATE OR REPLACE FUNCTION USERACCESS_FUNCTION( P_OBJ_SCHEMA IN
VARCHAR2
,P_OBJ_NAME IN VARCHAR2) RETURN VARCHAR2 AS
D_PREDICATE VARCHAR2 (200);
BEGIN
/* TODO implementation required */
IF NVL (SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER''), "SCOTT") THEN
D_PREDICATE := 'MGR =7566';
ELSIF NVL(SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER''), "KING") THEN
D_PREDICATE := ' '; --SELECT ALL
ELSIF NVL(SYS_CONTEXT( ''USERNV'', ''CLIENT_IDENTIFIER''), "LENIN")THEN
ORACLE PL/SQL AND SQL LOADER
D_PREDICATE:='MGR=1';
ELSE D_PREDICATE:= '1 = 2'; --BLOCKS
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN D_PREDICATE;
END USERACCESS_FUNCTION;
*********************************************************************************
SELECT DISTINCT(mgr) FROM EMP WHERE MGR IN(SELECT EMPNO FROM EMP
WHERE ENAME = 'KING');
_________________________________________________________________________________
CREATE OR REPLACE FUNCTION Usage_Profile3 ( p_obj_schema IN VARCHAR2
,p_obj_name IN VARCHAR2) RETURN varchar2
IS
D_predicate VARCHAR2 (2000):=null;
D_predicate1 VARCHAR2 (2000):=null;
P_EMPNO NUMBER;
MGRNO NUMBER:=0;
BEGIN
D_predicate1 := 'NVL(upper(SYS_CONTEXT(''USERENV'', ''SESSION_USER'')),''BLAKE'')';
if D_predicate ='KING' then
D_predicate:='1=1';
else
select mno into D_predicate from manager where mname=D_predicate1;
if D_predicate=null then
D_predicate:='ename = D_predicate1';
else
D_predicate:='mgr = D_predicate or ename = D_predicate1';
ORACLE PL/SQL AND SQL LOADER
end if;
end if;
return D_predicate;
END Usage_Profile3;
________________________
Begin
DBMS_RLS.ADD_POLICY ('SCOTT', 'EMP2', 'emp_policy2', 'SCOTT',
'Usage_Profile3', 'select');
end;
Begin
DBMS_RLS.DROP_POLICY ('scott'
,'emp2'
,'emp_policy2'
);
end;
Email Validation pl/sql:
CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name
,'.');
l_at_pos := instr(l_user_name
,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length))
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name
,l_at_pos)
ORACLE PL/SQL AND SQL LOADER
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;
How do I create a 8 digit password for FND_USER record?
FUNCTION get_random_password RETURN VARCHAR2 IS
BEGIN
RETURN lower(dbms_random.STRING('X',8));
END get_random_password;
We want users to be enforced to alter their password every 6months, how to ?
Use parameter x_password_lifespan_days
fnd_user_pkg.createuser
(
x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => v_password
,x_description => p_person_description
,x_password_lifespan_days => 180
,x_employee_id => p_person_id
,x_email_address => p_email_address
);
The random password that is generated might have repeating characters, which will error
if Profile "Signon Password Hard To Guess" is Set to Yes?
You can temporarily set the profile to N in the session during which concurrent program runs.
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS' ,val => 'N');
Should I use Workflow to inform users?
You might as well use SMTP. Be careful that on Dev environment business users do not
receive Emails.
This can be ensured by checking return of select instance_name from v$instance to be PRD
Database.
You can use procedure send_html_email in the Code Sample in link provided in the article.
Can I run the source code as is?
The source code provided is for allocating a responsibility named "XX HR Employee Self
ORACLE PL/SQL AND SQL LOADER
Service".I have removed all the client specific bits from the code. Hence you will need to alter
to make this to be of any use. The idea is to merely provide you a guideline for API Usage.
Note: ALTER SESSION SET CURRENT_SCHEMA =APPS
FAQS ON PLSQL:
1) http://www.jusungyang.com/ORACLEfolder/PLSQL.html
2) http://www.orafaq.com/wiki/Interview_Questions
3) http://dev.fyicenter.com/faq/oracle/oracle_introduction_pl_sql.php
8/29/2009
****************************
If u r connecting to other server host name add the path
C:\WINDOWS\system32\drivers\etc\hosts
*********************
Deleting the concurrent program using one more Concurent Program in that
caling API 's fnd_program.delete_program,fnd_program.delete_executable:
CREATE OR REPLACE PROCEDURE P_CONCURENT_API_TEST(errbuf OUT
VARCHAR2,retcode OUT NUMBER)
AS
CURSOR C1 IS
SELECT FCP.CONCURRENT_PROGRAM_NAME FCPN,
FA.APPLICATION_SHORT_NAME FASN
FROM
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION FA
WHERE FCP.CREATED_BY = 1011923
AND FA.APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_NAME = 'WHAT PROGRAM U R CREATING
THE NAME BECAUSE THAT WONT BE DELETED OTHER WISE U R PROGRAM
ALSO WHAT U R CALLING ALSO DELETED' ;
CNAME VARCHAR2(40) := NULL;
COUNT1 NUMBER := 0;
BEGIN
COUNT1 :=0;
FOR X IN C1 LOOP
ORACLE PL/SQL AND SQL LOADER
CNAME := X.FCPN;
IF (COUNT1>5) THEN
EXIT;
ELSE
COUNT1 := COUNT1+1;
fnd_program.delete_program(X.FCPN,X.FASN);
fnd_program.delete_executable(X.FCPN,X.FASN);
fnd_file.put_line(FND_FILE.LOG,X.FCPN);
fnd_file.put_line(FND_FILE.OUTPUT,X.FCPN);
--fnd_file.put_line(1,X.FCPN);
END IF;
END LOOP
COMMIT;
END;
/
*****************************************************************************
Writing a data file to output file procedure :
CREATE OR REPLACE PROCEDURE P_CONCURENT_FILE (errbuf OUT
VARCHAR2,retcode OUT NUMBER)
AS
CURSOR C1 IS
SELECT POH.PO_HEADER_ID PH, POH.TYPE_LOOKUP_CODE PT,POH.SEGMENT1
PN,POH.VENDOR_ID PV,POH.ORG_ID PO,POH.AUTHORIZATION_STATUS PS
,POL.PO_LINE_ID PL ,POL.CLOSED_CODE PC, RS.QUANTITY_SHIPPED RQS,
RS.QUANTITY_RECEIVED RQR, RS.SHIPMENT_LINE_STATUS_CODE
RS,MS.SEGMENT1 MS
FROM
MTL_SYSTEM_ITEMS_B MS,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
RCV_SHIPMENT_LINES RS
WHERE --POH.SEGMENT1 = '5363'
ROWNUM <20
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND RS.PO_HEADER_ID= POL.PO_HEADER_ID
AND RS.PO_LINE_ID = POL.PO_LINE_ID
AND MS.INVENTORY_ITEM_ID = POL.ITEM_ID
AND MS.ORGANIZATION_ID = POL.ORG_ID;
ORACLE PL/SQL AND SQL LOADER
CNAME VARCHAR2(40) := NULL;
COUNT1 NUMBER := 0;
BEGIN
COUNT1 :=0;
FOR X IN C1 LOOP
--DBMS_OUTPUT.PUT_LINE(X.FCP.CONCURRENT_PROGRAM_NAME);
fnd_file.put_line(FND_FILE.LOG,X.PH||'|'||X.PT||'|'||X.PN||'|'||X.PV||'|'||X.PO||'|'||X.PS||'|'||X.PL||'|'||X.PC||'|'||X.RQS||'|'||fnd_file.put_line(FND_FILE.OUTPUT,X.PH||'|'||X.PT||'|'||X.PN||'|'||X.PV||'|'||X.PO||'|'||X.PS||'|'||X.PL||'|'||X.PC||'|'||X.--fnd_file.put_line(1,X.CONCURRENT_PROGRAM_NAME);
END LOOP
COMMIT;
END;
/
-----------------------------
Save the out put file like .dat
61|BLANKET|501|1|204|APPROVED|61|OPEN|145|145|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|167|167|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|9|9|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|15|15|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|65|65|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|50|50|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|88|88|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|75|75|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|67|67|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|112|112|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|100|100|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|120|120|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|230|230|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|9|9|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|175|175|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|150|150|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|180|180|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|88|88|FULLY RECEIVED|f30000
61|BLANKET|501|1|204|APPROVED|61|OPEN|149|149|FULLY RECEIVED|f30000
MAKE A TABLE:
create table TESTSIVA (
PH NUMBER
,PT VARCHAR2(40)
ORACLE PL/SQL AND SQL LOADER
,PN NUMBER
,PV NUMBER
,PO NUMBER
,PS VARCHAR2(40)
,PL NUMBER
,PC VARCHAR2(40)
,RQS NUMBER
,RQR NUMBER
,RS VARCHAR2(20)
,MS VARCHAR2(25)
);
Write control file :
LOAD DATA
INFILE '/opt/erp/appl/apps/apps_st/appl/fnd/12.0.0/bin/CUSTOMER.dat'
INTO TABLE TESTSIVA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
PH
,PT
,PN
,PV
,PO
,PS
,PL
,PC
,RQS
,RQR
,RS
,MS
)
-------------------------------
Importnat: Put the data file and control file in the location:
/opt/erp/appl/apps/apps_st/appl/fnd/12.0.0/bin/XXCUSTOMERCTL.ctl
goto Application developer make Executable and Program and put file for request group
General Ledger.
Go to GL and Run the Request u will see the log file message 19 rows are successfully inserted.
Table TESTSIVA:
19 Rows successfully loaded.
ORACLE PL/SQL AND SQL LOADER
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
****************************************************************
NOTE: select * from v$parameter WHERE NAME LIKE 'utl%'; U WILL GET THE PATH
*******************************************************************************
WRITING A FILE IN APPS SERVER USING UTL FILE
create or replace procedure p_write_oraps
AS
CURSOR c1 IS
SELECT POH.PO_HEADER_ID PH, POH.TYPE_LOOKUP_CODE PT,POH.SEGMENT1
PN,POH.VENDOR_ID PV,POH.ORG_ID PO,POH.AUTHORIZATION_STATUS PS
,POL.PO_LINE_ID PL ,POL.CLOSED_CODE PC, RS.QUANTITY_SHIPPED RQS,
RS.QUANTITY_RECEIVED RQR, RS.SHIPMENT_LINE_STATUS_CODE
RS,MS.SEGMENT1 MS
FROM
MTL_SYSTEM_ITEMS_B MS,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
RCV_SHIPMENT_LINES RS
WHERE --POH.SEGMENT1 = '5363'
ROWNUM <20
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND RS.PO_HEADER_ID= POL.PO_HEADER_ID
AND RS.PO_LINE_ID = POL.PO_LINE_ID
AND MS.INVENTORY_ITEM_ID = POL.ITEM_ID
AND MS.ORGANIZATION_ID = POL.ORG_ID;
msg varchar2(500) :=NULL;
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(150):='/opt/erp/database/db/tech_st/10.2.0/appsutil/
outbound/VISDB_swan';
v_salg number :=0;
BEGIN
ORACLE PL/SQL AND SQL LOADER
sFileName := 'CUSTOMER.dat';
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x in c1 loop
msg :=
X.PH||'|'||X.PT||'|'||X.PN||'|'||X.PV||'|'||X.PO||'|'||X.PS||'|'||X.PL||'|'||X.PC||'|'||X.RQS||'|'||X.RQR||'|'||X.RS||'|'||X.MS||'|';
UTL_FILE.PUT_LINE(uFileHndl,msg);
end loop;
-- Close the output file
UTL_FILE.FCLOSE(uFileHndl);
commit;
end;
/
************************************************************************
moving file from one file to other location:
For copying the file..
Utl_File.Fcopy ( src_location => p_file_location
,src_filename => p_file_name
, dest_location => p_arch_location,
dest_filename => p_arch_file );
For removing the file..:
UTL_FILE.Fremove(p_file_location,lc_datafile_name);
*********************************************************************************************************
Write a file in APPS and Move to other folder:
create or replace procedure p_write_oraps
AS
CURSOR c1 IS
SELECT POH.PO_HEADER_ID PH, POH.TYPE_LOOKUP_CODE PT,POH.SEGMENT1
PN,POH.VENDOR_ID PV,POH.ORG_ID PO,POH.AUTHORIZATION_STATUS PS
,POL.PO_LINE_ID PL ,POL.CLOSED_CODE PC, RS.QUANTITY_SHIPPED RQS,
RS.QUANTITY_RECEIVED RQR, RS.SHIPMENT_LINE_STATUS_CODE
RS,MS.SEGMENT1 MS
FROM
MTL_SYSTEM_ITEMS_B MS,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
RCV_SHIPMENT_LINES RS
WHERE --POH.SEGMENT1 = '5363'
ROWNUM <20
ORACLE PL/SQL AND SQL LOADER
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND RS.PO_HEADER_ID= POL.PO_HEADER_ID
AND RS.PO_LINE_ID = POL.PO_LINE_ID
AND MS.INVENTORY_ITEM_ID = POL.ITEM_ID
AND MS.ORGANIZATION_ID = POL.ORG_ID;
msg varchar2(500) :=NULL;
uFileHndl UTL_FILE.FILE_TYPE;
sFileName VARCHAR2(30);
-- The directory you want file to be generated
gs_TestDir CONSTANT VARCHAR2(150):='/opt/erp/database/db/tech_st/10.2.0/appsutil/
outbound/VISDB_swan';
to_testdir CONSTANT VARCHAR2(150):= '/opt/erp/appl/apps/apps_st/appl/fnd/12.0.0/bin';
BEGIN
sFileName := 'CUSTOMER.dat';
--UTL_FILE.Fremove(gs_TestDir,sFileName);
uFileHndl := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
for x in c1 loop
msg :=
X.PH||'|'||X.PT||'|'||X.PN||'|'||X.PV||'|'||X.PO||'|'||X.PS||'|'||X.PL||'|'||X.PC||'|'||X.RQS||'|'||X.RQR||'|'||X.RS||'|'||X.MS||'|';
UTL_FILE.PUT_LINE(uFileHndl,msg);
end loop;
commit;
Utl_File.fcopy (gs_TestDir,
sFileName ,
gs_TestDir,
'CUSTOMER1.dat');
UTL_FILE.FCLOSE(uFileHndl);
--UTL_FILE.FCOPY(GET_DIR,sFileName,TO_DIR,sFileName);
-- Close the E(uFileHndl);
commit;
end;
/
********************************************************************************
create or replace procedure p_ftpsend
AS
--CREATE OR REPLACE DIRECTORY sivaftp AS 'siva';
--DECLARE
l_conn UTL_TCP.connection;
ORACLE PL/SQL AND SQL LOADER
BEGIN
l_conn := ftp.login('ftp.drivehq.com', '21', 'siva711', 'ammulu');
ftp.ascii(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => '/opt/erp/database/db/tech_st/10.2.0/appsutil/outbound/VISDB_swan',
--p_from_dir => 'siva',
p_from_file => 'CUSTOMER.dat',
p_to_file => 'Customer.txt');
ftp.logout(l_conn);
END;
*********************************************************************************
Bulk Collections:
Note: The OLTP and Data Ware house is OLTP is Normalized form it is very good for
insertion. The online tansaction information stroed in OLTP systems.
OLAP[data ware house] in this data is denormalized form. That is we can retrive the data fast.
Daily every day night the OLAP system will get the data of that day.
Bulk collection: If u r inserting the bulk of data in a table. If u take the cursor and do this. The
context between fetch and insert takes long time to complete the task.
If u go to bulk collection we fetch the 20,000 records at a time and next we will do insertion so
the program will finish less time optimiztion.
*********************************************************************************
ORACLE PL/SQL AND SQL LOADER
No comments:
Post a Comment