Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:  password.sql
rem Purpose:   Simple password encryption package to demonstrate how
rem            values can be encrypted and decrypted using Oracle's
rem            DBMS Obfuscation Toolkit
rem Note:      Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Date:      18-Mar-2003
rem Author:    Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------


CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;

password encryption package

rem -----------------------------------------------------------------------
rem Filename:  plstable.sql
rem Purpose:   Example: how to populate a PL/SQL Table from a cursor
rem Date:      09-Apr-1999
rem Author:    Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

declare
  -- Declare the PL/SQL table
  type deptarr is table of dept%rowtype
       index by binary_integer;
  d_arr deptarr;

  -- Declare cursor
  type d_cur is ref cursor return dept%rowtype;
  c1 d_cur;

  i number := 1;
begin
  -- Populate the PL/SQL table from the cursor
  open c1 for select * from dept;
  loop
    exit when c1%NOTFOUND;
    fetch c1 into d_arr(i);
    i := i+1;
  end loop;
  close c1;

  -- Display the entire PL/SQL table on screen
  for i in 1..d_arr.last loop
    dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
    dbms_output.put_line('DNAME  : '||d_arr(i).dname  );
    dbms_output.put_line('LOC    : '||d_arr(i).loc    );
    dbms_output.put_line('---------------------------');
  end loop;
end;
/

populate a PL/SQL Table from a cursor

rem -----------------------------------------------------------------------
rem Filename:   profiler.sql
rem Purpose:    Profile PL/SQL code to get run-time statistics.
rem             Shows execution time for each PL/SQL line. This data can
rem             be used to improve performance.
rem Date:       02-Mar-2004
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql

-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
  v_dummy CHAR;
BEGIN
   FOR i IN 1..100 LOOP
      SELECT dummy INTO v_dummy FROM dual;
   END LOOP;
END;
/
SHOW ERRORS

-- Do the profilling and print the report...
set line 5000 serveroutput on size 1000000
DECLARE
  v_run NUMBER;
BEGIN
  DBMS_PROFILER.START_PROFILER('test','test1',v_run);
  proc1;
  DBMS_PROFILER.STOP_PROFILER;
  DBMS_PROFILER.ROLLUP_RUN(v_run);
  PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/

Profile PL/SQL code to get run-time statistics

rem -----------------------------------------------------------------------
rem Filename:   bulkbind.sql
rem Purpose:    Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes:      Bulk operations on ROWTYPE only work from and above.
rem Date:       12-Feb-2004
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000

DECLARE
  CURSOR emp_cur IS SELECT * FROM EMP;

  TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab emp_tab_t;            -- In-memory table

  rows NATURAL        := 10000;   -- Number of rows to process at a time
  i    BINARY_INTEGER := 0;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Bulk collect data into memory table - X rows at a time
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
    EXIT WHEN emp_tab.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

    FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
      -- Manipumate data in the memory table...
      dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
    END LOOP;

    -- Bulk bind of data in memory table...
    FORALL i in emp_tab.FIRST..emp_tab.LAST
      INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

  END LOOP;
  CLOSE emp_cur;
END;
/

BULK COLLECT and BULK BIND

rem -----------------------------------------------------------------------
rem Filename:  plsloop.sql
rem Purpose:   Example: UPDATE/DELETE in a loop and commit very X records
rem            Handy for huge tables that cause rollback segment problems
rem            DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date:      09-Apr-1999; Updated: 25-Nov-2004
rem Author:    Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

declare
  i number := 0;
  cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
  for c1 in s1 loop
      update tab1 set col1 = 'value2'
             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every X records
      if i > 10000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/

-- Note: More advanced users can use the mod() function to commit every N rows. 
--       No counter variable required:
--
-- if mod(i, 10000) 
--    commit;
--    dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
--  end if;
--

UPDATE/DELETE in a LOOP

rem -----------------------------------------------------------------------
rem Purpose:    Delete duplicate values from a table
rem Date:       04-Mar-2005
rem Notes:      Verify that the correct rows are deleted before you COMMIT!
rem Author:     Dharmendra Srivastava,Associate, 
rem             MindTree Consulting Pvt Ltd. India
rem -----------------------------------------------------------------------

DELETE FROM my_table 
 WHERE ROWID NOT IN (SELECT MIN(ROWID) 
                       FROM my_table 
                      GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- To delete the duplicate values:
-- 
-- DELETE FROM emp 
--  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id); 
-- 
-- COMMIT;
--

Delete duplicate values from a table

rem -----------------------------------------------------------------------
rem Filename:     conv2lob.sql
rem Purpose:      Convert LONG datatypes to LOBs (Large Objects)
rem Dependancies: Oracle 8.1.x or higher
rem Date:         17-Sep-2001
rem Author:       Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create table old_long_table(c1 number, c2 long);
insert into  old_long_table values (1, 'LONG data to convert to CLOB');

create table new_lob_table(c1 number, c2 clob);

-- Use TO_LOB function to convert LONG to LOB...
insert into  new_lob_table
       select c1, to_lob(c2) from old_long_table;

-- Note: the same procdure can be used to convert LONG RAW datatypes to BLOBs.

Convert LONG datatypes to LOBs (Large Objects)

rem -----------------------------------------------------------------------
rem Filename:   varray.sql
rem Purpose:    Demontrate VARRAY (variable array in one database column) 
rem             collection types
rem Date:       12-Aug-2001
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from   varray_table t1, TABLE(t1.col1) t2
/

-- Use PL/SQL to access the varray...
set serveroutput on
declare
  v_vcarray vcarray;
begin
  for c1 in (select * from varray_table) loop
      dbms_output.put_line('Row fetched...');
      FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
          dbms_output.put_line('...property fetched: '|| c1.col1(i));
      END LOOP;
  end loop;
end;
/

-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;

VARRAY

rem -----------------------------------------------------------------------
rem Filename:   objopt.sql
rem Purpose:    Demonstrate Oracle database types and object tables
rem Date:       12-Feb-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

drop type employee_typ;

create type employee_typ as object (
        empno NUMBER,
        emp_name varchar2(30),
        hiredate date,
        member function days_at_company return NUMBER,
        pragma restrict_references(days_at_company, WNDS)
)
/

create type body employee_tye is
begin
        member function days_at_company return number is
        begin
                return (SYSDATE-hiredate);
        end;
end;
/
show errors

drop type department_typ;

create type department_typ as object (
        deptno NUMBER(5),
        manager ref employee_typ
)
/

select * from user_types
where predefined = 'NO';

-- Create a object table
create table emp1 as employee_typ;

create table employee (emp_no NUMBER, emp employee_typ);

insert into employee values (1, employee_typ(1, 'Frank Naude', SYSDATE));

commit;

select * from employee;

select x.emp.emp_name from employee x;

Oracle database types and object tables

rem -----------------------------------------------------------------------
rem Filename:   countall.sql
rem Purpose:    Count the number of rows for ALL tables in the current schema
rem Date:       30-Apr-1999
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off echo off feed off trimspool on head off pages 0

spool countall.tmp
select 'SELECT count(*), '''||table_name||''' from '||table_name||';'
from   user_tables
/
spool off

set termout on
@@countall.tmp

set head on feed on

Count the number of rows for ALL tables in the current schema

rem -----------------------------------------------------------------------
rem Filename:   encode.sql
rem Purpose:    Demonstrate simple encoding and decoding of secret messages 
rem             This method can be extended to create simple password
rem             encryption
rem Date:       15-Feb-2001
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

SELECT TRANSLATE(
        'HELLO WORLD',                  -- Message to encode
        'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
        '1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
        '85@@%._%*@4',                  -- Message to decode
        '1234567890!@#$%^&*()-=_+;,.',
        'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/

Encoding and Decoding of Secret Messages

rem -----------------------------------------------------------------------
rem Filename:   spellout.sql
rem Purpose:    This script will spell out numbers to words (handy for 
rem        cheque printing). Example '10' --> Ten
rem Date:       12-Sep-2000
rem Author:     Anonymous
rem -----------------------------------------------------------------------

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
       decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/

Spell out Numbers to Words

rem -----------------------------------------------------------------------
rem Filename:   leapyear.sql
rem Purpose:    Check if a year is a leap year
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select year,
       decode( mod(year, 4), 0,
          decode( mod(year, 400), 0, 'Leap Year',
             decode( mod(year, 100), 0, 'Not a Leap Year', 'Leap Year')
          ), 'Not a Leap Year'
       ) as leap_year_indicator
from   my_table
/

Check if a year is a leap year

rem -----------------------------------------------------------------------
rem Filename:   appinfo.sql
rem Purpose:    Example of how to pass application info through to Oracle RDBMS
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- The following code tells the database what the application is up to: 

begin
   dbms_application_info.set_client_info('BANCS application info');
   dbms_application_info.set_module('BANCS XYZ module', 'BANCS action name');
end;
/

-- Retrieve application info from the database:

select module, action, client_info
from   sys.v_$session where audsid = USERENV('SESSIONID')
/

select sql_text
from   sys.v_$sqlarea
where  module = 'BANCS XYZ module'
and  action = 'BANCS action name'
/

Example of how to pass application info through to Oracle RDBMS

rem -----------------------------------------------------------------------
rem Filename:   comments.sql
rem Purpose:    Display table and column comments for the current schema
rem             Handy for getting to know the database schema
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
set null 'No Comments'

tti 'Table Comments'
col comments format a29 wrap word

select * from user_tab_comments;

tti 'Column Comments'
col comments format a18 wrap word
break on table_name skip 1
select * from user_col_comments;
clear break

set null ''
set pages 23

Display table and column comments for the current schema

rem -----------------------------------------------------------------------

rem Filename:   default.SQL

rem Purpose:    Example script to demonstrate DEFAULT column VALUES

rem Date:       25-Apr-2001

rem Author:     Frank Naude, Oracle faq

rem -----------------------------------------------------------------------


DROP TABLE x ;

CREATE TABLE x (
a CHAR,
b NUMBER     DEFAULT 99999,
c DATE,
d VARCHAR2(6))
/


ALTER TABLE x
MODIFY( c DATE     DEFAULT SYSDATE)
/


INSERT INTO x
        (a,
         d)
VALUES     ('a',
         'qwerty')
/


SELECT *
FROM   x
/

--

-- Expected output:

--

--  A          B C           D

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

--  a      99999 25-APR-2001 qwerty

Example script to demonstrate DEFAULT column values

rem execute this code in the Scott SCHEMA
SELECT   LEVEL,
       Max(sal)
FROM     emp
WHERE    LEVEL = &nth
CONNECT BY PRIOR sal > sal
GROUP BY LEVEL ;

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

rem execute this code in the HR SCHEMA
SELECT   LEVEL,
       Max(salary)
FROM     employees
WHERE    LEVEL = &nth
CONNECT BY PRIOR salary > salary
GROUP BY LEVEL ;

SELECT sal ename 
FROM   emp
WHERE  sal IN (SELECT Min(sal)
FROM   (SELECT   DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM = &num);  

SELECT salary FROM employees e1
WHERE  &n IN (SELECT Count(DISTINCT (e2.salary))
FROM   employees e2
WHERE  e2.salary > e1.salary);  

SELECT * FROM   (
SELECT ROWNUM rn , sal FROM   (
SELECT * FROM emp
ORDER BY sal DESC))
WHERE  rn = &x;  

SELECT salary FROM   (
SELECT salary FROM employees
ORDER BY salary)
WHERE  ROWNUM < &n;  

REM TO Find SECOND MAX VALUE FROM A TABLE
SELECT Max(salary) FROM employees WHERE salary IN 
(SELECT Max(salary) FROM employees);  

SELECT sal FROM   (
SELECT DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM < &&n
MINUS
SELECT sal FROM   (
SELECT DISTINCT sal FROM emp
ORDER BY sal DESC)
WHERE  ROWNUM < (&n - 1);  

Select the Nth highest - lowest value from a table

rem -----------------------------------------------------------------------

rem Filename:   whoami.SQL

rem Purpose:    Reports information about your current database context

rem Author:     Frank Naude, Oracle faq

rem -----------------------------------------------------------------------

SET termout off

STORE set store rep

SET head off

SET pause off

SET termout ON


SELECT 'User: '
     ||USER
     ||' on database '
     ||GLOBAL_NAME,
     '  (term='
     ||Userenv('TERMINAL')
     ||', audsid='
     ||Userenv('SESSIONID')
     ||')' AS mycontext
FROM   GLOBAL_NAME;

@STORE

SET termout ON

Reports information about your current database context

rem -----------------------------------------------------------------------

rem File name:   ver.SQL

rem Purpose:    Show database version with options installed

rem             (handy for your HELP/ABOUT menu)

rem Date:       12-Nov-1999

rem Author:     Frank Naude, Oracle faq

rem -----------------------------------------------------------------------

SET head off feed off pages 0 serveroutput ON

col banner format a72 wrap


SELECT banner
FROM   sys.v_$version;


SELECT '   With the '
     ||parameter
     ||' option'
FROM   sys.v_$option
WHERE  VALUE = 'TRUE';


SELECT '   The '
     ||parameter
     ||' option is not installed'
FROM   sys.v_$option
WHERE  VALUE <> 'TRUE';


BEGIN

dbms_output.Put_line('Port String: '

                     ||dbms_utility.port_string);

END;

/


SET head on feed ON

Show database version with options installed

Look Inside >>  May/June 2008

Oracle Magazine (May/June 2008)

Look Inside >>  July/August 2008

Oracle Magazine (July/August 2008)

Look Inside >>  September/October 2008

Oracle Magazine (September/October 2008)

rem -----------------------------------------------------------------------

rem UPDATED version

rem Filename:   matrix.SQL

rem Purpose:    Example of a CROSS MATRIX report implemented USING

rem             standard SQL.

rem Date:       12-Feb-2000

rem Author:     Frank Naude, Oracle faq

rem

rem Updated By  Mahesh Pednekar. (bunty609@hotmail.com)

rem Description Removed the Main query because the sub query itself

rem             will full fill the requirement.

rem -----------------------------------------------------------------------


SELECT   job,

        Sum(Decode(deptno,10,sal)) dept10,

        Sum(Decode(deptno,20,sal)) dept20,

        Sum(Decode(deptno,30,sal)) dept30,

        Sum(Decode(deptno,40,sal)) dept40

FROM     scott.emp

GROUP BY job

/

-- Sample output:

--

-- JOB           DEPT10     DEPT20     DEPT30     DEPT40

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

-- ANALYST                    6000

-- CLERK           1300       1900        950

-- MANAGER         2450       2975       2850

-- PRESIDENT       5000

-- SALESMAN                              5600

--

CROSS MATRIX report

Download the Source code

for the famous book

( SQL Hacks ).... just Click Here

Copyright © 2007 O'Reilly Media, Inc. All
rights reserved.

SQL Hacks Source code

To Generate Excel file for any table , make like this example at SQL*PLUS :-

SET MARKUP HTML ON
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL current_employees.xls

SELECT first_name
||' '
||last_name AS "Employee Name",
salary,
hire_date,
NVL(TO_CHAR(commission_pct),'No Commission')
AS "Commission", job_id
FROM employees;

SPOOL OFF
EXIT;

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

Oracle to Excel via Sqlplus

Look Inside >>  November/December 2008

Oracle Magazine (November/December 2008)