In this blog we will explore some PL/SQL – Scenario questions.


Scenario 1. Write PL/SQL program to show overloading concept of oops in PL/SQL.

Solution 1: See below example for PL/SQL package PKG_TEST, it has two procedure. Both procedure has same name & same input parameter name. But parameter type is different. This is example of overloading of objects in PL/SQL.

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 ('Calling VARCHAR2 procedure');
  END PRC_SAMPLE;
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling number procedure');
  END PRC_SAMPLE;
END PKG_TEST;
/

pls0

Scenario 2. Write PL/SQL block which raise the user-defined exception if the day is weekend.

SET SERVEROUTPUT ON;
DECLARE
  exp_weeknds EXCEPTION;
BEGIN
  IF TO_CHAR(SYSDATE, 'DY')IN ('SUN','SAT') THEN
    raise exp_weeknds;
  ELSE
    DBMS_OUTPUT.put_line( 'Record processed!');
  END IF;
EXCEPTION
WHEN exp_weeknds THEN
  dbms_output.put_line('Cannot process the record as its weekend!');
END ;
/

pls1

Scenario 3. Write PL/SQL program to show hiding concept of oops in PL/SQL.

Solution 3: See below example for PL/SQL package PKG_TEST, it has two procedure in its body but only one is declared in package spec. The one which is not declared but present in the body is basically hidden from outside world & can be used internally only.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_INTERNAL(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   -- This procedure is not declared in package specification.
   DBMS_OUTPUT.PUT_LINE ('Calling PRC_INTERNAL procedure');
  END PRC_INTERNAL;
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling PRC_TEST procedure');
    PRC_INTERNAL('100');
  END PRC_TEST;
END PKG_TEST;
/

pls3

Now try calling the procedure which was not defined in declaration.

EXEC PKG_TEST.PRC_INTERNAL(‘100’);

pls4

Calling procedure PRC_TEST which is calling the internal procedure PRC_INTERNAL.

pls5

Scenario 4. Can we write commit in PL/SQL exception block.

Solution 4: Yes, but it is not recommended to write the commit. As if you want to logs the exception you can use pragma autonomous transaction.  Below example showing that we can use commit in exception block.

CREATE TABLE err_logs
  (
    error_number  VARCHAR2(25),
    error_message VARCHAR2(1000)
  );

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
  SELECT to_number('X') INTO ln_num FROM dual;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
  INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END;
/

pls6

Scenario 5. What is pragma autonomous transaction?

Solution 5: Fire an transaction which is independent of calling transaction and return to the calling transaction without affecting it’s state.

CREATE OR REPLACE PROCEDURE prc_log_errors (lv_err_code  IN  VARCHAR2,lv_err_msg IN VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END prc_log_errors;
/

If you see below example, DML transaction performed before exception occurred. As we have called the procedure with pargma autonomous which has commit in it, still my main translation data is roll backed.

CREATE TABLE test_table (TEST_ID NUMBER);

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
 INSERT INTO test_table(TEST_ID)
    VALUES(1);
     INSERT INTO test_table(TEST_ID)
    VALUES(2);
  SELECT to_number('X') INTO ln_num FROM dual;
  commit;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
 prc_log_errors(lv_err_code,lv_err_msg);
 rollback;
END;
/

pls6

pls5

Scenario 6. Can you fire DDLs in function?

Solution 6: Yes using dynamic sql, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
  BEGIN
    EXECUTE immediate 'drop table TEST_TABLE';
     dbms_output.put_line('Table dropped');
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
EXECUTE immediate 'CREATE TABLE TEST_TABLE (TEST_ID NUMBER)';
dbms_output.put_line('Table created');
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/

pls4

Now calling that function in SQL statement:

pls5

Same thing with the DML also you can use in function but cannot call using SQL statement. I have tried calling function from sql which has DML & commit;

pls6

Scenario 7. Can you call procedure from sql statement?

Solution 7: No

CREATE OR REPLACE PROCEDURE PRC_SQUARE(
    INPUT_NUM IN NUMBER,OUTPUT_NUM OUT NUMBER )
IS
BEGIN
OUTPUT_NUM:= INPUT_NUM*INPUT_NUM;
END;
/

SELECT PRC_SQUARE(2) FROM dual;

pls6

Call procedure using below method.

declare 
ln_out NUMBER; 
begin 
PRC_SQUARE(6,ln_out); 
dbms_output.put_line('Square is: '|| ln_out); 
end; /

pls5

Function you can using SQL statements. As it has always return type.

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

pls6


Thanks!

Happy Learning! Your feedback would be appreciated!

 

One thought on “PL/SQL Scenario Questions – Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s