PL/SQL – Part 2

Posted byRajalakshmi MPosted inpl/sql, SQLTags:, ,

BIND VARIABLES / HOST VARIABLES:

  • Bind variables does not require PL/SQL block.
  • Mostly, we use them for static values, but they can also be used for dynamic values.
-- Declare a bind variable
VARIABLE BINDVAR_1 VARCHAR2(240);

-- Assign a value to the bind variable
EXEC :BINDVAR_1 := 'Testing1';

-- Print the value of the bind variable
PRINT :BINDVAR_1;

In this case:

  1. Declare a bind variable:
    • VARIABLE BINDVAR_1 VARCHAR2(240);
      • This line declares a bind variable named BINDVAR_1 with a data type of VARCHAR2(240).
      • The colon (:) is not used here because it’s not necessary when declaring the variable; it’s only needed when referencing the variable later.
  2. Assign a value to the bind variable:
    • EXEC :BINDVAR_1 := 'Testing1';
      • The EXEC command is used to execute a PL/SQL statement.
      • :BINDVAR_1 refers to the bind variable declared earlier. The colon (:) is used here to indicate that it’s a bind variable.
      • The value ‘Testing1’ is assigned to BINDVAR_1.
  3. Print the value of the bind variable:
    • PRINT :BINDVAR_1;
      • The PRINT command is used to display the value of a bind variable.
VARIABLE L_BIND VARCHAR2(240);
EXEC :L_BIND := 'Test';

BEGIN
DBMS_OUTPUT.PUT_LINE('L_BIND Value Is: ' || :L_BIND);
END;
SET AUTOPRINT ON;

VARIABLE L_BIND1 VARCHAR2(240);
VARIABLE L_BIND2 VARCHAR2(240);
EXEC :L_BIND1 := 'Test1';
EXEC :L_BIND2 := 'Test2';
  • SET AUTOPRINT ON; -> This command enables automatic printing of bind variable values after execution.

Note:

We have three options for viewing or printing the values of bind variables:

Automatic Printing with SET AUTOPRINT ON;

  • By using SET AUTOPRINT ON; SQL Developer will automatically display the values of bind variables after they are assigned.
  • This is a convenient way to see the values without explicitly issuing additional commands.

PRINT Command:

  • We can use the PRINT command to explicitly display the values of bind variables.
  • For example, 
PRINT :L_BIND1; 
PRINT :L_BIND2;
  • will print the values of :L_BIND1 and :L_BIND2, respectively.

DBMS_OUTPUT.PUT_LINE in PL/SQL with SET SERVEROUTPUT ON;

  • If we are executing PL/SQL blocks and want to display messages within the PL/SQL code, We can use DBMS_OUTPUT.PUT_LINE.
  • However, to see the output in SQL Developer Script Output Screen, we need to enable server output with SET SERVEROUTPUT ON;
  • Or to see the output in DBMS Output Screen, enable DBMS OUTPUT.

LITERALS:

select 'Ms.' || emp_name from xx1195_emp where emp_id <> 101;
  • Literals are values that are explicitly specified in the code.
  • In this query, ‘Ms.’ is a string literal.
  • It’s a fixed value and will be the same for every row.

NESTED BLOCKS:

DECLARE
    -- Declaration section
BEGIN
    -- Executable section
    DECLARE
        -- Nested declaration section
    BEGIN
        -- Nested executable section
    END;
    -- More executable statements in the outer block
END;
  • Nested blocks refer to the ability to include one or more blocks within another block.
  • Inside the outer BEGIN/END block, there is another set of DECLARE/BEGIN/END that represents a nested block.
DECLARE
    outer_variable NUMBER := 5; 
BEGIN
    DBMS_OUTPUT.PUT_LINE('Outer variable: ' || outer_variable);   
    DECLARE
        inner_variable NUMBER := 10; 
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Inner variable: ' || inner_variable);
    END;
END;
  • Outer Block: The code starts with the DECLARE keyword, where you declare variables for the entire block. In this case, outer_variable is declared and assigned a value of 5. Then, in the BEGIN/END section, there’s a message printed to the console using DBMS_OUTPUT.PUT_LINE, which displays the value of outer_variable.
  • Nested Block: Inside the outer block, there’s another DECLARE/BEGIN/END block. This is a nested block. It has its own set of declarations and executable statements. In this block, an inner_variable is declared and assigned a value of 10. A message is printed displaying the value of inner_variable.
  • Scope: Variables declared in the outer block (like outer_variable) are accessible throughout the entire outer block, including any nested blocks. However, variables declared in the nested block (like inner_variable) are only accessible within that specific nested block. Once you exit the nested block, you can no longer reference inner_variable.
  • END: Finally, after the entire block is executed, the program moves outside of the block. At this point, variables declared within the block are no longer accessible.

EXCEPTION:

  • Exceptions are used to handle errors or unexpected situations that may occur during the execution of a program. When something goes wrong, like a division by zero or an attempt to insert a duplicate value into a unique column, an exception is raised.
  • Think of exceptions as a way for your program to gracefully deal with problems instead of crashing. Instead of letting the error disrupt the entire program, you can catch and handle it, allowing the program to continue or take a different course of action.
  • Exception Raised: When an error occurs, an exception is raised. This could be due to various reasons like invalid data, division by zero, or no data found.
  • Exception Handler: We can write a block of code known as an “exception handler” to deal with specific types of exceptions. This block specifies what actions should be taken when a particular type of error occurs.
  • Catch and Handle: When an exception occurs, the program checks if there’s a corresponding exception handler. If there is, it executes the code within the handler to address the issue. If not, the program might terminate or take a default action.
DECLARE
   -- Declare variables and other program elements here
BEGIN
   -- PL/SQL code block
EXCEPTION
   WHEN exception_name1 THEN
      -- Handle exception 1
   WHEN exception_name2 THEN
      -- Handle exception 2
   WHEN OTHERS THEN
      -- Handle any other exception not explicitly caught
END;

EXCEPTION TYPES:

Automatically by Oracle:

  • An exception is automatically raised when an error occurs during the execution of a PL/SQL block.
  • For example, if we try to divide by zero, attempt to fetch a nonexistent record, or encounter a constraint violation, Oracle will raise a predefined exception associated with that error.

Ex: 1

Exception Handling:

Ex: 2

Exception Handling:

Ex: 3

Ex: 4

Ex: 5

DECLARE
   l_emp_id NUMBER;
BEGIN
   SELECT emp_id INTO l_emp_id FROM xx1195_emp WHERE emp_id = 'a1010';
   DBMS_OUTPUT.PUT_LINE('Employee ID is: ' || l_emp_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No employee found for the specified ID.');
   WHEN INVALID_NUMBER THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid value for employee ID.');
END;

NOTE:

  • Now, there’s a special rule called OTHERS that says, “If none of the other rules apply, use me.” But if you put this rule first, it would catch everything, even problems that have their own specific instructions.
  • So, to avoid confusion and make sure specific problems get their specific solutions, the rule is: always put OTHERS last. This way, the program checks the specific rules first, and only if none of them fit, it goes to the catch-all rule (OTHERS).

Explicitly by the Programmer:

  • We can also raise exceptions explicitly in our PL/SQL code using the RAISE statement. This allows us to create and raise custom exceptions based on certain conditions or business logic.
DECLARE
   custom_exception EXCEPTION;
BEGIN
   -- Some condition that requires raising a custom exception
   IF some_condition THEN
      RAISE custom_exception;
   END IF;
   -- Rest of code goes here
   DBMS_OUTPUT.PUT_LINE('No custom exception raised.');
EXCEPTION
   WHEN custom_exception THEN
      DBMS_OUTPUT.PUT_LINE('Custom exception was raised!');
END;

Ex: 1

DECLARE
   user_age NUMBER := 15;
   age_limit NUMBER := 18;
   underage_exception EXCEPTION;
BEGIN
   -- Checking if the user's age is below the limit
   IF user_age < age_limit THEN
      RAISE underage_exception;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Welcome! You are old enough.');
   END IF;
EXCEPTION
   WHEN underage_exception THEN
      DBMS_OUTPUT.PUT_LINE('Sorry, you are too young to proceed.');
END;

Ex: 2

DECLARE
   score NUMBER := 20;
   passing_score NUMBER := 35;
   not_passing_exception EXCEPTION;
BEGIN
   IF score < passing_score THEN
      RAISE not_passing_exception;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Congratulations! You passed with a score of ' || score);
   END IF;
EXCEPTION
   WHEN not_passing_exception THEN
      DBMS_OUTPUT.PUT_LINE('Sorry, you did not pass. Please try again.');
END;

PRAGMA EXCEPTION_INIT:

  • PRAGMA EXCEPTION_INIT is used to associate a user-defined exception with a specific Oracle error code.
  • This is particularly useful when we want to handle a specific Oracle error in a more customized way.
PRAGMA EXCEPTION_INIT (custom_exception_name, oracle_error_code);
DECLARE
   l_emp_id NUMBER;
   custom_exception EXCEPTION;
   PRAGMA EXCEPTION_INIT(custom_exception, -1422);
BEGIN
   -- Try to select emp_id into l_emp_id
   SELECT emp_id INTO l_emp_id FROM xx1195_emp;
   -- If the SELECT is successful, print the employee ID
   DBMS_OUTPUT.PUT_LINE('Employee ID is: ' || l_emp_id);
EXCEPTION
   WHEN custom_exception THEN
      -- Handle the custom exception when too many rows are found
      DBMS_OUTPUT.PUT_LINE('There are too many rows found');
END;
  • Here we use PRAGMA EXCEPTION_INIT to associate the custom_exception with the Oracle error code -1422. The negative sign indicates 0.

Ex: 1

DECLARE
   l_emp_id NUMBER;
   many_rows_exception EXCEPTION;
   PRAGMA EXCEPTION_INIT(many_rows_exception, -1422);
   no_data_exception EXCEPTION;
   PRAGMA EXCEPTION_INIT(no_data_exception, 100);
BEGIN
   SELECT emp_id INTO l_emp_id FROM xx1195_emp where emp_id=1010;
   DBMS_OUTPUT.PUT_LINE('Employee ID is: ' || l_emp_id);
EXCEPTION
   WHEN many_rows_exception THEN
      DBMS_OUTPUT.PUT_LINE('There are too many rows found');
   WHEN no_data_exception THEN
      DBMS_OUTPUT.PUT_LINE('There is no data found');
END;

User Defined Error: RAISE_APPLICATION_ERROR

  • RAISE_APPLICATION_ERROR is a PL/SQL statement used to generate a user-defined exception and associate it with a specific error number and error message.
  • This statement is often used to communicate custom error conditions in a more controlled and informative way.
RAISE_APPLICATION_ERROR(error_code, 'error_message');

Define Custom Error Code:

-20001, -20002, ..., -20999
  • Oracle allows us to define custom error codes in the range of -20000 to -20999 using RAISE_APPLICATION_ERROR

Ex: 1

DECLARE
  v_value NUMBER := -5;
BEGIN
  IF v_value < 0 THEN
    RAISE_APPLICATION_ERROR(-20901, 'Value cannot be negative');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Ex: 2

DECLARE
  v_result NUMBER;
BEGIN
  v_result := 10 / 0; 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    RAISE_APPLICATION_ERROR(-20002, 'Cannot divide by zero');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unhandled error: ');
END;

Note:

RAISE_APPLICATION_ERROR Used in two different places

  • Executable section
  • Exception section

SQLCODE & SQLERRM:

  • These functions are useful for capturing and handling errors in PL/SQL code.
  • The SQLCODE provides the numeric error code.
  • and SQLERRM provides a human-readable error message associated with the error.

Ex: 1

DECLARE
  v_result NUMBER;
BEGIN
  v_result := 10 / 0; 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;

Ex: 2

DECLARE
  v_value NUMBER := -5;
BEGIN
  IF v_value < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Value cannot be negative');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;

Granting Access to a Table in Another Schema:

  • If you have two schemas, schemaA and schemaB, and you have a table called emp_table in schemaA but not in schemaB, and you want to grant access to schemaB to use emp_table, you can follow these steps:
  • In schemaA:
GRANT ALL ON emp_table TO schemaB;
  • In schemaB:
CREATE SYNONYM emp_table FOR schemaA.emp_table;
  • A synonym in a database is an alias or alternate name for a table, view, sequence, or other database object.

Leave a comment

Design a site like this with WordPress.com
Get started