======================================================
First create pl/sql package.procedure we'll call it apps_plsql.demo:
NOTE:
errbuf and retcode are required parameters that will be passed from/to
concurrent manager - you must have them.
- - - - - - - - - - - - - - CUT - - - - - - - - - - - - -
CREATE OR REPLACE PACKAGE apps_plsql AS
procedure demo(errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2);
END apps_plsql;
/
CREATE OR REPLACE PACKAGE BODY apps_plsql AS
procedure demo(errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2)
is
begin
--
-- If you want to write some output call:
--
FND_FILE.put_line(FND_FILE.output,'Starting processing:');
--
-- NOTE:
-- to write to log use FND_FILE.log instead of FND_FILE.output
--
--
FND_FILE.put_line(FND_FILE.output,'Done!');
commit;
-- Return 0 for successful completion.
errbuf := '';
retcode := '0';
exception
when others then
errbuf := sqlerrm;
retcode := '2';
end demo;
END apps_plsql;
/
Script for creating EMP,DEPT table and Insert sample data into the table
========================================================================
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
--
-- Create a primary key as this, or a user-defined unique replication key, is
-- necessary for replication.
--
alter table emp add constraint pk_emp primary key (empno)
/
alter table dept add constraint pk_dept primary key (deptno)
/
==================================================
Send Mail
=========
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'test@yahoo.com';
mailhost VARCHAR2(30) := '11.128.211.74'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/
begin
send_mail(msg_to=>'satk@x123.com',
msg_subject => 'Hello from Oracle',
msg_text => 'This is the body of the message'
);
end;
Example Case Statement
======================
DECLARE
A VARCHAR2(240);
BEGIN
CASE
when &A IN ('SYSTEM1','XXX') then
DBMS_OUTPUT.PUT_LINE('FIRST');
WHEN &A ='SYSTEM2' THEN
DBMS_OUTPUT.PUT_LINE('SECOND');
ELSE
DBMS_OUTPUT.PUT_LINE('THIRD');
END case;
END;
Before running sql trace
========================
alter session set timed_statistics=true
alter session set max_dump_file_size=unlimited
GLOBAL TEMPORARY TABLE
======================
drop table x
/
create global temporary table x (a date)
on commit delete rows -- Delete rows after commit
-- on commit preserve rows -- Delete rows after exit session
/
select table_name, temporary, duration
from user_tables
where table_name = 'X'
/
insert into x values (sysdate);
select * from x;
commit;
-- Inserted rows are missing after commit
select * from x;
To find TOP 300 Vendors by dollars spend
========================================
SELECT company,vendor_number,vendor_name,amt_paid,amt_paid_rank
FROM
(SELECT gcc.segment1 company,pv.segment1 vendor_number,pv.vendor_name,
sum(amount_paid) amt_paid,DENSE_RANK() OVER (Partition by gcc.segment1 ORDER BY sum(amount_paid) DESC) amt_paid_rank FROM apps.po_vendors pv,apps.ap_invoices_all aia,apps.gl_code_combinations gcc
WHERE pv.vendor_id=aia.vendor_id
AND gcc.code_combination_id=aia.accts_pay_code_combination_id
AND (pv.vendor_type_lookup_code <> 'GOVT' or pv.vendor_type_lookup_code is null)
AND aia.creation_date > sysdate-366
AND gcc.segment1 in ('100','101')
AND amount_paid is not null
GROUP BY pv.segment1,pv.vendor_name,gcc.segment1) where amt_paid_rank <=300
To Rename a column for a table
==============================
update col$ set name = '&NEWNAME' where name ='&COLUMNAME and obj#=(select obj# from obj$ from obj$ where name ='&TABLENAME' and owner#=(select user# from user$ where name='&USERNAME'))
Hint: Use it from SYS
Query to display dublicate rows
===============================
Select * from dept x where
rowid not in
(select min(rowid) from dept where deptno=x.deptno)
Query to get column without specifying the column name
======================================================
Select &n,&q from emp where deptno=10;
Add a column C1 to the existing T1
==================================
Alter table T1 add (C1 date)
Change C1 column type to varchar2
=================================
Alter table T1 modify(C1 varchar2)
Add a UNIQUE constraint to C1
=============================
Alter table T1 add constraint aa unique(C1)
Tips for efficient SQL
======================
*do not perform calculation on an indexed column,it negates the index
*use UNION instead of OR
*use NOT EXISTS instead of NOT IN or HAVING
*avoid using LIKE,it will negate inxexes and cause a full table scan
*do not mix datatypes,use numbers to numeric datatypes and same for alphanumeric datatypes
*driving table is last table in FROM clause(rule-based optimizer)
*avoid subqueries,use a JOIN
*use DECODE to reduce the number of table accesses
*disable an index by concatenating a null or 0 to the column name(name||" or salary+0)
*use a full table scan is returning more than 20% of the rows in the table
*use table aliases with column names
No comments:
Post a Comment