ORACLE-FORU - ORACLE DBMS_PROFILER

DBMS_PROFILER

The DBMS_PROFILER package was introduced in Oracle 8i to allow developers to profile the run-time behaviour of PL/SQL code, 

Making it easier to identify performance bottlenecks which can then be investigated more closely.

1. The first step is to install the DBMS_PROFILER package (profload.sql) 
2. create the tables to hold the profiler results (proftab.sql). 
3. The profiler tables can be created in your local schema, or in a shared schema, as shown below.

-- Install the DBMS_PROFILER package.

CONNECT sys/password@service AS SYSDBA

@$ORACLE_HOME/rdbms/admin/profload.sql

-- Install tables in a shared schema.

CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO profiler;

GRANT CREATE TABLE, CREATE SEQUENCE TO profiler;

CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;

CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;

CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;

CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

CONNECT profiler/profiler@service

@$ORACLE_HOME/rdbms/admin/proftab.sql

GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Make sure the user that needs to run the profiler has the correct privileges on the DBMS_PROFILER package.

GRANT EXECUTE ON dbms_profiler TO test;

Next we connect to the test user and create a dummy procedure to profile.

CONN test/test@service

CREATE OR REPLACE PROCEDURE do_something (p_times  IN  NUMBER) AS

  l_dummy  NUMBER;

BEGIN

  FOR i IN 1 .. p_times LOOP

    SELECT l_dummy + 1

    INTO   l_dummy

    FROM   dual;

  END LOOP;

END;

/

Next we start the profiler, run our procedure and stop the profiler.

DECLARE

  l_result  BINARY_INTEGER;

BEGIN

  l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);

  do_something(p_times => 100);

  l_result := DBMS_PROFILER.stop_profiler;

END;

/

With the profile complete we can analyze the data to see which bits of the process took the most time, with all times presented in nanoseconds. First we check out which runs we have.

SET LINESIZE 200

SET TRIMOUT ON

COLUMN runid FORMAT 99999

COLUMN run_comment FORMAT A50

SELECT runid,

       run_date,

       run_comment,

       run_total_time

FROM   plsql_profiler_runs

ORDER BY runid;

RUNID RUN_DATE  RUN_COMMENT                        RUN_TOTAL_TIME

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

    1 21-AUG-03 do_something: 21-AUG-2003 14:51:54      131072000

We then use the appropriate RUNID value in the following query.

COLUMN runid FORMAT 99999

COLUMN unit_number FORMAT 99999

COLUMN unit_type FORMAT A20

COLUMN unit_owner FORMAT A20

SELECT u.runid,

       u.unit_number,

       u.unit_type,

       u.unit_owner,

       u.unit_name,

       d.line#,

       d.total_occur,

       d.total_time,

       d.min_time,

       d.max_time

FROM   plsql_profiler_units u

       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number

WHERE  u.runid = 1

ORDER BY u.unit_number, d.line#;

RUNID UNIT_NU UNIT_TYPE       UNIT_OWNER  UNIT_NAME    LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

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

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      4           1          0        0        0

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      5           1          0        0        0

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      6           1          0        0        0

    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     4         101          0        0        0

    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     5         100   17408000        0  2048000

5 rows selected.

The results of this query show that line 4 of the DO_SOMETHING procedure ran 101 times but took very little time, while line 5 ran 100 times and took proportionately more time. 

We can check the line numbers of the source using the following query.

SELECT line || ' : ' || text

FROM   all_source

WHERE  owner = 'MY_SCHEMA'

AND    type  = 'PROCEDURE'

AND    name  = 'DO_SOMETHING';

LINE||':'||TEXT

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

1 : PROCEDURE do_something (p_times  IN  NUMBER) AS

2 :   l_dummy  NUMBER;

3 : BEGIN

4 :   FOR i IN 1 .. p_times LOOP

5 :     SELECT l_dummy + 1

6 :     INTO   l_dummy

7 :     FROM   dual;

8 :   END LOOP;

9 : END;

As expected, the query took proportionately more time than the procedural loop. Assuming this were a real procedure we could use the DBMS_TRACE or the SQL trace facilities to investigate the problem area further.

Read More

ORACLE-FORU - SQL%BULK_ROWCOUNT

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute.

Lets see example you will clearly understand.

create one table.

CREATE TABLE bulk_rowcount_test AS

SELECT *

FROM   all_users;

Below example will clearly understand how it will work through sql%bulk_rowcount.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_array_tab IS TABLE OF VARCHAR2(30);

  l_array t_array_tab := t_array_tab('SCOTT', 'SYS',

                                     'SYSTEM', 'DBSNMP', 'BANANA'); 

BEGIN

  -- Perform bulk delete operation.

  FORALL i IN l_array.first .. l_array.last 

    DELETE FROM bulk_rowcount_test

    WHERE username = l_array(i);

  -- Report affected rows.

  FOR i IN l_array.first .. l_array.last LOOP

    DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||

      ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));

  END LOOP;

END;

/

Element: SCOTT           Rows affected: 1

Element: SYS             Rows affected: 1

Element: SYSTEM          Rows affected: 1

Element: DBSNMP          Rows affected: 1

Element: BANANA          Rows affected: 0

PL/SQL procedure successfully completed.

SQL>

So we can see that no rows were deleted when we performed a delete for the username "BANANA".

Read More

ORACLE-FORU - Oracle FORALL Function in Bulk Collection.

FORALL in Bulk Collection.

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

lets try to understand with example.

create one table to test how forall function will work and performance of the function we can see.

CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (

  CONSTRAINT forall_test_uk UNIQUE (code));

Below code compares the time taken to insert 10,000 rows using regular FOR..LOOP and a bulk bind.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time bulk inserts.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Inserts: 305
Bulk Inserts  : 14

PL/SQL procedure successfully completed.


SQL>


By seeing output we can able to see how much performance will increase FORALL function.

Below example uses the ROW keyword, when doing a comparison of normal and bulk updates.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_id_tab  t_id_tab          := t_id_tab();
  l_tab     t_forall_test_tab := t_forall_test_tab ();
  l_start   NUMBER;
  l_size    NUMBER            := 10000;
BEGIN
  -- Populate collections.
  FOR i IN 1 .. l_size LOOP
    l_id_tab.extend;
    l_tab.extend;

    l_id_tab(l_id_tab.last)       := i;
    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  -- Time regular updates.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    UPDATE forall_test
    SET    ROW = l_tab(i)
    WHERE  id  = l_tab(i).id;
  END LOOP;
  
  DBMS_OUTPUT.put_line('Normal Updates : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;

  -- Time bulk updates.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    ROW = l_tab(i)
    WHERE  id  = l_id_tab(i);
  
  DBMS_OUTPUT.put_line('Bulk Updates   : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Updates : 235
Bulk Updates   : 20

PL/SQL procedure successfully completed.


SQL>

we can see the output shows the performance improvements you can expect to see when using bulk binds.

Read More

Bulk Collect In Oracle

Bulk Collect In Oracle

Definition of Bulk Collect:- Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this create the following table.

below example will help you to understand how it will improve performance.

create a table to understand how bulk collection is working.

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id

FROM   all_objects;

Below code will understand how normal code and bulk code will improve performance.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();

  l_start  NUMBER;

BEGIN

  -- Time a regular population.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *

                  FROM   bulk_collect_test)

  LOOP

    l_tab.extend;

    l_tab(l_tab.last) := cur_rec;

  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || 

                       (DBMS_UTILITY.get_time - l_start));

  

  -- Time bulk population.  

  l_start := DBMS_UTILITY.get_time;

  SELECT *

  BULK COLLECT INTO l_tab

  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || 

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Regular (42578 rows): 66

Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

we can see how much regular time has taken and bulk time has taken, it will improve more performance.Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem

we can use by another method by using Limit option in bulk collections.This gives you the benefits of bulk binds, without hogging all the server memory. The following code shows how to chunk through the data in a large table.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS

    SELECT *

    FROM bulk_collect_test;

BEGIN

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 10000;

    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.

    DBMS_OUTPUT.put_line(l_tab.count || ' rows');

  END LOOP;

  CLOSE c_data;

END;

/

10000 rows

10000 rows

10000 rows

10000 rows

2578 rows

PL/SQL procedure successfully completed.

SQL>

So we can see that with a LIMIT 10000 we were able to break the data into chunks of 10,000 rows, reducing the memory footprint of our application

from 10g on words PL/SQL compiler converts cursor FOR LOOPs into BULK COLLECTs with an array size of 100. 

Below example will give you clear idea how cursor for loop and bulk collection will work .

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab;

  CURSOR c_data IS

    SELECT *

    FROM   bulk_collect_test;

  l_start  NUMBER;

BEGIN

  -- Time a regular cursor for loop.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *

                  FROM   bulk_collect_test)

  LOOP

    NULL;

  END LOOP;

  DBMS_OUTPUT.put_line('Regular  : ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 10.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 10;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 10 : ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 100.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 100;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 100: ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 1000.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 1000;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 1000: ' || 

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Regular  : 18

LIMIT 10 : 80

LIMIT 100: 15

LIMIT 1000: 10

PL/SQL procedure successfully completed.

SQL> 

You can see from this example the performance of a regular FOR LOOP is comparable to a BULK COLLECT using an array size of 100.

Read More

ORACLE-FORU - Difference between inner join and equi join and natural join

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.

Inner Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;

Inner Join Result

tblEmp.Name

tblEmp.DeptID

tblDept.Name

tblDept.DeptID

Ram

1

HR

1

Raju

2

IT

2

Soya

2

IT

2

Sam

3

ADMIN

3

In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;
--Using Clause is not supported by SQL Server
--Oracle and MySQL Query
SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)

Equi Join Result

tblEmp.Name

tblEmp.DeptID

tblDept.Name

tblDept.DeptID

Ram

1

HR

1

Raju

2

IT

2

Soya

2

IT

2

Sam

3

ADMIN

3

Note

1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
2. Equi join only have equality (=) operator in the join condition.
3. Equi join can be an Inner join, Left Outer join, Right Outer join
4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

--Run in Oracle and MySQL 
SELECT * FROM tblEmp NATURAL JOIN tblDept

Natural Join Result

DeptID

tblEmp.Name

tblDept.Name

1

Ram

HR

2

Raju

IT

2

Soya

IT

3

Sam

ADMIN

In the above join result we have only one column "DeptID" for each pair of equally named columns.

Note

1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.
2. Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

Read More

ORACLE-FORU - PL/SQL code for SPACE AFTER EACH LETTER

pl/sql program to insert a space after each  letter in a  given string.

 Procedure:

declare
     str varchar(10):='&str';
     len number(2);
      i number(3):=1;
      newstr varchar(20);
 begin
    len:=length(str);
     while i<=len
     loop
        newstr:=newstr||substr(str,i,1)||' ';
         i:=i+1;
     end loop;
      dbms_output.put_line(newstr);
    end;
     /

Execution:

SQL> @e:\plsql\9b.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';

s v c e t

PL/SQL procedure successfully completed.


Conclusion:
a pl/sql program is successfully executed for to insert a space after each letter.
INSERTING A ROW USING PL/SQL

Read More

ORACLE-FORU - PL/SQL code for LETTER BY LETTER

pl/sql program to print a string in a letter by letter format.

Procedure:

  declare
      str varchar(10):='&str';
      len number(2);
      i number(3):=1;
  begin
      len:=length(str);
      while i<=len
      loop
          Dbms_output.put_line(substr(str,i,1));
           i :=i+1;
     end loop;
   end;
  /

Output:

SQL> @e:\plsql\9a.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';
s
v
c
e
t

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for printing a string letter by letter.

Read More

ORACLE-FORU - PL/SQL code to check given number is EVEN OR ODD

pl/sql program to find given number is even or odd.

Procedure:

Declare
     n number(4):=&n;
 Begin
     if mod(n,2)=0
    then
       dbms_output.put_line(n||' even number');
    else
      dbms_output.put_line(n||' odd number');
   end if;
end;
/

Execution:

SQL> @e:\plsql\even.sql
Enter value for n: 5
old   2:  n number(4):=&n;
new   2:  n number(4):=5;

5 odd number

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for finding even or odd.

Read More

ORACLE-FORU - PL/SQL code for FIBINOCCI SERIES

pl/sql program to generate fibinocci series.

Procedure:
   declare
      a number(3):=1;
      b number(3):=1;
      c number(3);
      n number(3):=&n;
begin
  Dbms_output.put_line('the fibinocci series is:');
  while a<=n
    loop
        dbms_output.put_line(a);
        c:=a+b;
        a:=b;
        b:=c;
     end loop;
  end;
 /

Execution:
SQL> @e:\plsql\fibi.sql
Enter value for n: 13
old   5:   n number(3):=&n;
new   5:   n number(3):=13;
the fibinocci series is:
1
1
2
3
5
8
13
PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for to generate fibinocci series.

Read More

ORACLE-FORU - PL/SQL code for Reverse Of A Number

pl/sql program to generate reverse for given number.

Procedure:

declare
     n number(4):=&n;
     s number(4):=0;
     r number(4);
 begin
     while n>0
     loop
      r:=mod(n,10);
      s:=(s*10)+r;
      n:=trunc(n/10);
     end loop;
   dbms_output.put_line(‘the reverse number is:’);
   dbms_output.put_line(s);
end;
/

Execution:

SQL> @e:\plsql\rev.sql
Enter value for n: 457
old   2:   n number(4):=&n;
new   2:   n number(4):=457;

the reverse number is:
754

PL/SQL procedure successfully completed.


Conclusion:
       a pl/sql program is successfully executed to generate reverse number for given number.

Read More

ORACLE-FORU - PL/SQL code for FACTORIAL OF A NUMBER

pl/sql program to finding factorial of given number.

Procedure:

 declare
       i number(4):=1;
       n number(4):=&n;
       f number(4):=1;
 begin
    for i in 1..n
    loop
       f:=f*i;
     end loop;
    Dbms_output.put_line('the factorial of '||n||' is:'||f);
    end;
   /

Output:

SQL> @e:\plsql\fact.sql
Enter value for n: 5
old   3:   n number(4):=&n;
new   3:   n number(4):=5;
the factorial of 5 is:120

PL/SQL procedure successfully completed.


Conclusion:
       a pl/sql program is successfully executed for finding factorial of a given number

Read More

ORACLE-FORU - PL/SQL code for PRIME NUMBER

pl/sql program to check weather given number is Prime or not.

Procedure:

  declare
       num number;
       i number:=1;
       c number:=0;
  begin
        num:=&num;
       for i in 1..num
       loop
          if((mod(num,i))=0)
           then
              c:=c+1;
         end if;
      end loop;
     if(c>2)
     then
         dbms_output.put_line(num||' not a prime');
     else
        dbms_output.put_line(num||' is prime');
     end if;
  end;
   /

Execution:

SQL> @e:\plsql\prime.sql
Enter value for num: 5
old   6:  num:=&num;
new   6:  num:=5;
5 is prime


Conclusion:
       A pl/sql program is successfully executed to check the given number is prime or not.

Read More

ORACLE-FORU - PL/SQL code for MULTIPLES OF 5

To write a pl/sql program for finding Multiples of 5.

Procedure:

declare
     i number(3):=5;
     n number(3):=&n;
Begin
     Dbms_output.put_line('the multiples of 5 are:');
     while i<=n
       loop
          dbms_output.put_line(i);
          i:=i+5;
     end loop;
  end;
  /

Output:

SQL> @e:\sqlpl\2.sql
Enter value for n: 20
old   3:  n number(3):=&n;
new   3:  n number(3):=20;
the multiplus of 5 are:
5
10
15                                                                                      
20

PL/SQL procedure successfully completed.

Conclusion:
        A pl/sql program is successfully executed for finding Multiples of 5.

Read More

ORACLE-FORU - PL/SQL Code for Sum of two number

To write a pl/sql program to find sum of two numbers.
Procedure:

Declare

i number(3);
j number(3);
k number(3);

           Begin
                     i:=&i;
                     j:=&j;
                     k:=i+j;

                     Dbms_output.put_line('the sum of '||i||','||j||'is:'||k);

           end;
Execution:
SQL> @e:\sqlpl\add.sql
Enter value for i: 4
old 6: i:=&i;
new 6: i:=4;
Enter value for j: 4
old 7: j:=&j;
new 7: j:=4;
the sum of 4,4 is:8

PL/SQL procedure successfully completed.
Conclusion:

A pl/sql program is successfully executed for finding sum of two numbers.

Read More

ORACLE-FORU - Oracle all Important Functions with Examples

Oracle Functions

                                             
ASCII                                

Returns the ASCII value (NUMBER) that represents the specified character.
 ASCII(SINGLE_STRING)

 ASCII('t') - 116

 ASCII('T') - 84
 ASCII('T2') - 84


ASCIISTR                            

Converts a string in any character set to an ASCII string using the database                                                   character set.
  ASCIISTR(STRING)
  ASCIISTR('A B C Ä Ê') - 'A B C \00C4 \00CA'
 ASCIISTR('A B C Õ Ø') - 'A B C \00D5 \00D8'
 ASCIISTR('A B C Ä Ê Í Õ Ø') - 'A B C \00C4 \00CA \00CD \00D5\00D8'

CHR                                    

Returns the character based on the NUMBER code.
This is opposite of the ascii function.
 CHR(NUMBER_CODE)

 CHR(116) - 't'
 CHR(84) - 'T'

CONCAT                           

Allows you to concatenate two strings together.
CONCAT(STRING1, STRING2)
CONCAT('Tech on',' the Net') - 'Tech on the Net'
CONCAT('a', 'b') - 'ab'

Concat ||                             

|| operator allows you to concatenate 2 or more strings together.
 STRING1 || STRING2 || STRINGn
 'Tech on' || ' the Net' - 'Tech on the Net'
 'a' || 'b' || 'c' || 'd' - 'abcd'


CONVERT                           

Converts a string from one character set to another
CONVERT(STRING1 , CHAR_SET_TO , [CHAR_SET_FROM ] )
CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') - 'A B  C D E A E I ? ?'

INITCAP                             

 Returns first character in each word to uppercase and the rest to lowercase.
 INITCAP(STRING)
 INITCAP('tech on the net') - 'Tech On The Net'
 INITCAP('GEORGE BURNS') - 'George Burns'

INSTR                                  

 It returns the location of a substring in a string.
 INSTR(STRING1,STRING2 [,STRING_POSITION  [,nth_APPEARANCE]])
 string1 is the string to search.
  string2 is the substring to search for in string1.
  start_position is the position in string1 where the search will start. This                                                           argument is optional. If omitted, it defaults to 1. The first position in the string                                                 is 1. If the start_position is negative, the function counts backstart_position                                                   number of characters from the end of string1 and then searches towards the                                                   beginning of string1.
  nth_appearance is the nth appearance of string2. This is optional. If omitted, it                                              defaults to 1.
Note: If string2 is not found in string1, then the instr Oracle function will return 0.
INSTR('Tech on the net','e') - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,1) - 2 the first occurrence of 'e'
INSTR('Tech on the net','e',1,2) - 11 the second occurrence of 'e'
INSTR('Tech on the net','e',1,3) - 14 the third occurrence of 'e'
INSTR('Tech on the net','e',-3,2) -

LENGTH                              

Returns the length of the specified string.

LENGTH(STRING)             

string1 is NULL, then the function returns NULL.

LENGTH(NULL) - NULL

 LENGTH('') - NULL

 LENGTH('Tech on the Net') - 15

LENGTH('Tech on the Net ') - 16 

LOWER                               

Converts all letters in the specified string to lowercase. If there are characters                                                in the string that are not letters, they are unaffected by this function.

 LOWER(STRING)

LOWER('Tech on the Net') - 'tech on the net'

LOWER('GEORGE BURNS 123 ') - 'george burns 123 '

LPAD                                   

Function pads the left-side of a string with a specific set of characters (when                                                  string1 is not null).
LPAD( STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.
LPAD('tech', 7) - ' tech'
LPAD('tech', 2) - 'te'
LPAD('tech', 8, '0') - '0000tech'
LPAD('tech on the net', 15, 'z')- 'tech on the net'
LPAD('tech on the net', 16, 'z')- 'ztech on the net'

LTRIM                                     

 Function removes all specified characters from the left-hand side of a string.

LTRIM( STRING1 [, TRIM_STRING])
string1 is the string to trim the characters from the left-hand side.
trim_string is the string that will be removed from the left-hand side of string1.
If this parameter is omitted, the ltrim function will remove all leading spaces from string1.
LTRIM(' tech') - 'tech'
LTRIM(' tech', ' ') - 'tech'
LTRIM('000123', '0') - '123'
LTRIM('123123Tech', '123') - 'Tech'
LTRIM('123123Tech123', '123') - 'Tech123'
LTRIM('xyxzyyyTech', 'xyz') - 'Tech'
LTRIM('6372Tech', '0123456789') - 'Tech'

The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
LTRIM ('xyxzyyyTech', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The ltrim function can also be used to remove all leading numbers as demonstrated in the next example.
LTRIM ('6372Tech', '0123456789')
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the ltrim function.


REPLACE                                      

Function replaces a sequence of characters in a string with another set                                                         of characters.
REPLACE(STRING1,STRINGTO_REPLACE [,REPLACEMENT_STRING])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.
REPLACE('123123tech', '123') - 'tech'
REPLACE('123tech123', '123') - 'tech'
REPLACE('222tech', '2', '3') - '333tech'
REPLACE('0000123', '0') - '123'
REPLACE('0000123', '0', ' ') - ' 123'
REPLACE ('123tech', '231', '555') - '123tech'

RPAD                                          

Function pads the right-side of a string with a specific set of characters                                                         (when string1 is not null).
RPAD (STRING1, PADDED_LENGTH [,PAD_STRING])
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return. If the padded_length is smaller than the original string, the rpad function will truncate the string to the size of padded_length.
pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the rpad function will pad spaces to the right-side of string1.
RPAD('tech', 7) - 'tech '
RPAD('tech', 2) - 'te'
RPAD('tech', 8, '0') - 'tech0000'
RPAD('tech on the net', 15, 'z') - 'tech on the net'
RPAD('tech on the net', 16, 'z') - 'tech on the netz'

RTRIM                                    

 Function removes all specified characters from the right-hand side of a                                                          string.
RTRIM( STRING1 [,TRIM_STRING] )
string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.

RTRIM('tech ') - 'tech'
RTRIM('tech ', ' ') - 'tech'
RTRIM('123000', '0') - '123'
RTRIM('Tech123123', '123') - 'Tech'
RTRIM('123Tech123', '123') - '123Tech'
RTRIM('Techxyxzyyy', 'xyz') - 'Tech'
RTRIM('Tech6372', '0123456789') - 'Tech'
The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the above example.
RTRIM ('Techxyxzyyy', 'xyz')
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
RTRIM ('Tech6372', '0123456789')
The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.

SOUNDEX                                   

Function returns a phonetic representation (the way it sounds) of a string.
SOUNDEX( STRING )
The Soundex algorithm is as follows:
1. The soundex return value will always begin with the first letter of string1.
2. The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.
The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
SOUNDEX('tech on the net') - 'T253'
SOUNDEX('TECH ON THE NET') - 'T253'
SOUNDEX('apples') - 'A142'
SOUNDEX('apples are great') - 'A142'
SOUNDEX('applus') - 'A142'

SUBSTR                                                      

 Function allows you to extract a substring from a string.

SUBSTR (STRING, START_POSITION [,LENGTH])
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.
SUBSTR ('This is a test', 6, 2) - 'is' 

SUBSTR ('This is a test', 6) - 'is a test'
SUBSTR ('TechOnTheNet', 1, 4) - 'Tech'
SUBSTR ('TechOnTheNet', -3, 3) - 'Net'
SUBSTR ('TechOnTheNet', -6, 3) - 'The'
SUBSTR ('TechOnTheNet', -8, 2) - 'On'
SUBSTR ('TechOnTheNet', 7, -2) - null

TRANSLATE


Translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
TRANSLATE(STRING1,STRING_TO_REPLACE,REPLACEMENT_STRING)
TRANSLATE('1tech23', '123', '456') - '4tech56'
TRANSLATE('222tech', '2ec', '3it') - '333tith'


TRIM


Function removes all specified characters either from the beginning or the ending of a string.
TRIM ([LEADING | TRAILING | BOTH [TRIM_CHARACTER] FROM STRING1)
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.
TRIM(' tech ') - 'tech'
TRIM(' ' FROM ' tech ') - 'tech'
TRIM(LEADING '0' FROM '000123') - '123'
TRIM(TRAILING '1' FROM 'Tech1') - 'Tech'
TRIM(BOTH '1' FROM '123Tech111') - '23Tech'

UPPER


Converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
UPPER (STRING)
UPPER('Tech on the Net') - 'TECH ON THE NET'
UPPER('george burns 123 ') - 'GEORGE BURNS 123 '

TO_CHAR


Function converts a number or date to a string.
TO_CHAR(VALUE [,FORMAT_MASK] [,NLS_LANGUAGE])
value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.
Examples :
Query                                                                     Result
TO_CHAR(SYSDATE,'DAY')                           'MONDAY'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyy') 'Monday, 30th January, 2012'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyysp') 'Monday, 30th January, two thousand twelve'
TO_CHAR (SYSDATE, 'Day, ddth Month, yyyyspth') 'Monday, 30th January, two thousand twelfth'
TO_CHAR(1210.73, '9999.9')                                   '1210.7'
TO_CHAR(1210.73, '9,999.99')                                '1,210.73'
TO_CHAR(1210.73, '$9,999.00')                              '$1,210.73'
TO_CHAR(21, '000099')                                           '000021'
TO_CHAR(SYSDATE, 'yyyy/mm/dd')                       '2003/07/09'
TO_CHAR(SYSDATE, 'Month DD, YYYY')            'July 09, 2003'
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY')      'July 9, 2003'
TO_CHAR(SYSDATE, 'MON DDth, YYYY')         'JUL 09TH, 2003'
TO_CHAR(SYSDATE, 'FMMON DDth, YYYY')    'JUL 9TH, 2003'
TO_CHAR(SYSDATE, 'FMMon ddth, YYYY')        'Jul 9th, 2003'

Format Mask :

Format                                                                          Meaning
D                      Day of the week (1(sunday) to 7(saturday)) 

DD                   Day of the month (1 to 31)
DDD                Day of the year (1 to 365) 

DAY                Full day for ex. 'Monday', 'Tuesday'.
DY                   Day in three letters for ex. 'MON', 'TUE'.
W                    Week of the month (1 to 5)
WW                Week of the year (1 to 53)
MM                 Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON               Month in three characters like "Jan", "Feb".
MONTH          Full Month like "January", "February".
RM                  Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q                     Quarter of the Month
YY                   Last two digits of the year.
YYYY             Full year
YEAR             Year in words like "Nineteen Ninety Nine"
HH                   Hours in 12 hour format
HH12               Hours in 12 hour format
HH24               Hours in 24 hour format
MI                   Minutes
SS                   Seconds
FF                   Fractional Seconds
SSSSS            Milliseconds
J                     Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR                 If the year is less than 50 Assumes the year as 21ST Century.
                      If the year is greater than 50 then assumes the year in 20th Century.

Suffixes :

TH
Returns th, st, rd or nd according to the leading number like 1st, 2nd, 3rd

SP
Spells out the leading number

AM or PM
Returns AM or PM according to the time

SPTH
Returns Spelled Ordinal number. For. Example First, Fourth

TO_DATE


Function converts a string to a date.
TO_DATE(STRING [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
TO_DATE('2003/07/09','yyyy/mm/dd')- JULY 9, 2003
TO_DATE('070903','MMDDYY') - JULY 9, 2003
TO_DATE('20020315','yyyymmdd') - MAR 15, 2002
By default, date will be displayed in session NLS date format.


TO_NUMBER


Function converts a string to a number.
TO_NUMBER(STRING1 [,FORMAT_MASK] [,NLS_LANGUAGE])
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.

nls_language is optional. This is the nls language used to convert string1 to a number.
TO_NUMBER('1210.73', '9999.99') - 1210.73
TO_NUMBER('546', '999') - 546
TO_NUMBER('23', '99') - 23
Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric
value as follows:
TO_NUMBER('1210.73') - 1210.73
TO_TIMESTAMP
Function converts a string to a timestamp.
TO_TIMESTAMP( STRING1 [,FORMAT_MASK] [,NLSPARAM])
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'
TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
- '13-DEC-03 10.13.18.000000000 AM'

ABS


Returns absolute value of a number
ABS( NUMBER )
ABS(-23) - 23
ABS(-23.65) - 23.65
ABS(23.65) - 23.65
ABS(23.65 * -1) - 23.65


AVG


Function returns average value of an expression.
AVG( EXPRESSION )
Basic Syntax is :
SELECT Avg( expression )
FROM tables
WHERE predicates;

CEIL


Function returns the smallest integer value that is greater than or equal to a number.
CEIL ( NUMBER )
CEIL(32.65) - 33
CEIL(32) - 32

CEIL(-32.65) - -32
CEIL(-32) - -32

COUNT


Function returns number of rows in a query.
COUNT ( EXPRESSION )
Supplier_ID                         Supplier_Name                           State
       1                                           IBM                                    CA
       2                                       Microsoft
       3                                                                                     NVIDIA
COUNT(*) - 3
COUNT(1) - 3
COUNT(SUPPLIER_ID) - 3

COUNT(STATE) - 1 

FLOOR


Function returns the largest integer value that is equal to or less than a number.
FLOOR( NUMBER )
FLOOR(5.9) - 5
FLOOR(34.29) - 34
FLOOR(-5.9) - -6

GREATEST


Function returns the greatest value in a list of expressions.
GREATEST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.
GREATEST(2, 5, 12, 3) - 12
GREATEST('2', '5', '12', '3') - '5'
GREATEST('apples', 'oranges', 'bananas') - 'oranges'
GREATEST('apples', 'applis', 'applas') - 'applis'
Note :
Having a NULL value in one of the expressions will return NULL as the greatest value.

LEAST


Function returns the smallest value in a list of expressions.
LEAST( EXPR1, EXPR2, … EXPR_n )
expr1, expr2, . expr_n are expressions that are evaluated by the least function.
If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.
Note :

Having a NULL value in one of the expressions will return NULL as the least value.
LEAST(2, 5, 12, 3) - 2
LEAST('2', '5', '12', '3') - '12'
LEAST('apples', 'oranges', 'bananas') - 'apples'
LEAST('apples', 'applis', 'applas') - 'applas'
LEAST('apples', 'applis', 'applas', NULL) - NULL

Read More