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.

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

Get Oracle Version ( SQL )

You can get all information about Oracle Version ( SQL ). Below Examples,

Get Oracle Version ( SQL ) Database Version

select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production  

Get Oracle Version ( SQL ) Select Full Product Details

select * from product_component_version;PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
NLSRTL
10.2.0.2.0
ProductionOracle Database 10g Enterprise Edition
10.2.0.2.0
ProdPRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------PL/SQL
10.2.0.2.0
Production

TNS for Solaris:
10.2.0.2.0

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
Production

Get Oracle Version ( SQL ) Select Version With Banner

select * from v$version where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
Select Version With Banner

select * from v$version where banner like 'PL/SQL%';
BANNER
----------------------------------------------------------------
PL/SQL Release 10.2.0.2.0 - Production

 

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

Create table In Oracle Simple Example

Create table In Oracle

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements. Here we see about Create Table with examples

Create Table Statement

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

Create table In Oracle Syntax:

CREATE TABLE TABLENAME (
     field1 data_type,
     field2 data_type,
     ...
     ...
     fieldn data_type
 )

Create table In Simple Example Declaration:

here,
 Consider
 Two Fields;
 1) E_ID as number
 2) E_Name as varchar(20)

Create table In Oracle Simple Example Code:

CREATE TABLE Example
 CREATE TABLE EMPLOYEE
     (
     E_ID NUMBER,
     E_NAME VARCHAR2(20)
     );

Drop In Oracle

Drop In Oracle:

Assume table name is EMPLOYEE

DROP TABLE_NAME;
DROP EMPLOYEE;

Count In Oracle SQL

Assume table name is EMPLOYEE and it has E_NAME Field.

Count In Oracle SQL  : All Records In Table

SELECT COUNT(*) FROM EMPLOYEE;

Count In Oracle SQL : All Records In Table with Head Name

SELECT COUNT(*) AS EMPLOYYE_COUNT FROM EMPLOYEE;

Count Column Records In Table

SELECT COUNT(E_NAME) FROM EMPLOYEE;

Count Distinct Column Records In Table

SELECT COUNT(DISTINCT E_NAME) FROM EMPLOYEE;

Count In Oracle SQL : Selected Records In Table

SELECT COUNT(*) FROM EMPLOYEE WHERE CONDITION E_NAME = 'BOND';

Count In Oracle SQL : Selected Records with Head Name

SELECT COUNT(*) FROM EMPLOYEE NAME_BOND WHERE CONDITION E_NAME = 'BOND';

Delete In Oracle SQL

Assume table name is EMPLOYEE and it has E_NAME Field.

Delete In Oracle SQL : All Rows In Tables

DELETE TABLE_NAME;
DELETE EMPLOYEE;

Delete In Oracle SQL : All Rows In Tables

DELETE FROM TABLE_NAME;
DELETE FROM EMPLOYEE;

Delete In Oracle SQL : Selected Rows In Tables

DELETE FROM TABLE_NAME WHERE CONDITION;
DELETE FROM EMPLOYEE WHERE E_NAME = 'RAJU';

Delete In Oracle SQL : through Select Rows In Tables

DELETE FROM (SELECT * FROM TABLE_NAME WHERE CONDITION);
DELETE FROM (SELECT * FROM EMPLOYEE WHERE E_NAME = 'RAJU');

Get Date Time In Oracle

Date Time In Oracle: Date From System In Oracle

SELECT SYSDATE FROM DUAL;

Date Time In Oracle: Date From System In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') FROM DUAL;

Date Time In Oracle: Time From System In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') FROM DUAL;

Date Time In Oracle: Date From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') TODAY_DATE FROM DUAL;

Date Time In Oracle: Time From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Time(24 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH24 MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Time(12 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Date & Time(12 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD HH24 MI SS') DAT_TIME FROM DUAL;

Create Sequence In Oracle

Sequence In Oracle Definition:

You can create for auto increment number/ID creation. Oracle generates sequence of number depend upon our code boundary.

Sequence In Oracle Syntax:

CREATE SEQUENCE SEQUENCE_NAME

        MINVALUE VALUE        ( Assign Minimum Value )

        MAXVALUE VALUE        ( Assign Maximum Value )

        START WITH VALUE    ( Assign Start Value )

        INCREMENT BY VALUE;    ( Assign Increment Value )

Sequence In Oracle Example:

CREATE SEQUENCE DUMMY_SEQUENCE

    MINVALUE 1

    MAXVALUE 10000

    START WITH 1

    INCREMENT BY 1

    CACHE 20;

Sequence Usage:

    DUMMY_SEQUENCE.NEXTVAL

Sequence Inside Query

INSERT INTO DUMMY_PERSONAL_DTLS

(DUMMY_ID,DUMMY_FNAME,DUMMY_LANME)

VALUES

(DUMMY_SEQUENCE.NEXTVAL,'RAJINI','THIYAGARAAJ');

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;
/