Oracle Interval Partitioning

In this blog Oracle Range Partition we have explored how we can create range partition in oracle using date as partition key. Also we have seen one example where the range partition key is defined maximum upto ’01-APR-2019′ (Non-inclusive). But we are trying to insert ’20-OCT-2019′ date data, which is not getting mapped to any partition, that’s why ORA-14400 error occurred. See below error report provided by oracle.

SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”

Here comes the Interval Partition, which is extension of  Oracle Range Partition in which, interval partitions are automatically created by the database when data is inserted into the partition.

Monthly Partition:

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  INTERVAL
  (
    NUMTOYMINTERVAL(1, 'MONTH')
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

After that we have inserted the data of future months, i.e. SEP & OCT for which Partitions were not available. See below insert statements.

INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567890', 'ABC', TO_DATE('20-SEP-19', 'DD-MON-RR'));
INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567891', 'ABC', TO_DATE('20-OCT-19', 'DD-MON-RR'));
COMMIT;

After committing the data let see the automatic partitions created by Oracle :

2

Notice that a system generated partition named SYS_P601 & SYS_P402 has been created after inserting rows with a partition key greater than the provided partition.

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT partition (SYS_P601);

It would be really painful to look up the system generated partition name every time, if you have to query directly partition. Therefore, oracle provided a syntax to specify a partition is by using the partition for (DATE) clause in the query: See below example:

partition for (to_date(’15-OCT-2019′,’DD-MON-YYYY’));

SELECT * FROM T_ACCOUNT partition  for (to_date('01-SEP-2019','DD-MON-YYYY'));

The following restrictions apply on Interval Partition:

  • Only one partitioning key column allowed (NUMBER or DATE)
  • Minimum one partition must be defined when the table is created.
  • MAXVALUE partition cannot be defined.
  • NULL values are not allowed in the partition column.
  • Can’t be used at the subpartition level.
  • Not supported for index-organized tables.
  • You cannot create a domain index on an interval partitioned table.

 

Daily Partitions Example:

CREATE TABLE T_TRANSACTIONS
  (
    TRANSACTION_KEY NUMBER,
    AMOUNT          NUMBER,
    CUST_ID         NUMBER,
    TRANSACTION_DATE DATE
  )
  PARTITION BY RANGE
  (
    TRANSACTION_DATE
  )
  INTERVAL
  (
    NUMTODSINTERVAL(1, 'day')
  )
  (
    PARTITION P_01012019 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

 


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Range Partition

Range partitioning is useful when data has some sort of logical range. Example dates or numbers. See below example where monthly range partitions created based on date column in account table.

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
    PARTITION P_JAN2019 VALUES LESS THAN (TO_DATE('01-FEB-2019','DD-MON-YYYY')),
    PARTITION P_FEB2019 VALUES LESS THAN (TO_DATE('01-MAR-2019','DD-MON-YYYY')),
    PARTITION P_MAR2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY'))
  );

 

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT PARTITION(P_MAR2019);

 

Check partitions metadata in user_tab_partitions & subpartitions.

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_ACCOUNT';

1

To check the Partition KEY of the table, use below query:

SELECT partition_name,
  column_name,
  high_value,
  partition_position
FROM USER_TAB_PARTITIONS PART ,
  USER_PART_KEY_COLUMNS PART_KEY
WHERE table_name ='T_ACCOUNT'
AND PART.table_name = PART_KEY.name;

In the above example any record having acc_date less than ’01-JAN-2019′ will go to P_DEC2018 partition. See below example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2010', 'DD-MON-YYYY')
  );
COMMIT;
SELECT * FROM T_ACCOUNT PARTITION (P_DEC2018) ;

In the this example any record having acc_date greater than ’31-MAR-2019′ will error out, as no partition exists for that date.

Example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2019', 'DD-MON-YYYY')
  );

Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification

1

Here in this example we can see the partition key is defined maximum upto 2019-04-01 (Non-inclusive) . But we are trying to insert 20-OCT-2019, which is not getting mapped to any partition, that’s why this error occured as date value which we are trying to insert is T_ACCOUNT not satisfying the partition key range criteria.

Solution: To fix it, you may have to add new partitions or add a Maxvalue partition.

ALTER TABLE T_ACCOUNT ADD partition P_OCT2019 VALUES less than (TO_DATE('01-NOV-2019', 'DD-MON-YYYY'));
ALTER TABLE T_ACCOUNT ADD partition P_MAXVALUE VALUES less than (MAXVALUE);

You can also go with : Oracle Interval Partitioning to resolve this issue, but this not preferable.

Drop Partition:

Once historical data is no longer required for business analysis, after retention period the whole partition can be dropped.

DROP PARTITION P_DEC2018;

Updating Key:

When you are trying to update the partition key column for any record.

UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';

Error report:
SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 – “updating partition key column would cause a partition change”
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old
partition key.

You can’t move a row with an update: Row movement disabled, which is the default option.

Solution:  We can enable Row movement to partition table T_ACCOUNT. It allows rows to be moved across partitions.

ALTER TABLE T_ACCOUNT ENABLE ROW MOVEMENT;
UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';
COMMIT;

 


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Data Integrator | Master & Work Repositories Relationships

Oracle Data Integrator | Master & Work Repositories Relationships

Folks,

In this blog we will explore the relationship between ODI Master & Work Repository.

You can refer this blog  for better understanding of ODI Master & Work Repositories, how they differ from each other & how to setup new repository in ODI Studio using Oracle technology.


Master & Work Repositories Relationships

Master Repository is usually associated with multiple Work Repositories.

Each Work repository can associate with only one Master Repository.

Case 1)  When single Work Repository is attached to the Master Repository.

  • When we have to isolate any environment from the rest of the environments.
  • Usually case of Production/Pre-Production environment.

In below figure we can see that production/pre-prod master repository is isolated from other repositories. Its like one to one relationship. No information sharing.

 

Case 2)  When multiple Work Repository is attached to same Master Repository.

  • See below Figure 2, both work repositories are sharing same master repository.
  • Basically both are sharing the same Topology and Security data.
sharing
Figure 2

 

In real word production environment is isolated from rest of the environments.

practical.PNG


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

Folks,

In this blog we will learn about Oracle Data Integrator Repositories.

You can refer this blog for ODI 11g Installation & Setup on Windows OS.


Suppose you are just done with the ODI installation on your machine:

Now you are going to use the ODI Studio to develops, operate & administrate some ELT project work. But you need some space/location to start working & to save your all work. Basically you need ODI repository to store the information you are going to develop, configure or used by the ODI.

There are two types of ODI repositories: Master & Work Repository

  • Objects developed, configured, operated/used by the ODI stored in one of these two types of repositories.
  • Repositories are accessed in client/server mode by various components of the ODI architecture.
  • Master Repository is usually associated with multiple Work Repositories.
  • Each Work repository can associate with only one Master Repository.
  • Work repository can either be Development Work Repository or Execution Work repository. (We will discuss this point later in this blog)

repo1

Refer this YouTube video to setup Master & Work Repositories in ODI 11g using Oracle.


Master Repository:

Master Repositories are used to store:

  • ODI Connection Information: JDBC URLs, LDAP information, user names and passwords for source/target connections.
  • ODI Security Information: ODI users names and passwords, ODI users privileges and profiles information, if security is handled by ODI.
  • Version Information: When a new version of an object is created in the ODI, that information is saved in the Master Repository.

Information contained in the Master Repository is maintained with the Topology Navigator and the Security Navigator in ODI Studio.

  • Data contained in master repository is mostly static data and will be going through minimum changes, whenever any topology or security information added/updated.
  • ODI components access the Master Repository, whenever they need the topology and security information data stored in it.

Work Repository:

Work Repositories are used to store:

 

  • Project Information: Folders/Sub-folders, packages, interface, procedure, variables, sequences, functions,  knowledge module etc.
  • Models: Data Store structures and metadata, fields, columns, constraints etc.
  • Load Plans & Scenarios: Load plans information & scenarios.
  • Operations:  Execution details, scheduling information, and logs.

The contents of a Work Repository are managed by using Designer and Operator. They are also accessed by the agent at run time.

When a Work Repository is used only to store execution, operations logs information (typically for production environment), it is called an execution work repository. Designer Navigator is disabled in this execution repository.

Work repository consists of two sub-types: Development Work RepositoryExecution Work repository.

Development Work Repository:

  • Repository which contains all the objects which are used for developing the development project i.e. packages, interface, procedure, variables, sequences, load plans, data stores etc.
  • Designer Navigator & Operator Navigator both are available for Development Work Repository.
  • Usually, used for development projects.

2.PNG

Execution Work Repository:

  • Repository which stores only the scenarios (executable files) & execution information, no source code & designer information available here.
  • Usually, used for production and test environments to make sure that source code will not be modified in a live environment.
  • Operator Navigator is available, but Designer Navigator is restricted for Execution Work Repository.

3

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Refer this YouTube video to Install ODI 11g on Windows OS.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

 

Oracle SQL – Scenario Questions (Interview)

Oracle SQL – Scenario Questions (Interview)

Folks,

In this blog we will explore some latest Oracle SQL – Scenario questions for Interview!


Scenario 1:  

Select numeric & character values in separate columns using data present in single column.

Input Data – One column having both numeric & character values in it. See below sample data.

create table data (value varchar2(20));
insert into data values ('1');
insert into data values ('a');
insert into data values ('b');
insert into data values ('2');
insert into data values ('c');

 

Untitled.png

Output – Show numeric & character values in two separate columns using select query only. See below output.

output

Solution of Scenario 1:

Step 1: Separate the data values using REGEXP_LIKE & digit class [:digit:].

select  null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')

Output of Step 1:

3.png

Step 2 – Remove null values from the data.

select nv.numeric_value, cv.character_value
from

( select  numeric_value , rownum rnum from
(select  null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')
)
where numeric_value is not null ) nv 

full join 

(select  character_value , rownum rnum from
(select  null as numeric_value, value as character_value from data WHERE REGEXP_LIKE(value, '[^[:digit:]]')
union all
select value as numeric_value, null as character_value from data WHERE REGEXP_LIKE(value, '[[:digit:]]')
)
where character_value is not null ) cv

on nv.rnum=cv.rnum

Output Step 2:

4.png


Scenario 2:  

Print data on the basis of Even/Odd data present in Id Column.

Input Data – See below sample data.

create table data (ID number, value varchar2(20));
insert into data values (1,'A');
insert into data values (2,'B');
insert into data values (3,'C');
insert into data values (4,'D');
insert into data values (5,'E');
insert into data values (6,'F');
insert into data values (7,'G');
insert into data values (8,'H');
 

Untitled.png

Output Required: Suppose we have to print the Even ID – Values

output.png

Solution of Scenario 2:

Output 2.1:  – With null values

select decode(mod(id,2),0,value, null) as value from data ;

3.png

Output 2.2:  – Without null values

-- 1st way
select value from data where (mod(id,2))=0;
-- 2nd way
select value
  from data
where id in (
              select decode(mod(id,2),0,id,null) from data
            );

1

4.png

For Odd – Just change the decode condition, see below code.

-- Simple Way
select value from data where (mod(id,2))<>0;
-- Without Null Values
select value from data where id in ( select decode(mod(id,2),0,null,id) from data );

-- With Null Values
select decode(mod(id,2),0,null,value) as value from data ; 

1



Scenario 3: 

Fetch alternate data from table – Using Even/Odd rownum.

Suppose we have only one column in the table & we have to fetch alternate records using rowid & rownum concepts.

Input Data – 

create table data ( value varchar2(20));
insert into data values ('A');
insert into data values ('B');
insert into data values ('C');
insert into data values ('D');
insert into data values ('E');
insert into data values ('F');
insert into data values ('G');
insert into data values ('H');
 

Untitled.png

Solution of Scenario 3:

Simple way:-

SELECT value FROM
  (SELECT(mod(rownum,2)) AS rn,value FROM data
  ) WHERE rn=0;

For Even Rowid- 

-- Without Null Values
select value
  from data
where rowid in (
              select decode(mod(rownum,2),0,rowid, null)  from data
            );

-- With Null Values
 select decode(mod(rownum,2),0,value, null) as value from data ;

3

4

For Odd Rowid- 

Simple way:-

SELECT value FROM
  (SELECT(mod(rownum,2)) AS rn,value FROM data
  ) WHERE rn<>0;
 
-- Other way
-- Without Null Values
select value
  from data
where rowid in (
              select decode(mod(rownum,2),0, null,  rowid)  from data
            );

-- With Null Values
 select decode(mod(rownum,2),0, null,  value) as value from data ;

3.png


 

Scenario 4: 

Print like this using select query.


2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18
2 * 10 = 20

Solution of Scenario 4:

SELECT '2 * ' || rownum  || ' = ' || rownum *2 as t
  FROM DUAL
CONNECT BY rownum  <= 10

output.png


 

Scenario 5:

Print how many ‘e’ in ‘elephant’ using select query.

Solution

 

select length('elephant') - length(replace('elephant', 'e', '')) from dual;

--  case-insensitive matching.
SELECT REGEXP_COUNT ('Elephant', 'e', 1, 'i')
FROM dual;

--  case-sensitive matching.
SELECT REGEXP_COUNT ('Elephant', 'e', 1, 'c')
FROM dual;
 

Output – Using length & Replace

Untitled

Output – Using Regexp_count

4    3


 

Scenario 6:

create table data ( city varchar2(10), gender char(1) );
insert into data values ('Delhi','M');
insert into data values ('Delhi','F');
insert into data values ('Delhi','M');
insert into data values ('Delhi','M');
insert into data values ('Pune','F');
insert into data values ('Pune','M');
insert into data values ('Pune','F');
insert into data values ('Pune','F');
insert into data values ('Banglore','F');
insert into data values ('Banglore','F');
 

Input Data –                              Output Required –

Untitled.png output.png

 

 

 

 

 

 

Solution of Scenario 6 using CASE statement:

select
city,
Count ( case gender when 'M' then 1 end) as Male_Count,
Count ( case gender when 'F' then 1 end) as Female_Count,
Count(*) as Total_Count
from DATA
group by city;
 

 

3.png

Using Pivot-

with w_data as (SELECT * FROM
(
  SELECT city, gender
  FROM data
)
PIVOT
(
  count(gender)
  FOR gender IN ('M' as Male_Count , 'F' as Female_Count)
))
select city, Male_Count,Female_Count,Male_Count+Female_Count as Total_Count from w_data
;

 

Using Decode-

SELECT city,SUM(DECODE(gender, 'M', 1,0)) AS Male_Count,
       SUM(DECODE(gender, 'F', 1,0)) AS Female_Count,
       count(*) AS Total_Count
FROM   DATA
group by city;

Scenario 7:

See below data table. Update product_name ‘CAR’ with ‘BIKE’ & ‘BIKE’ with ‘CAR’ using single update statement.

create table data (Product_id number, Product_name char(1));
insert into data values (1,'CAR');
insert into data values (2,'BIKE');
insert into data values (3,'CAR');
insert into data values (4,'BIKE');
insert into data values (5,'CAR');
insert into data values (6,'BIKE');
insert into data values (7,'CAR');
insert into data values (8,'BIKE');
insert into data values (9,'CAR');
insert into data values (10,'BIKE');

Untitled.png

Solution of Scenario 7:

 

-- Using Decode Statement
update DATA
set product_name = decode (product_name,'CAR', 'BIKE','CAR')

-- Using Case Statement
update DATA
set product_name = ( case product_name
                          when 'CAR' then 'BIKE'
                          when 'BIKE' then 'CAR' END)
 

Scenario 8:

8.1) In ‘abcde12xys2254’ string, replace all numeric data with null.

select regexp_replace('abcde12xys2254', '[0-9]', '') from dual;
 

3.png

8.2) In ‘abcde12xys2254’ string, replace all character data with null.

select regexp_replace('abcde12xys2254', '[^0-9]', '') from dual;
 

4.png


Scenario 9: Binary Tree

Input:- See binary tree below

tree-1.jpg

  • Root Node: If node is root node. Which doesn’t have any parent node.
  • Leaf: If node is leaf node. Which doesn’t have any further child node.
  • Inner: If node is neither root nor leaf node.

Here we have a table, Data, containing two columns: Node and Parent_Node.

create table data (node number, parent_node number);

insert into data values (40,null);
insert into data values (10,20);
insert into data values (20,40);
insert into data values (30,20);
insert into data values (60,40);
insert into data values (50,60);
insert into data values (70,60);

treeee.JPG

Output Required:-

Query to display the node & node type of Binary Tree.

tre data    tree-1.jpg

 

Solution of Scenario 9:

SELECT Node ,
  CASE
    WHEN Parent_node IS NULL
    THEN 'Root'
    WHEN EXISTS
      (SELECT 1 FROM Data B WHERE B.Parent_Node=A.Node
      )
    THEN 'Inner'
    ELSE 'Leaf'
  END AS Node_Type
FROM Data A
ORDER BY 2 DESC;

 

Some more questions.

Question 10: Can we use where clause in case statement in SQL? or Vice-Versa Can we use case in where statement in SQL.?

Question 11: Can we use aggregate functions in update statement.

Question 12: Can two procedure in a package have the same name & input parameters differ only by datatype ?

Answer 12 : – Yes, this is possible. This is a concept of Overloading.

Overloading – Creating multiple procedures/functions of the same name in a package, having different numbers of arguments and / or where the arguments have different datatypes.

Create or replace PACKAGE PKG_TEST  AS 

   PROCEDURE PRC_SAMPLE ( 
          INPUT_ID IN VARCHAR2 );

   PROCEDURE PRC_SAMPLE ( 
          INPUT_ID IN NUMBER );

END PKG_TEST;

Create or replace PACKAGE BODY PKG_TEST AS

PROCEDURE PRC_SAMPLE (INPUT_ID IN VARCHAR2)
IS 
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);

END PRC_SAMPLE;

PROCEDURE PRC_SAMPLE (INPUT_ID IN NUMBER)
IS 
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);

END PRC_SAMPLE;

END PKG_TEST;

 

proc

 

Question 13: Can two standalone procedure have the same name. Only input parameters differ by datatype ?

Answer 13: – Yes, this is possible.


CREATE OR REPLACE PROCEDURE SA_PRC_SAMPLE (INPUT_ID IN VARCHAR2)
IS 
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);

END SA_PRC_SAMPLE;

CREATE OR REPLACE PROCEDURE SA_PRC_SAMPLE (INPUT_ID IN NUMBER)
IS 
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);

END SA_PRC_SAMPLE;

EXEC SA_PRC_SAMPLE(1);
EXEC SA_PRC_SAMPLE('1');

SA PRC.PNG

Question 14: Can 2 functions have the same name & input parameters differ only by datatype ?

Answer : Yes

create or replace PACKAGE PKG_TEST  AS 

   FUNCTION  FNC_SAMPLE ( 
          INPUT_ID IN VARCHAR2 ) RETURN VARCHAR2 ;

   FUNCTION FNC_SAMPLE ( 
          INPUT_ID IN NUMBER ) RETURN NUMBER ;

END PKG_TEST;

create or replace PACKAGE BODY PKG_TEST AS

FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return VARCHAR2
IS 
 lv_id VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;

return lv_id;

END FNC_SAMPLE;

FUNCTION FNC_SAMPLE (INPUT_ID IN NUMBER)
return NUMBER
IS 
 lv_id NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;

return lv_id;

END FNC_SAMPLE;
END PKG_TEST;

1

2

 

Question 15: Can 2 functions have the same name & same input parameters. Only return type differ by datatype ?

Answer: To overload a function, you must change the “signature” of the function, which is the defined by the position and datatype of the inputs. Return is not a part of the function signature, so it can’t be used for overloading.

 

I have created sample functions of same name, same parameters but different return type in package. Package Spec & Body compile successfully.  But it not work while calling function.


create or replace PACKAGE PKG_TEST  AS 

   FUNCTION  FNC_SAMPLE ( 
          INPUT_ID IN VARCHAR2 ) RETURN VARCHAR2 ;

   FUNCTION FNC_SAMPLE ( 
          INPUT_ID IN VARCHAR2 ) RETURN NUMBER ;

END PKG_TEST;
/

create or replace PACKAGE BODY PKG_TEST AS

FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return VARCHAR2
IS 
 lv_id VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= INPUT_ID;

return lv_id;

END FNC_SAMPLE;

FUNCTION FNC_SAMPLE (INPUT_ID IN VARCHAR2)
return NUMBER
IS 
 lv_id NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Value of input Id is '|| INPUT_ID);
lv_id:= to_number(INPUT_ID);

return lv_id;

END FNC_SAMPLE;

END PKG_TEST;
/

 

func

 


If you have optimized answers for these scenarios, then please comment.

Thanks! Happy Learning! Your feedback would be appreciated!