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
Great Bro.
LikeLiked by 1 person