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!

Follow @shobhitsinghIN