Trim Example In Oracle SQL

TRIM function

TRIM is a function that takes a character expression and returns that expression with leading and/or trailing pad characters removed. Optional parameters indicate whether leading, or trailing, or both leading and trailing pad characters should be removed, and specify the pad character that is to be removed.

TRIM Syntax

TRIM( [ trimOperands ] trimSource)
trimOperands ::= { trimType [ trimCharacter ] FROM | trimCharacter FROM }
trimType ::= { LEADING | TRAILING | BOTH }
trimCharacter ::= CharacterExpression
trimSource ::= CharacterExpression

Trim Example In Oracle SQL 1 : Trim Character

Trim Single Character from declaring Text Value. Here Trim 'A' from 'AAAHAI'.

Example Query For Trim Single Character

SELECT TRIM('A' FROM 'AAAHAI') FROM DUAL;
OUTPUT:
HAI

Trim Example In Oracle SQL 2 : Trim Space

Trim Space Character from declaring Text Value. Here Trim Space from ''I AM THIYAGARAAJ'.

Example Query For Trim Space

SELECT TRIM(' ' FROM 'I AM THIYAGARAAJ') FROM DUAL;
OUTPUT:
I AM THIYAGARAAJ

Trim Example In Oracle SQL 3 : Trim Character

Trim Single Character from declaring Text Value. Here Trim 'A' from ''I AM THIYAGARAAJ'.

Example Query For Trim Space

SELECT TRIM('A' FROM 'AAI AM THIYAGARAAJAAAA') FROM DUAL;
OUTPUT:
I AM THIYAGARAAJ

Trim Example In Oracle SQL 4 :Trim Space

Trim Space Character from declaring Text Value. Here Trim Space from ''I AM EXAMPLE'.

Example Query For Trim Space

SELECT TRIM(' ' FROM '        I AM EXAMPLE') FROM DUAL;
OUTPUT:
I AM EXAMPLE

Step By Step Example For Object Type In Oracle

Object Type Definition

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.

Step By Step Example For Object Type In Oracle

Create Object Type

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

SQL> CREATE TYPE ObjectPersonType AS OBJECT (

  2    ID       NUMBER,

  3    FNAME    VARCHAR2(20),

  4    LNAME    VARCHAR2(25),

  5    PHONE    VARCHAR2(20),

  6    MAP MEMBER FUNCTION get_idno RETURN NUMBER,

  7    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ));

  8  /

Type created.

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

here,  object variables are,

ID       NUMBER,

FNAME    VARCHAR2(20),

LNAME    VARCHAR2(25),

PHONE    VARCHAR2(20)

and

Object Memeber Functions,Procedures are

MAP MEMBER FUNCTION get_idno RETURN NUMBER

-- Return Self ID Number,

MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType )

-- In & Out Is Own Type Parameter

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

Create/ Replace Type Body

 

SQL> CREATE OR REPLACE TYPE BODY ObjectPersonType AS

  2    MAP MEMBER FUNCTION get_idno RETURN NUMBER IS

  3    BEGIN

  4      RETURN ID;

  5    END;

  6    MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY ObjectPersonType ) IS

  7            BEGIN

  8      -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details

  9              DBMS_OUTPUT.PUT_LINE(TO_CHAR(ID) || ' ' || FNAME || ' ' || LNAME);

  10      DBMS_OUTPUT.PUT_LINE(PHONE);

 11    END;

 12  END;

 13/

Type body created.

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

Create New Table Using Object Type (ObjectPersonType)

 

Description:

Table Name Is PERSONLIST,

Fields are, RECORDDATE as Date & DETAILS As ObjectPersonType(Using Defined Object Type)

CREATE TABLE PERSONLIST (

  RECORDDATE   DATE,

  DETAILS              ObjectPersonType);

Table created.

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

Insert Values For Object Type PERSONLIST

 

INSERT INTO PERSONLIST VALUES (

                SYSDATE,  ObjectPersonType (25, 'Raaj', 'Malik','9876700001'));

1 row created.

INSERT INTO PERSONLIST VALUES (

                SYSDATE,  ObjectPersonType (21, 'Boss', 'Sivaji','9876700002'));

1 row created.

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

Select Statements For Object Type

SQL> SELECT * FROM PERSONLIST;

RECORDDAT

---------

DETAILS(ID, FNAME, LNAME, PHONE)

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

13-JUL-10

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

13-JUL-10

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT DETAILS FROM PERSONLIST;

DETAILS(ID, FNAME, LNAME, PHONE)

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

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT DETAILS FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);

DETAILS(ID, FNAME, LNAME, PHONE)

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

OBJECTPERSONTYPE(21, 'Boss', 'Sivaji', '9876700002')

SQL> SELECT c.DETAILS.get_idno() FROM PERSONLIST c;

C.DETAILS.GET_IDNO()

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

                  25

                  21

SQL> SELECT Obj.DETAILS.FNAME FROM PERSONLIST Obj;

DETAILS.FNAME

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

Raaj

Boss

SQL> SELECT DETAILS FROM PERSONLIST Obj WHERE Obj.DETAILS.FNAME='Raaj';

DETAILS(ID, FNAME, LNAME, PHONE)

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

OBJECTPERSONTYPE(25, 'Raaj', 'Malik', '9876700001')

SQL> DECLARE

  Obj OBJECTPERSONTYPE;

BEGIN

  SELECT DETAILS INTO Obj FROM PERSONLIST WHERE RECORDDATE=( SELECT MAX(RECORDDATE) FROM PERSONLIST);

  Obj.display_details();

END;

/

21 Boss Sivaji

9876700002

PL/SQL procedure successfully completed.

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

Difference of Unhandled and Handled exception

Step 1 : Create Table

 create table cust_table

 (

   NAME                                VARCHAR2(30),

   ORD_DT                            DATE,

   PUR_DATE                    DATE,

   QUANTITY                        NUMBER,

   ITEM_NO                         VARCHAR2(30)

  );

  Step 2 : Try to execute a block without exception part.

    begin

                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakurunath',sysdate-1,sysdate,100,'ORA0001');

                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakumar',sysdate-1,sysdate,100,'ORA0002');

                                update cust_table set NAME='eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

                                where ITEM_NO='ORA0002';

 end;

 /

  Step 3 : Do select ,Check how many rows inserted and updated.

 select * from cust_table;

 Step 4 : Try to execute same block with  exception.

  begin

                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakurunath',sysdate-1,sysdate,100,'ORA0001');

                                insert into cust_table(NAME,ORD_DT,PUR_DATE,QUANTITY,ITEM_NO) values('Sivakumar',sysdate-1,sysdate,100,'ORA0002');

                                update cust_table set NAME='eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

                                where ITEM_NO='ORA0001';

 exception

     when others then

        dbms_output.put_line(sqlerrm);

 end;

 /

 step 5 : Do select ,Check how many rows inserted and updated.

 select * from cust_table;

 step 6 : Find in which scenario transaction remain as a part of transaction (step 2 or step 4). 

Difference Between View and Materialized View with Examples

Difference Between View and Materialized

1. Materialized views are disk based and update periodically base upon the query definition.


2. Views are virtual only and run the query definition each time they are accessed.

Difference Between View and Materialized Working Example

Step 1 : Create Base Table

create table T1(KEY number,VAL varchar2(10));

insert into t1 values(1,'a');
insert into t1 values(2,'b');
insert into t1 values(3,'c');
insert into t1 values(4,'');

Step 2 : Create Ordinary View

create view v as select * from t1 ;

Step 3 : Create Materialized View

create materialized view log on t1 with rowid;
create materialized view mv refresh fast with rowid as select * from t1 ;

Step 4: Check for rowid similarity and difference in materialized view

select rowid from T1 order by rowid ;
select rowid from v order by rowid ;
select rowid from mv order by rowid ;

Step 5 := Update base table

update t1 set val = upper(val);

Step 6 := After DML try to select

select * from T1 order by rowid ;
select * from v order by rowid ;
select * from mv order by rowid ;

Step 7 :- Refresh your materialized View

execute dbms_mview.refresh( 'MV' );

Step 8 := Try to update Base table Via both the view

update v set val = lower(val); -- View will be create
update mv set val = lower(val); -- Here it won't

Step 9 := Drop all objects.

drop materialized view mv ;
drop view v ;
drop table t1;

How Exception Works - Detailed Step by Step Explanation In Oracle PL/SQL

What is Exception  In Oracle PL/SQL ?

Whenever error occurred in executable area (ie. in begin block) then exception will raise and program will terminate .

Difference Between Calling and Called program

• A program contain many programs inside then it is called as calling program.


• A program called by another program then it is called as called program.

How Exception Works  In Oracle PL/SQL

1. If exception handled in both calling and called environment .Then any exception arrives inside the called program then exception will be handled by itself only(ie. called program) and program will not terminate until exception raised in calling environment or execute completely.


2. While handling exception in calling environment but not in called environment .Then any exception arrives inside the called program then exception will be handled by called one and program terminates immediately.

Exception Works

Detailed Step by Step Explanation In Oracle PL/SQL

Step 1: Drop Table If Exists

drop table error;
drop table tab_1;
drop table tab_2;
drop table tab_3;

Step 2: Create Table

create table error ( value number, status varchar2(1000),st_date timestamp,nam_tab varchar2(50));
create table tab_1 (a number , dup_num number, name varchar2(30));
create table tab_2 (a number , dup_num number, name varchar2(30));
create table tab_3 (a number , dup_num number, name varchar2(30));

Step 3: Drop Sequence

drop sequence test_seq;

Step 4 : create Sequence

create sequence test_seq increment by 1 start with 1 maxvalue 100 nocache nocycle;

Step 5:= Create Packages use exception in both calling and called environment

create or replace package pack_one
as
procedure p1(p_a number);
end pack_one;
/

create or replace package body pack_one
as
procedure p1(p_a number)
as
v_num number;
v_error varchar2(4000);
begin

select dup_num
into v_num
from tab_1
where a = p_a;
dbms_output.put_line('Step 1 ..........................');
pack_two.p2(v_num);
dbms_output.put_line('Step 2 ..........................');
pack_three.p3(v_num);
dbms_output.put_line('Step 3 ..........................');
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p1');
end p1;
end pack_one;
/

create or replace package pack_two
as
procedure p2(p_a number);
end pack_two;
/

create or replace package body pack_two
as
procedure p2(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_2
where a = p_a;
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p2');
end p2;
end pack_two;
/

create or replace package pack_three
as
procedure p3(p_a number);
end pack_three;
/

create or replace package body pack_three
as
procedure p3(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_3
where a = p_a;
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p3');
end p3;

end pack_three;
/

Step 6: Truncate Table rows only

Truncate table tab_1;
Truncate table tab_2;
Truncate table tab_3;

Step 7 : Insert Few Rows

Insert into tab_1 values(1,20,'Hello');
Insert into tab_2 values(2,3,'Hi');
Insert into tab_3 values(20,1,'Wow');

set serveroutput on
execute pack_one.p1(1);

Select * from error;

Step 8 : Create Package by writing Exception only in calling package (eg.pack_one)

create or replace package pack_one
as
procedure p1(p_a number);
end pack_one;
/

create or replace package body pack_one
as
procedure p1(p_a number)
as
v_num number;
v_error varchar2(4000);
v_seq number;
begin
select dup_num
into v_num
from tab_1
where a = p_a;
dbms_output.put_line('Step 1 ..........................');
pack_two.p2(v_num);
dbms_output.put_line('Step 2 ..........................');
pack_three.p3(v_num);
dbms_output.put_line('Step 3 ..........................');
Exception
when others then
v_error := substr(sqlerrm,1,100);
insert into error values(test_seq.nextval ,v_error,sysdate,'pack_one.p1');
end p1;
end pack_one;
/

create or replace package pack_two
as
procedure p2(p_a number);
end pack_two;
/

create or replace package body pack_two
as
procedure p2(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_2
where a = p_a;
end p2;
end pack_two;
/

create or replace package pack_three
as
procedure p3(p_a number);
end pack_three;
/
create or replace package body pack_three
as
procedure p3(p_a number)
as
v_num number;
v_error varchar2(4000);
begin
select dup_num
into v_num
from tab_3
where a = p_a;
end p3;
end pack_three;
/

Step 9: Truncate Table rows only

Truncate table tab_1;
Truncate table tab_2;
Truncate table tab_3;

Step 10 : Insert Few Rows

Insert into tab_1 values(1,20,'Hello');
Insert into tab_2 values(2,3,’Hi’);
Insert into tab_3 values(20,1,'Wow');

set serveroutput on
execute pack_one.p1(1);

Select * from error;

Create simple procedure in PL/SQL Oracle

Subprograms

Subprograms are named PL/SQL blocks that can be called with a set of parameters.
There are two types of blocks in PL/SQL:
•    Procedures
•    Functions

Structure Of Oracle Procedure(Anonymous block)

DECLARE         (optional)
        /* Variable Block                     */
    BEGIN             (mandatory)
        /* Executable Statements / Queries     */
    EXCEPTION    (optional)
        /* Exception Action                 */
END;                (mandatory)
/

Syntax of PL/SQL Oracle Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name
 [(parameter1 [mode1] datatype1,
  parameter2 [mode2] datatype2,
  . . .)]
IS|AS
PL/SQL Block;

Structure Of Oracle Procedure(Named block)

CREATE [OR REPLACE] PROCEDURE procedure_name          (mandatory)
        /* Variable Block                     */
    BEGIN             (mandatory)
        /* Executable Statements / Queries     */
    EXCEPTION    (optional)
        /* Exception Action                 */
END;                (mandatory)
/

Modes:

•    IN: procedure must be called with a value for the parameter. Value cannot be changed
•    OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference)
•    IN OUT: value can be sent, and changes to the parameter are seen by the user

Default Mode is:

IN

Consider Table

Table Name: Example

NAME             VARCHAR2(10)
NUM              NUMBER(3)

Table Data

Select * from example;
NAME       NUM                    
---------- ----------------------
NAMEONE    1                      
NAMETWO    2                      
NAMETHREE  3                      
NAMEFOUR   4                      
NAMEFIVE   5                      
NAMESIX    6                      
NAMESEVEN  7                      
test -100  100                    
test -200  200                    
test -300  300                    
test -500  500        

11 rows selected

Create Simple Procedure in PL/SQL Oracle For Get Name From Example Table

Create or replace procedure p_getname
(v_num IN example.num%TYPE,
v_name OUT example.name%TYPE)
/*    v_num     - Input Parameter            */
/*    v_name     - Output Parameter            */
IS
BEGIN
    select name
    into v_name
    from example
    where v_num = num;
END;
/

Calling the Procedure

set serveroutput on;
declare
    getname  example.name%TYPE;
begin
    p_getname(1,getname);
    dbms_output.put_line('-----------');
    dbms_output.put_line(getname);
end;
/

Sample Output:

anonymous block completed
-----------
NAMEONE

While Loop in PL/SQL Example Code and Concepts

While Loop In Oracle PL/SQL

The WHILE LOOP statement runs one or more statements while a condition is TRUE. The WHILE LOOP statement ends when the condition becomes FALSE or NULL, when a statement inside the loop transfers control outside the loop, or when PL/SQL raises an exception.

Syntax WHILE Loop In Oracle PL/SQL

WHILE Contitions
Loop Statements
END LOOP

While Loop in PL/SQL Example Code : Start With 1 to 10

DECLARE
i NUMBER :=0;
BEGIN
WHILE i < 10 LOOP
i:= i+1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

While Loop in PL/SQL Example Code :  Start With 10 to 17

DECLARE
i NUMBER :=10;
BEGIN
WHILE i < 17 LOOP
i:= i+1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

While Loop in PL/SQL Example Code :  Start & End With variables value

DECLARE
StartValue NUMBER := 10;
EndValue   NUMBER := 20;
BEGIN
WHILE StartValue < EndValue LOOP
StartValue := StartValue + 1;
DBMS_OUTPUT.PUT_LINE('Current Number :'||StartValue);
END LOOP;
END;
/

For Loop PL/SQL Example Code and Syntax

For Loop Overview and Defintion

Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double dot (..) serves as the range operator. The syntax follows:

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
sequence_of_statements
END LOOP;

Syntax For Loop In Oracle

FOR Conditions
Loop Statements
END LOOP

For Loop Evaluation Formula In Oracle PL/SQL

The range is evaluated when the FOR loop is first entered and is never re-evaluated.

As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.

FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
sequence_of_statements -- executes three times
END LOOP;

For Loop PL/SQL Example Code: Start With 1 to 10

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

For Loop PL/SQL Example Code: Start With 10 to 17

BEGIN
FOR i IN 10..17 LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/

For Loop PL/SQL Example Code: Start & End With variables value

DECLARE
StartValue NUMBER := 10;
EndValue   NUMBER := 20;
BEGIN
FOR i IN StartValue .. EndValue LOOP
DBMS_OUTPUT.PUT_LINE('Current Number :'||i);
END LOOP;
END;
/