Procedure FAQS

1. What is a procedure in Oracle?

A procedure is a named PL/SQL block that performs a specific task and can be called multiple times.

·        Can accept input and output parameters

·        Does not return a value directly (unlike functions)

·        Can be stored in the database for reuse

2. What is the syntax of a procedure?

CREATE OR REPLACE PROCEDURE procedure_name (

   param1 IN NUMBER,

   param2 OUT VARCHAR2

) IS

BEGIN

   -- PL/SQL statements

   param2 := 'Hello ' || param1;

END procedure_name;

·        IN – Input parameter

·        OUT – Output parameter

·        IN OUT – Input and output parameter

3. How do you call a procedure?

From PL/SQL Block

DECLARE

   v_msg VARCHAR2(100);

BEGIN

   procedure_name(101, v_msg);

   DBMS_OUTPUT.PUT_LINE(v_msg);

END;

From SQL*Plus

EXEC procedure_name(101, :v_msg);

4. What are procedure parameters types?

Parameter Type	Description
IN	Passes value to the procedure; cannot be modified inside
OUT	Returns value from procedure to caller
IN OUT	Passes value in and can modify it to return back

5. Can a procedure have default parameter values?

Yes. Example:

CREATE OR REPLACE PROCEDURE greet_user(

   p_name IN VARCHAR2 DEFAULT 'Guest'

) IS

BEGIN

   DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name);

END;

Calling without parameter will use the default 'Guest'.

6. Difference between procedure and function

Feature	Procedure	Function
Returns value	No	Yes (single value)
Can be used in SQL	No	Yes, if deterministic
Purpose	Perform action	Compute and return value

7. What are advantages of using procedures?

·        Reusability of code

·        Modularity for large applications

·        Improved maintainability

·        Easier debugging

·        Can encapsulate complex logic

8. Can procedures call other procedures or functions?

Yes. Procedures and functions can call each other.

BEGIN

   procedure1;

   v_result := function1(10);

END;

9. How can you handle exceptions in procedures?

Use EXCEPTION block:

CREATE OR REPLACE PROCEDURE divide_numbers(a NUMBER, b NUMBER) IS

   v_result NUMBER;

BEGIN

   v_result := a / b;

   DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

EXCEPTION

   WHEN ZERO_DIVIDE THEN

      DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Some error occurred');

END;

10. Can procedures improve performance?

Indirectly:

·        Reduce network round-trips by processing logic on the server

·        Can use bulk operations (BULK COLLECT + FORALL) for large data

·        Encapsulation allows optimized SQL execution

11. What are common mistakes with procedures?

·        Not specifying parameter modes correctly

·        Forgetting to COMMIT after DML (if required)

·        Using procedures where functions would be better for SQL use

·        Ignoring exception handling

·        Overusing row-by-row operations instead of bulk processing

12. Best practices for Oracle procedures

·        Use parameterized procedures instead of hardcoded values

·        Use EXCEPTION handling for robust code

·        Keep procedures modular and reusable

·        Use BULK COLLECT and FORALL for processing large datasets

·        Avoid DML inside loops where possible

·        Document procedure purpose and parameters

13. Can procedures return result sets?

Yes, indirectly via:

·        OUT parameters

·        REF CURSOR for multi-row result sets

Example with REF CURSOR:

CREATE OR REPLACE PROCEDURE get_employees(

   p_dept IN NUMBER,

   p_cursor OUT SYS_REFCURSOR

) IS

BEGIN

   OPEN p_cursor FOR

      SELECT * FROM employees WHERE department_id = p_dept;

END;

14. How to debug a procedure?

·        Use DBMS_OUTPUT.PUT_LINE statements

·        Check logs or write debug messages to a table

·        Test with small datasets first

·        Use Oracle SQL Developer Debugger for step-by-step execution