PL/SQL Scenario Questions – Part 2

In this blog we will explore some PL/SQL – Scenario questions related to Oracle PL/SQL subprogram functions only.

  • Function Input, Output Parameters & Return Type
  • Calling Function using Dynamic SQL
  • Function used in case & if statement
  • DDL & DMLs in Oracle Subprogram Function
  • Calling function from SQL statement
  • Table Function
  • Pipeline Table Function

Scenario 1. Can function have output parameter & return type both. If yes how you can call function & retrieve output.

Solution 1: Function can have input/output parameters (optional) & return type(mandatory).

Sample Function having input, output & return type:

create or replace function fnc_test (INPUT_PARAMETER IN NUMBER, OUTPUT_PARAMETER OUT NUMBER)
return number
as
begin
OUTPUT_PARAMETER:= 2;
return INPUT_PARAMETER;
end;
/

declare
ln_input number:=1;
ln_num_ret number;
ln_num_out number;
begin

ln_num_ret:=  fnc_test(ln_input,ln_num_out) ;
dbms_output.put_line('ln_num_ret:='|| ln_num_ret);
dbms_output.put_line('ln_num_out:='|| ln_num_out);
end;
/

Calling Function using Dynamic SQL

See below example calling function using CALL statement.

create or replace function fnc_square (INPUT_PARAMETER IN NUMBER, OUTPUT_PARAMETER OUT NUMBER)
return number
as
begin
OUTPUT_PARAMETER:= INPUT_PARAMETER;
return INPUT_PARAMETER*INPUT_PARAMETER;
end;
/

set serveroutput on;
declare
ln_input number:=4;
ln_num_ret number;
ln_num_out number;
lv_sql varchar2(100);
begin
 
lv_sql:= 'call fnc_square(:input,:output) into :return' ;

execute immediate lv_sql using  in ln_input , out ln_num_out, out ln_num_ret;

dbms_output.put_line('ln_num_ret:='|| ln_num_ret);
dbms_output.put_line('ln_num_out:='|| ln_num_out);
end;
/

Scenario 2: Can you use function in if statement or case statement in PL/SQL.

Solution: Yes we can use in if/case statement like variable if it does not have any arguments. Only return type.

create or replace function fnc_check 
return number
as
begin
return 1;
end;
/

set serveroutput on;
declare
begin
if (fnc_check=1) then
dbms_output.put_line('Hello from if statement');
else
dbms_output.put_line('Bye');
end if;
end;
/

set serveroutput on;
declare
begin
  case fnc_check
  when 1 then dbms_output.put_line('Hello from case');
   else dbms_output.put_line('Bye');
end case;
end;
/

Scenario 3. Can you fire DDLs & DMLs in function?

Solution 3: Yes we can, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_TEST(
    ACCOUNT_ID IN NUMBER)
  RETURN VARCHAR2
IS
  ln_out VARCHAR2(100);
BEGIN
  BEGIN
    EXECUTE immediate 'DROP TABLE T_ACCOUNT';
     dbms_output.put_line('Table dropped T_ACCOUNT');
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  
EXECUTE immediate 'CREATE TABLE T_ACCOUNT (ACCOUNT_ID NUMBER)';
dbms_output.put_line('Table created: T_ACCOUNT');

EXECUTE immediate 'insert into T_ACCOUNT values ('|| ACCOUNT_ID||')';
commit;
dbms_output.put_line('DMLs done for T_ACCOUNT');

ln_out:= ACCOUNT_ID || ' got created!';
RETURN ln_out;
END FNC_TEST;
/

declare
ln_out varchar2(100);
begin
ln_out:=FNC_TEST(1000);
dbms_output.put_line('ln_out:='|| ln_out);
end
;

But you cannot call this function from SQL statement. See below:

You cannot call function from SQL statement if any DDL/DMLs or Output parameter is present.

You can call function easily if it has return type or input parameters. See below sample

Example 1: Function calling from SQL

create or replace FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/ 
select FNC_SQUARE(2) from dual;

Example 2: Function having multiple input parameters & return type, calling from SQL.

create or replace FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER, INPUT_NUM IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_NUM;
RETURN ln_out;
END;
/ 
select FNC_SQUARE(2,3) from dual;

Example 3: Function having only return type, calling from SQL.

create or replace function fnc_check 
return number
as
begin
return 1;
end;
/

Scenario 4. Can Oracle Function return more that one value.

Solution: Function can only have one return type. But you an return a table using Table Functions. Check out below example of table function.

CREATE type objects_type
 AS
  OBJECT
  (
 OBJECT_NAME VARCHAR2(128),
    OBJECT_TYPE VARCHAR2(23) ,
    CREATED     DATE );
  /
CREATE OR REPLACE type t_row_tab
AS
  TABLE OF objects_type;
CREATE OR REPLACE FUNCTION get_objects(
    input_type VARCHAR2)
  RETURN t_row_tab
AS
  obj_record t_row_tab:=t_row_tab();
BEGIN
  SELECT objects_type(OBJECT_NAME,OBJECT_TYPE,CREATED) BULK COLLECT
  INTO obj_record
  FROM user_objects
  WHERE OBJECT_TYPE = input_type ;
  RETURN obj_record;
END;
/

Scenario 5. In which scenario you don’t specify anything after return keyword in function body,

Solution: While writing the pipeline table function. Pipelining enables a table function to return rows faster and can reduce the memory, which is required to cache results.

It include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, instead of complete table collection. 

CREATE OR REPLACE FUNCTION get_objects
  RETURN t_row_tab PIPELINED
IS
  obj_record t_row_tab:=t_row_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    PIPE ROW(objects_type('TABLE'||i, 'TABLE ',sysdate));   
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE (get_objects);

Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL Scenario Questions – Part 3

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover RPAD, PLAD, Null Function, Instr, Substr, regex_substr, translate.


Scenario 1. What will be the output of following SQL statement.

select rpad('TEST      ',12, '*') from dual;

length (‘TEST      ‘) = 10

Output:

rpad

Scenario 2. What will be the output of following SQL statement.

select lpad('1234',12, '*') from dual;
lpad

Scenario 3: What will be the output of following SQL statement.

select nullif(12,12) from dual;
select nullif(12,13) from dual;
nullif

NULLIF (expression 1, expression 2)

  • NULLIF function returns NULL if expression 1 and expression 2 are equal.
  • NULLIF function returns expression 1 if not equal.
  • expression 1 cannot be literal null.
  • Datatypes should be same for both expression.
select nullif('NULL',12) from dual;
nullif

Scenario 4: What will be the output of following SQL statement.

select NVL2('', 'Name avalailbe', 'Name not avalailbe') from dual;
select NVL2('Ram', 'Name avalailbe', 'Name not avalailbe') from dual;
vnl2

NVL2( input_string, value_if_not_null, value_if_null )

You can substitutes a value when a null value is encountered as well as when a non-null value is encountered.

Scenario 5: Rotating columns to rows, see below screenshot. See below source data.

unpv
select * from sales_data
unpivot
( SALES for MONTH_ in (JAN, FEB, MAR, APR) );
unpv2

Scenario 6: Draw this pattern.

* 
* * 
* * * 
* * * * 
* * * * *
select rpad('*', rownum, '*') from dual connect by rownum <=5;
star

Scenario 7: Draw this pattern.

* * * * * 
* * * * 
* * * 
* * 
*
select rpad('*', rownum, '*') from dual connect by rownum <=5
order by 1 desc;
star

Scenario 8: What will be the output of following SQL statement.

select translate ( 'HeXXo WorXd' , 'X', 'l') from dual;
translate

Scenario 8: Extract the state code from these addresses.

insttr

Option 1: Using instr & substr

select ADDRESS1,  instr(ADDRESS1,','),
trim(substr(address1,instr(ADDRESS1,',')+1)) state_code from address;
instr2

Option 2: Easy way as you know that codes are last two char of string.

select address1, substr(address1,-2) state_code from address;
subsst

Scenario 8: Extract the state code from these addresses. Here after the 2nd occurrence of delimiter ~.

kerala
select ADDRESS1, regexp_substr(ADDRESS1, '[^~]+',1,2) state_name,
regexp_substr(ADDRESS1, '[^~]+',1,3) state_code from address;
kk

Visit below blogs for more scenario questions.


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!

 

 

Inner Join: SAS Merge & Proc SQL

Folks,

In this blog we will explore the basic concept of Inner Join using SAS Merge & Proc SQL.


An inner join retrieve only the matched rows from the data-sets/tables.

Suppose we have two data-sets/tables Customer & Sales.

So an inner join of Customer and Sales gives the result of Customer intersect Sales, i.e. the inner part of a Venn diagram intersection. (See below image)

innerjoinn.png

In SQL terminology, this is inner join. Equivalent to a merge in a DATA step in SAS.

To achieve the inner join using SAS merge, first we have to understand the concept of IN= option, which is especially useful when merging and concatenating data sets.

IN= option

IN= option tells SAS to create an “indicator variable” having the value 0 or 1 depending on whether or not the current observation comes from the input data set.

  • If the observation does come from the input data-set, then the indicator will be 1.
  • If the observation does not come from the input data set, then the indicator variable value will be 0.

Lets have a example – Here is the Data-sets Customer & Sales.

inner

DATA TEMP;
MERGE CUSTOMER (IN=IN_CUSTOMER ) SALES(IN=IN_SALES);
BY CUSTOMER_ID ;

/* Creating new variables using indicator variables */
CUSTOMER = IN_CUSTOMER ;
SALES = IN_SALES;

RUN;
Proc print data=temp;

See below output. Indicator Sales= 1 When observation coming from the input data-set Sales  else indicator is 0, same applied for customer data set.

full

 

SAS Merge (Inner Join):

Lets go for SAS Merge (Inner Join) using IN= Options.

Prerequisites for a SAS Merge

  • Input data-sets must have at least one common variable to merge with same name (In our case we have CUSTOMER_ID). If not same then use rename.
  • Input data sets must be sorted by the common variable(s) that will be used to merge.

Let see how we can use IN= options to get the common data using Merge.

Select only those observation where IN variables Sales=1 & Customer=1 i.e. common observation from both datasets.joins.png

IN VAR

SAS Merge (Inner Join) –

DATA INNER_JOIN;
MERGE CUSTOMER (IN=IN_CUSTOMER ) SALES(IN=IN_SALES);
BY CUSTOMER_ID ;
IF IN_CUSTOMER = 1 & IN_SALES = 1;
RUN;

Output

output inner join

Inner Join using Proc SQL in SAS:

  • Joining is possible on columns with differing names. (No need to rename)
  • Input data sets doesn’t required sorting by the common variable(s)

1st way using Proc SQL-

 PROC SQL;
 SELECT C.CUSTOMER_ID,C.COUNTRY, S.SALES FROM 
 CUSTOMER AS C, SALES AS S
 WHERE C.CUSTOMER_ID=S.CUSTOMER_ID;
 QUIT;

2nd way using Proc SQl-

PROC SQL;
 SELECT C.CUSTOMER_ID,C.COUNTRY, S.SALES FROM 
 CUSTOMER AS C JOIN SALES AS S
 ON C.CUSTOMER_ID=S.CUSTOMER_ID;
 QUIT;

Proc SQL Output

proc sql


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Database 11g Installation & Setup| Windows OS

Oracle Database 11g  Installation & Setup| Windows OS

Folks,

In this vlog we will learn how to download, install & setup Oracle Database 11g on Windows 10 OS.

Step by step installation guide:

You can download the Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64) using this Link

Refer this blog setup your first schema/user: Oracle Database 11g | Create User | Check Instance Name| Host Name | Services | Parameters |


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL Scenario Questions – Part 1

Oracle SQL Scenario Questions – Part 1

Folks,

In this blog we will explore some latest Oracle SQL scenario questions for interview. Here we will cover topics like regex, pivot, binary tree and other Oops related PL/SQL questions.

For more question visit:


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

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.


PL/SQL Scenario Questions

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 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 14: 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

For more scenarios question visit:


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

Thanks! Happy Learning! Your feedback would be appreciated!