Oracle Procedures 50 Examples

Basic Procedures

1. Simple Procedure

Description: This procedure prints a basic greeting using DBMS_OUTPUT.

   CREATE OR REPLACE PROCEDURE say_hello IS
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello, World!');
   END;
   
   
   BEGIN
  say_hello;
  END;
  /

2. Procedure with IN Parameter

Description: This procedure accepts a name and prints a personalized greeting.

   CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
   END;

3. Procedure with OUT Parameter

Description: This procedure fetches an employee name and returns it through an OUT parameter.

   CREATE OR REPLACE PROCEDURE get_employee_name(p_emp_id IN NUMBER, p_emp_name OUT VARCHAR2) IS
   BEGIN
     SELECT employee_name INTO p_emp_name FROM employees WHERE employee_id = p_emp_id;
   END;

4. Procedure with IN OUT Parameter

Description: This procedure updates a salary value and changes the input number at the same time.

   CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_increase IN OUT NUMBER) IS
   BEGIN
     UPDATE employees SET salary = salary + p_increase WHERE employee_id = p_emp_id;
     p_increase := p_increase * 2;
   END;

5. Procedure with Default Parameter

Description: This procedure uses a default salary value when one is not provided.

   CREATE OR REPLACE PROCEDURE add_employee(p_name IN VARCHAR2, p_salary IN NUMBER DEFAULT 50000) IS
   BEGIN
     INSERT INTO employees (employee_name, salary) VALUES (p_name, p_salary);
   END;

6. Procedure with Exception Handling

Description: This procedure handles a missing employee case with exception handling.

   CREATE OR REPLACE PROCEDURE safe_delete_employee(p_emp_id IN NUMBER) IS
   BEGIN
     DELETE FROM employees WHERE employee_id = p_emp_id;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('Employee not found.');
   END;

7. Procedure to Count Rows

Description: This procedure returns the total number of rows in the employees table.

   CREATE OR REPLACE PROCEDURE count_employees(p_count OUT NUMBER) IS
   BEGIN
     SELECT COUNT(*) INTO p_count FROM employees;
   END;

8. Procedure to Create a Table

Description: This procedure creates a table dynamically using EXECUTE IMMEDIATE.

   CREATE OR REPLACE PROCEDURE create_new_table(p_table_name IN VARCHAR2) IS
   BEGIN
     EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name || ' (id NUMBER, name VARCHAR2(50))';
   END;

9. Procedure to Drop a Table

Description: This procedure drops a table dynamically using EXECUTE IMMEDIATE.

   CREATE OR REPLACE PROCEDURE drop_table(p_table_name IN VARCHAR2) IS
   BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
   END;

10. Procedure to Merge Data

Description: This procedure merges source data into the employees table.

    CREATE OR REPLACE PROCEDURE merge_employees(p_source IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'MERGE INTO employees e USING ' || p_source || ' s ON (e.employee_id = s.employee_id) 
      WHEN MATCHED THEN UPDATE SET e.salary = s.salary 
      WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (s.employee_id, s.employee_name, s.salary)';
    END;

11. Procedure to Insert Multiple Rows

Description: This procedure inserts multiple employee rows inside a loop.

    CREATE OR REPLACE PROCEDURE insert_multiple_employees IS
    BEGIN
      FOR i IN 1..10 LOOP
        INSERT INTO employees (employee_name, salary) VALUES ('Employee ' || i, 30000 + i * 1000);
      END LOOP;
    END;

12. Procedure to Update Records Using a Cursor

Description: This procedure updates employee salaries by looping through a cursor.

    CREATE OR REPLACE PROCEDURE update_all_salaries(p_increment IN NUMBER) IS
      CURSOR emp_cursor IS
        SELECT employee_id FROM employees;
    BEGIN
      FOR emp_rec IN emp_cursor LOOP
        UPDATE employees SET salary = salary + p_increment WHERE employee_id = emp_rec.employee_id;
      END LOOP;
    END;

13. Procedure with Dynamic SQL

Description: This procedure builds a SQL statement dynamically and executes it.

    CREATE OR REPLACE PROCEDURE dynamic_select(p_table_name IN VARCHAR2) IS
      v_query VARCHAR2(1000);
    BEGIN
      v_query := 'SELECT COUNT(*) FROM ' || p_table_name;
      EXECUTE IMMEDIATE v_query;
    END;

14. Procedure to Handle Transactions

Description: This procedure demonstrates COMMIT and ROLLBACK inside exception handling.

    CREATE OR REPLACE PROCEDURE handle_transaction IS
    BEGIN
      INSERT INTO employees (employee_name, salary) VALUES ('John Doe', 60000);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Transaction failed, rolled back.');
    END;

15. Procedure to Use a Collection

Description: This procedure fetches rows through a collection-like workflow.

    CREATE OR REPLACE PROCEDURE insert_collection(p_employees SYS_REFCURSOR) IS
      v_employee_record employees%ROWTYPE;
    BEGIN
      LOOP
        FETCH p_employees INTO v_employee_record;
        EXIT WHEN p_employees%NOTFOUND;
        INSERT INTO employees (employee_id, employee_name, salary)
        VALUES (v_employee_record.employee_id, v_employee_record.employee_name, v_employee_record.salary);
      END LOOP;
    END;

16. Procedure with PL/SQL Table

Description: This procedure uses a PL/SQL table to store employee IDs.

    CREATE OR REPLACE PROCEDURE process_employee_list IS
      TYPE emp_table IS TABLE OF employees.employee_id%TYPE;
      v_emp_ids emp_table;
    BEGIN
      v_emp_ids := emp_table(101, 102, 103);
      FOR i IN v_emp_ids.FIRST..v_emp_ids.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Processing employee ID: ' || v_emp_ids(i));
      END LOOP;
    END;

17. Procedure to Retrieve Data into a Cursor Variable

Description: This procedure opens a SYS_REFCURSOR for employee data.

    CREATE OR REPLACE PROCEDURE get_employee_data(p_cursor OUT SYS_REFCURSOR) IS
    BEGIN
      OPEN p_cursor FOR
        SELECT employee_id, employee_name FROM employees;
    END;

18. Procedure to Count Rows Based on Criteria

Description: This procedure counts employees whose salary is above a threshold.

    CREATE OR REPLACE PROCEDURE count_high_salary_employees(p_threshold IN NUMBER, p_count OUT NUMBER) IS
    BEGIN
      SELECT COUNT(*) INTO p_count FROM employees WHERE salary > p_threshold;
    END;

19. Procedure to Backup a Table

Description: This procedure creates a backup table from an existing table.

    CREATE OR REPLACE PROCEDURE backup_table(p_table_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name || '_backup AS SELECT * FROM ' || p_table_name;
    END;

20. Procedure to Drop a Column

Description: This procedure removes a column using dynamic SQL.

    CREATE OR REPLACE PROCEDURE drop_column(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DROP COLUMN ' || p_column_name;
    END;

21. Procedure to Add a Column

Description: This procedure adds a new column dynamically.

    CREATE OR REPLACE PROCEDURE add_column(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2, p_data_type IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' ADD (' || p_column_name || ' ' || p_data_type || ')';
    END;

22. Procedure to Use Bulk Collect

Description: This procedure bulk collects employee rows into a collection.

    CREATE OR REPLACE PROCEDURE bulk_collect_example IS
      TYPE emp_table IS TABLE OF employees%ROWTYPE;
      v_employees emp_table;
    BEGIN
      SELECT * BULK COLLECT INTO v_employees FROM employees;
      FOR i IN v_employees.FIRST..v_employees.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employees(i).employee_id);
      END LOOP;
    END;

23. Procedure to Create a Synonym

Description: This procedure creates a synonym for a table.

    CREATE OR REPLACE PROCEDURE create_synonym(p_synonym_name IN VARCHAR2, p_table_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE SYNONYM ' || p_synonym_name || ' FOR ' || p_table_name;
    END;

24. Procedure to Drop a Synonym

Description: This procedure drops an existing synonym.

    CREATE OR REPLACE PROCEDURE drop_synonym(p_synonym_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'DROP SYNONYM ' || p_synonym_name;
    END;

25. Procedure to Find Duplicates

Description: This procedure finds duplicate employee names.

    CREATE OR REPLACE PROCEDURE find_duplicates IS
    BEGIN
      FOR rec IN (SELECT employee_name, COUNT(*) FROM employees GROUP BY employee_name HAVING COUNT(*) > 1) LOOP
        DBMS_OUTPUT.PUT_LINE('Duplicate Name: ' || rec.employee_name);
      END LOOP;
    END;

26. Procedure with Recursive Cursor

Description: This procedure lists managers and their subordinates using nested cursors.

    CREATE OR REPLACE PROCEDURE recursive_cursor_example IS
      CURSOR c_recursive IS
        SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL;
      CURSOR c_subordinates(p_manager_id IN NUMBER) IS
        SELECT employee_id, employee_name FROM employees WHERE manager_id = p_manager_id;
    BEGIN
      FOR rec IN c_recursive LOOP
        DBMS_OUTPUT.PUT_LINE('Manager ID: ' || rec.employee_id);
        FOR sub_rec IN c_subordinates(rec.employee_id) LOOP
          DBMS_OUTPUT.PUT_LINE('  Subordinate: ' || sub_rec.employee_name);
        END LOOP;


      END LOOP;
    END;

27. Procedure to Find the Highest Salary

Description: This procedure returns the maximum salary value.

    CREATE OR REPLACE PROCEDURE find_highest_salary(p_highest_salary OUT NUMBER) IS
    BEGIN
      SELECT MAX(salary) INTO p_highest_salary FROM employees;
    END;

28. Procedure with CLOB Data

Description: This procedure inserts CLOB content into a table.

    CREATE OR REPLACE PROCEDURE insert_clob_data(p_id IN NUMBER, p_data IN CLOB) IS
    BEGIN
      INSERT INTO clob_table (id, data) VALUES (p_id, p_data);
    END;

29. Procedure with BLOB Data

Description: This procedure inserts BLOB content into a table.

    CREATE OR REPLACE PROCEDURE insert_blob_data(p_id IN NUMBER, p_data IN BLOB) IS
    BEGIN
      INSERT INTO blob_table (id, data) VALUES (p_id, p_data);
    END;

30. Procedure to Check Constraints

Description: This procedure displays constraint names for a table.

    CREATE OR REPLACE PROCEDURE check_constraints(p_table_name IN VARCHAR2) IS
      v_constraints VARCHAR2(4000);
    BEGIN
      SELECT constraint_name INTO v_constraints FROM user_constraints WHERE table_name = p_table_name;
      DBMS_OUTPUT.PUT_LINE('Constraints on table ' || p_table_name || ': ' || v_constraints);
    END;

31. Procedure to Reset Sequence

Description: This procedure resets a sequence to start from 1.

    CREATE OR REPLACE PROCEDURE reset_sequence(p_seq_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' RESTART START WITH 1';
    END;

32. Procedure with a User-Defined Exception

Description: This procedure raises and handles a custom exception for low salary.

    CREATE OR REPLACE PROCEDURE check_salary(p_salary IN NUMBER) IS
      salary_too_low EXCEPTION;
    BEGIN
      IF p_salary < 20000 THEN
        RAISE salary_too_low;
      END IF;
    EXCEPTION
      WHEN salary_too_low THEN
        DBMS_OUTPUT.PUT_LINE('Salary is too low.');
    END;

33. Procedure to Call Another Procedure

Description: This procedure calls another stored procedure.

    CREATE OR REPLACE PROCEDURE call_another_procedure IS
    BEGIN
      say_hello; -- Calling the procedure defined earlier
    END;

34. Procedure with a Table Parameter

Description: This procedure processes rows passed through a table-like input.

    CREATE OR REPLACE PROCEDURE update_employee_table(p_emp_table IN SYS_REFCURSOR) IS
      v_emp_record employees%ROWTYPE;
    BEGIN
      LOOP
        FETCH p_emp_table INTO v_emp_record;
        EXIT WHEN p_emp_table%NOTFOUND;
        UPDATE employees SET salary = salary + 1000 WHERE employee_id = v_emp_record.employee_id;
      END LOOP;
    END;

35. Procedure to List All Procedures

Description: This procedure lists procedures available in the schema.

    CREATE OR REPLACE PROCEDURE list_all_procedures IS
    BEGIN
      FOR rec IN (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE') LOOP
        DBMS_OUTPUT.PUT_LINE('Procedure Name: ' || rec.object_name);
      END LOOP;
    END;

36. Procedure with FORALL and Bulk Collect

Description: This procedure bulk collects rows and updates them using FORALL.

    CREATE OR REPLACE PROCEDURE bulk_collect_forall IS
      TYPE emp_type IS TABLE OF employees%ROWTYPE;
      v_employees emp_type;
    BEGIN
      SELECT * BULK COLLECT INTO v_employees FROM employees;
      FORALL i IN v_employees.FIRST..v_employees.LAST
        UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_employees(i).employee_id;
    END;

37. Procedure to Generate a Report

Description: This procedure builds a report creation statement dynamically.

    CREATE OR REPLACE PROCEDURE generate_report(p_report_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE REPORT ' || p_report_name || ' AS SELECT * FROM employees';
    END;

38. Procedure to Add a Constraint

Description: This procedure adds a unique constraint dynamically.

    CREATE OR REPLACE PROCEDURE add_unique_constraint(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' ADD CONSTRAINT unique_' || p_column_name || ' UNIQUE (' || p_column_name || ')';
    END;

39. Procedure to Remove a Constraint

Description: This procedure drops a named constraint.

    CREATE OR REPLACE PROCEDURE remove_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DROP CONSTRAINT ' || p_constraint_name;
    END;

40. Procedure to Copy Data Between Tables

Description: This procedure copies data from one table to another.

    CREATE OR REPLACE PROCEDURE copy_data(p_source_table IN VARCHAR2, p_dest_table IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || p_dest_table || ' SELECT * FROM ' || p_source_table;
    END;

41. Procedure to Export Data to File

Description: This procedure demonstrates a file-oriented export workflow.

    CREATE OR REPLACE PROCEDURE export_data(p_query IN VARCHAR2, p_file_path IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'SPOOL ' || p_file_path;
      EXECUTE IMMEDIATE p_query;
      EXECUTE IMMEDIATE 'SPOOL OFF';
    END;

42. Procedure to Generate a Sequence Value

Description: This procedure retrieves the next sequence value.

    CREATE OR REPLACE PROCEDURE generate_sequence_value(p_seq_name IN VARCHAR2, p_value OUT NUMBER) IS
    BEGIN
      SELECT "seq".NEXTVAL INTO p_value FROM dual;
    END;

43. Procedure with an Implicit Cursor

Description: This procedure loops through query results using an implicit cursor.

    CREATE OR REPLACE PROCEDURE implicit_cursor_example IS
    BEGIN
      FOR rec IN (SELECT * FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || ' Name: ' || rec.employee_name);
      END LOOP;
    END;

44. Procedure to Create a View

Description: This procedure creates a view dynamically.

    CREATE OR REPLACE PROCEDURE create_view(p_view_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE VIEW ' || p_view_name || ' AS SELECT * FROM employees';
    END;

45. Procedure to Drop a View

Description: This procedure drops a view dynamically.

    CREATE OR REPLACE PROCEDURE drop_view(p_view_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'DROP VIEW ' || p_view_name;
    END;

46. Procedure to Check Table Existence

Description: This procedure checks whether a table exists in the schema.

    CREATE OR REPLACE PROCEDURE check_table_exists(p_table_name IN VARCHAR2) IS
      v_exists NUMBER;
    BEGIN
      SELECT COUNT(*) INTO v_exists FROM user_tables WHERE table_name = p_table_name;
      IF v_exists > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Table exists.');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Table does not exist.');
      END IF;
    END;

47. Procedure to Handle Large Objects

Description: This procedure demonstrates working with large objects.

    CREATE OR REPLACE PROCEDURE handle_large_object(p_blob BLOB) IS
    BEGIN
      -- Example of handling large objects
      DBMS_LOB.CREATETEMPORARY(p_blob, TRUE);
    END;

48. Procedure to Create an Index

Description: This procedure creates an index dynamically.

    CREATE OR REPLACE PROCEDURE create_index(p_index_name IN VARCHAR2, p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX ' || p_index_name || ' ON ' || p_table_name || ' (' || p_column_name || ')';
    END;

49. Procedure to Drop an Index

Description: This procedure drops an index dynamically.

    CREATE OR REPLACE PROCEDURE drop_index(p_index_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'DROP INDEX ' || p_index_name;
    END;

50. Procedure to Create a Trigger

Description: This procedure creates a trigger dynamically.

    CREATE OR REPLACE PROCEDURE create_trigger(p_trigger_name IN VARCHAR2) IS
    BEGIN
      EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ' || p_trigger_name || ' BEFORE INSERT ON employees FOR EACH ROW BEGIN NULL; END;';
    END;

---

Note: Some examples are illustrative and may need small adjustments before running in a real Oracle environment.

Oracle Procedure 100 Examples

1. Simple Procedure with No Parameters

Description: This procedure prints a simple greeting using DBMS_OUTPUT.

CREATE OR REPLACE PROCEDURE greet
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END greet;
/

2. Procedure with IN Parameter

Description: This procedure accepts a name and prints a personalized greeting.

CREATE OR REPLACE PROCEDURE greet_name(p_name IN VARCHAR2)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_name;
/

3. Procedure with IN and OUT Parameters

Description: This procedure adds two numbers and returns the result through an OUT parameter.

CREATE OR REPLACE PROCEDURE add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := p_num1 + p_num2;
END add_numbers;
/

4. Procedure with IN, OUT, and IN OUT Parameters

Description: This procedure calculates area and also updates the unit text.

CREATE OR REPLACE PROCEDURE calculate_area(p_length IN NUMBER, p_width IN NUMBER, p_area OUT NUMBER, p_unit IN OUT VARCHAR2)
AS
BEGIN
  p_area := p_length * p_width;
  p_unit := 'square ' || p_unit;
END calculate_area;
/

5. Procedure with Default Parameter Values

Description: This procedure uses a default value when no name is provided.

CREATE OR REPLACE PROCEDURE greet_with_default(p_name IN VARCHAR2 DEFAULT 'Guest')
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_with_default;
/

6. Procedure with Named Notation

Description: This procedure multiplies two numbers and returns the product.

CREATE OR REPLACE PROCEDURE multiply_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER, p_product OUT NUMBER)
AS
BEGIN
  p_product := p_num1 * p_num2;
END multiply_numbers;
/

7. Procedure with Positional Notation

Description: This anonymous block shows a procedure call using positional arguments.

BEGIN
  multiply_numbers(5, 10, :product);
END;
/

8. Procedure to Insert into a Table

Description: This procedure inserts a new employee row into the employees table.

CREATE OR REPLACE PROCEDURE insert_employee(p_emp_id IN NUMBER, p_emp_name IN VARCHAR2)
AS
BEGIN
  INSERT INTO employees(emp_id, emp_name) VALUES (p_emp_id, p_emp_name);
END insert_employee;
/

9. Procedure to Update a Table

Description: This procedure updates an employee name for a matching employee ID.

CREATE OR REPLACE PROCEDURE update_employee_name(p_emp_id IN NUMBER, p_emp_name IN VARCHAR2)
AS
BEGIN
  UPDATE employees SET emp_name = p_emp_name WHERE emp_id = p_emp_id;
END update_employee_name;
/

10. Procedure to Delete from a Table

Description: This procedure deletes an employee row based on employee ID.

CREATE OR REPLACE PROCEDURE delete_employee(p_emp_id IN NUMBER)
AS
BEGIN
  DELETE FROM employees WHERE emp_id = p_emp_id;
END delete_employee;
/

11. Procedure with Exception Handling

Description: This procedure handles division-by-zero errors gracefully.

CREATE OR REPLACE PROCEDURE safe_divide(p_num1 IN NUMBER, p_num2 IN NUMBER, p_result OUT NUMBER)
AS
BEGIN
  p_result := p_num1 / p_num2;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END safe_divide;
/

12. Procedure with CURSOR FOR LOOP

Description: This procedure loops through employees using a cursor FOR loop and prints each row.

CREATE OR REPLACE PROCEDURE list_employees
AS
  CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
  END LOOP;
END list_employees;
/

13. Procedure with Explicit Cursor

Description: This procedure fetches one employee name using an explicit cursor.

CREATE OR REPLACE PROCEDURE fetch_employee(p_emp_id IN NUMBER, p_emp_name OUT VARCHAR2)
AS
  CURSOR emp_cursor IS SELECT emp_name FROM employees WHERE emp_id = p_emp_id;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO emp_record;
  p_emp_name := emp_record.emp_name;
  CLOSE emp_cursor;
END fetch_employee;
/

14. Procedure with Cursor and Parameters

Description: This procedure lists employees for a given department.

CREATE OR REPLACE PROCEDURE fetch_employee_by_dept(p_dept_id IN NUMBER)
AS
  CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees WHERE dept_id = p_dept_id;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
  END LOOP;
END fetch_employee_by_dept;
/

15. Procedure with Dynamic SQL

Description: This procedure builds SQL dynamically to count rows in a given table.

CREATE OR REPLACE PROCEDURE execute_dynamic_sql(p_table_name IN VARCHAR2)
AS
  l_sql VARCHAR2(200);
BEGIN
  l_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
  EXECUTE IMMEDIATE l_sql INTO :row_count;
END execute_dynamic_sql;
/

16. Procedure to Create a Table Dynamically

Description: This procedure creates a table using dynamic SQL.

CREATE OR REPLACE PROCEDURE create_table_dynamic(p_table_name IN VARCHAR2)
AS
  l_sql VARCHAR2(200);
BEGIN
  l_sql := 'CREATE TABLE ' || p_table_name || ' (id NUMBER, name VARCHAR2(50))';
  EXECUTE IMMEDIATE l_sql;
END create_table_dynamic;
/

17. Procedure with Bulk Collect

Description: This procedure bulk collects employee IDs into a collection.

CREATE OR REPLACE PROCEDURE bulk_collect_employees(p_emp_ids OUT SYS.ODCINUMBERLIST)
AS
  TYPE emp_table IS TABLE OF employees.emp_id%TYPE;
  l_emp_ids emp_table;
BEGIN
  SELECT emp_id BULK COLLECT INTO l_emp_ids FROM employees;
  p_emp_ids := l_emp_ids;
END bulk_collect_employees;
/

18. Procedure to Handle Null Values

Description: This procedure checks whether a value is null and prints the result.

CREATE OR REPLACE PROCEDURE check_null_value(p_value IN VARCHAR2)
AS
BEGIN
  IF p_value IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Value is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Value: ' || p_value);
  END IF;
END check_null_value;
/

19. Procedure to Reverse a String

Description: This procedure reverses the input string and returns the reversed value.

CREATE OR REPLACE PROCEDURE reverse_string(p_input IN VARCHAR2, p_output OUT VARCHAR2)
AS
BEGIN
  p_output := REVERSE(p_input);
END reverse_string;
/

20. Procedure to Generate Fibonacci Sequence

Description: This procedure generates a Fibonacci sequence up to the requested count.

CREATE OR REPLACE PROCEDURE fibonacci_sequence(p_n IN NUMBER, p_result OUT VARCHAR2)
AS
  v_a NUMBER := 0;
  v_b NUMBER := 1;
  v_fib NUMBER;
  v_seq VARCHAR2(100) := '0, 1';
BEGIN
  FOR i IN 3..p_n LOOP
    v_fib := v_a + v_b;
    v_seq := v_seq || ', ' || v_fib;
    v_a := v_b;
    v_b := v_fib;
  END LOOP;
  p_result := v_seq;
END fibonacci_sequence;
/

21. Procedure to Calculate Factorial

Description: This procedure calculates factorial using a loop.

CREATE OR REPLACE PROCEDURE calculate_factorial(p_num IN NUMBER, p_fact OUT NUMBER)
AS
  v_fact NUMBER := 1;
BEGIN
  FOR i IN 1..p_num LOOP
    v_fact := v_fact * i;
  END LOOP;
  p_fact := v_fact;
END calculate_factorial;
/

22. Procedure with PL/SQL Table

Description: This procedure demonstrates a PL/SQL associative array.

CREATE OR REPLACE PROCEDURE process_table
AS
  TYPE num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_nums num_table;
BEGIN
  v_nums(1) := 10;
  v_nums(2) := 20;
  v_nums(3) := 30;
  FOR i IN 1..v_nums.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || v_nums(i));
  END LOOP;
END process_table;
/

23. Procedure to Calculate Sum of Array Elements

Description: This procedure sums all numbers in a numeric list.

CREATE OR REPLACE PROCEDURE sum_array_elements(p_nums IN SYS.ODCINUMBERLIST, p_sum OUT NUMBER)
AS
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 1..p_nums.COUNT LOOP
    v_sum := v_sum + p_nums(i);
  END LOOP;
  p_sum := v_sum;
END sum_array_elements;
/

24. Procedure to Find Maximum Value in Array

Description: This procedure finds the largest number in a numeric list.

CREATE OR REPLACE PROCEDURE find_max_in_array(p_nums IN SYS.ODCINUMBERLIST, p_max OUT NUMBER)
AS
  v_max NUMBER := p_nums(1);
BEGIN
  FOR i IN 2..p_nums.COUNT LOOP
    IF p_nums(i) > v_max THEN
      v_max := p_nums(i);
    END IF;
  END LOOP;
  p_max := v_max;
END find_max_in_array;
/

25. Procedure to Sort Array

Description: This procedure sorts a numeric list in ascending order.

CREATE OR REPLACE PROCEDURE sort_array(p_nums IN OUT SYS.ODCINUMBERLIST)
AS
BEGIN
  FOR i IN 1..p_nums.COUNT LOOP
    FOR j IN i+1..p_nums.COUNT LOOP
      IF p_nums(i) > p_nums(j) THEN
        p_nums(i) := p_nums(i) + p_nums(j);
        p_nums(j) := p_nums(i) - p_nums(j);
        p_nums(i) := p_nums(i) - p_nums(j);
      END IF;
    END LOOP;
  END LOOP;
END sort_array;
/

26. Procedure to Count Words in a String

Description: This procedure counts words using a regular expression.

CREATE OR REPLACE PROCEDURE count_words(p_str IN VARCHAR2, p_word_count OUT NUMBER)
AS
BEGIN
  p_word_count := REGEXP_COUNT(p_str, '\S+');
END count_words;
/

27. Procedure to Reverse an Array

Description: This procedure reverses the order of numbers in a list.

CREATE OR REPLACE PROCEDURE reverse_array(p_nums IN OUT SYS.ODCINUMBERLIST)
AS
  v_temp NUMBER;
BEGIN
  FOR i IN 1..p_nums.COUNT/2 LOOP
    v_temp := p_nums(i);
    p_nums(i) := p_nums(p_nums.COUNT - i + 1);
    p_nums(p_nums.COUNT - i + 1) := v_temp;
  END LOOP;
END reverse_array;
/

28. Procedure to Calculate GCD

Description: This procedure computes the greatest common divisor using repeated modulo.

CREATE OR REPLACE PROCEDURE calculate_gcd(p_num1 IN NUMBER, p_num2 IN NUMBER, p_gcd OUT NUMBER)
AS
BEGIN
  WHILE p_num2 != 0 LOOP
    p_gcd := p_num1 MOD p_num2;
    p_num1 := p_num2;
    p_num2 := p_gcd;
  END LOOP;
  p_gcd := p_num1;
END calculate_gcd;
/

29. Procedure to Calculate LCM

Description: This procedure calculates the least common multiple using the GCD.

CREATE OR REPLACE PROCEDURE calculate_lcm(p_num1 IN NUMBER, p_num2 IN NUMBER, p_lcm OUT NUMBER)
AS
  v_gcd NUMBER;
BEGIN
  calculate_gcd(p_num1, p_num2, v_gcd);
  p_lcm := (p_num1 * p_num2) / v_gcd;
END calculate_lcm;
/

30. Procedure to Generate Prime Numbers

Description: This procedure builds a list of prime numbers up to a requested limit.

CREATE OR REPLACE PROCEDURE generate_primes(p_n IN NUMBER, p_primes OUT SYS.ODCINUMBERLIST)
AS
  v_count NUMBER := 0;
BEGIN
  FOR i IN 2..p_n LOOP
    IF MOD(i, 2) = 0 THEN
      CONTINUE;
    END IF;
    v_count := 0;
    FOR j IN 2..SQRT(i) LOOP
      IF MOD(i, j) = 0 THEN
        v_count := 1;
        EXIT;
      END IF;
    END LOOP;
    IF v_count = 0 THEN
      p_primes.EXTEND;
      p_primes(p_primes.COUNT) := i;
    END IF;
  END LOOP;
END generate_primes;
/

31. Procedure to Convert Celsius to Fahrenheit

Description: This procedure converts temperature from Celsius to Fahrenheit.

CREATE OR REPLACE PROCEDURE celsius_to_fahrenheit(p_celsius IN NUMBER, p_fahrenheit OUT NUMBER)
AS
BEGIN
  p_fahrenheit := (p_celsius * 9/5) + 32;
END celsius_to_fahrenheit;
/

32. Procedure to Convert Fahrenheit to Celsius

Description: This procedure converts temperature from Fahrenheit to Celsius.

CREATE OR REPLACE PROCEDURE fahrenheit_to_celsius(p_fahrenheit IN NUMBER, p_celsius OUT NUMBER)
AS
BEGIN
  p_celsius := (p_fahrenheit - 32) * 5/9;
END fahrenheit_to_celsius;
/

33. Procedure to Find Sum of Digits

Description: This procedure adds the digits of a number.

CREATE OR REPLACE PROCEDURE sum_of_digits(p_num IN NUMBER, p_sum OUT NUMBER)
AS
  v_digit NUMBER;
BEGIN
  p_sum := 0;
  WHILE p_num > 0 LOOP
    v_digit := p_num MOD 10;
    p_sum := p_sum + v_digit;
    p_num := p_num / 10;
  END LOOP;
END sum_of_digits;
/

34. Procedure to Find Product of Digits

Description: This procedure multiplies the digits of a number.

CREATE OR REPLACE PROCEDURE product_of_digits(p_num IN NUMBER, p_product OUT NUMBER)
AS
  v_digit NUMBER;
BEGIN
  p_product := 1;
  WHILE p_num > 0 LOOP
    v_digit := p_num MOD 10;
    p_product := p_product * v_digit;
    p_num := p_num / 10;
  END LOOP;
END product_of_digits;
/

35. Procedure to Find the Nth Fibonacci Number

Description: This procedure returns a single Fibonacci value for the requested position.

CREATE OR REPLACE PROCEDURE nth_fibonacci(p_n IN NUMBER, p_fib OUT NUMBER)
AS
  v_a NUMBER := 0;
  v_b NUMBER := 1;
  v_temp NUMBER;
BEGIN
  IF p_n = 1 THEN
    p_fib := v_a;
  ELSIF p_n = 2 THEN
    p_fib := v_b;
  ELSE
    FOR i IN 3..p_n LOOP
      v_temp := v_a + v_b;
      v_a := v_b;
      v_b := v_temp;
    END LOOP;
    p_fib := v_b;
  END IF;
END nth_fibonacci;
/

36. Procedure to Reverse a Number

Description: This procedure reverses the digits of a number.

CREATE OR REPLACE PROCEDURE reverse_number(p_num IN NUMBER, p_reverse OUT NUMBER)
AS
  v_digit NUMBER;
BEGIN
  p_reverse := 0;
  WHILE p_num > 0 LOOP
    v_digit := p_num MOD 10;
    p_reverse := (p_reverse * 10) + v_digit;
    p_num := p_num / 10;
  END LOOP;
END reverse_number;
/

37. Procedure to Check Palindrome Number

Description: This procedure checks whether a number is the same forwards and backwards.

CREATE OR REPLACE PROCEDURE check_palindrome_number(p_num IN NUMBER, p_is_palindrome OUT BOOLEAN)
AS
  v_reverse NUMBER;
BEGIN
  reverse_number(p_num, v_reverse);
  IF v_reverse = p_num THEN
    p_is_palindrome := TRUE;
  ELSE
    p_is_palindrome := FALSE;
  END IF;
END check_palindrome_number;
/

38. Procedure to Calculate Power of a Number

Description: This procedure raises a number to a given exponent.

CREATE OR REPLACE PROCEDURE power_of_number(p_base IN NUMBER, p_exponent IN NUMBER, p_power OUT NUMBER)
AS
  v_result NUMBER := 1;
BEGIN
  FOR i IN 1..p_exponent LOOP
    v_result := v_result * p_base;
  END LOOP;
  p_power := v_result;
END power_of_number;
/

39. Procedure to Check Armstrong Number

Description: This procedure checks whether the number equals the sum of its digits raised to the digit count.

CREATE OR REPLACE PROCEDURE check_armstrong_number(p_num IN NUMBER, p_is_armstrong OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
  v_digit NUMBER;
  v_temp NUMBER := p_num;
  v_len NUMBER := LENGTH(TO_CHAR(p_num));
BEGIN
  WHILE v_temp > 0 LOOP
    v_digit := v_temp MOD 10;
    v_sum := v_sum + POWER(v_digit, v_len);
    v_temp := v_temp / 10;
  END LOOP;
  IF v_sum = p_num THEN
    p_is_armstrong := TRUE;
  ELSE
    p_is_armstrong := FALSE;
  END IF;
END check_armstrong_number;
/

40. Procedure to Check Perfect Number

Description: This procedure checks whether the sum of proper divisors equals the number.

CREATE OR REPLACE PROCEDURE check_perfect_number(p_num IN NUMBER, p_is_perfect OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 1..p_num/2 LOOP
    IF p_num MOD i = 0 THEN
      v_sum := v_sum + i;
    END IF;
  END LOOP;
  IF v_sum = p_num THEN
    p_is_perfect := TRUE;
  ELSE
    p_is_perfect := FALSE;
  END IF;
END check_perfect_number;
/

41. Procedure to Calculate Simple Interest

Description: This procedure calculates simple interest using principal, rate, and time.

CREATE OR REPLACE PROCEDURE calculate_simple_interest(p_principal IN NUMBER, p_rate IN NUMBER, p_time IN NUMBER, p_si OUT NUMBER)
AS
BEGIN
  p_si := (p_principal * p_rate * p_time) / 100;
END calculate_simple_interest;
/

42. Procedure to Calculate Compound Interest

Description: This procedure calculates compound interest using the power function.

CREATE OR REPLACE PROCEDURE calculate_compound_interest
(p_principal IN NUMBER, p_rate IN NUMBER, p_time IN NUMBER, p_ci OUT NUMBER)
AS
BEGIN
  p_ci := p_principal * POWER((1 + p_rate / 100), p_time) - p_principal;
END calculate_compound_interest;
/

43. Procedure to Calculate Area of Circle

Description: This procedure calculates the area of a circle from the radius.

CREATE OR REPLACE PROCEDURE calculate_area_of_circle(p_radius IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
  p_area := 3.14 * p_radius * p_radius;
END calculate_area_of_circle;
/

44. Procedure to Calculate Perimeter of Circle

Description: This procedure calculates the circumference of a circle.

CREATE OR REPLACE PROCEDURE calculate_perimeter_of_circle(p_radius IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
  p_perimeter := 2 * 3.14 * p_radius;
END calculate_perimeter_of_circle;
/

45. Procedure to Calculate Area of Rectangle

Description: This procedure calculates the area of a rectangle.

CREATE OR REPLACE PROCEDURE calculate_area_of_rectangle(p_length IN NUMBER, p_breadth IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
  p_area := p_length * p_breadth;
END calculate_area_of_rectangle;
/

46. Procedure to Calculate Perimeter of Rectangle

Description: This procedure calculates the perimeter of a rectangle.

CREATE OR REPLACE PROCEDURE calculate_perimeter_of_rectangle(p_length IN NUMBER, p_breadth IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
  p_perimeter := 2 * (p_length + p_breadth);
END calculate_perimeter_of_rectangle;
/

47. Procedure to Calculate Area of Triangle

Description: This procedure calculates the area of a triangle using base and height.

CREATE OR REPLACE PROCEDURE calculate_area_of_triangle(p_base IN NUMBER, p_height IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
  p_area := 0.5 * p_base * p_height;
END calculate_area_of_triangle;
/

48. Procedure to Calculate Perimeter of Triangle

Description: This procedure adds the three sides to compute triangle perimeter.

CREATE OR REPLACE PROCEDURE calculate_perimeter_of_triangle(p_a IN NUMBER, p_b IN NUMBER, p_c IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
  p_perimeter := p_a + p_b + p_c;
END calculate_perimeter_of_triangle;
/

49. Procedure to Calculate Area of Square

Description: This procedure computes the area of a square from one side length.

CREATE OR REPLACE PROCEDURE calculate_area_of_square(p_side IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
  p_area := p_side * p_side;
END calculate_area_of_square;
/

50. Procedure to Calculate Perimeter of Square

Description: This procedure computes the perimeter of a square.

CREATE OR REPLACE PROCEDURE calculate_perimeter_of_square(p_side IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
  p_perimeter := 4 * p_side;
END calculate_perimeter_of_square;
/

51. Procedure to Check Leap Year

Description: This procedure checks whether a year is a leap year.

CREATE OR REPLACE PROCEDURE check_leap_year(p_year IN NUMBER, p_is_leap OUT BOOLEAN)
AS
BEGIN
  IF MOD(p_year, 4) = 0 AND (MOD(p_year, 100) != 0 OR MOD(p_year, 400) = 0) THEN
    p_is_leap := TRUE;
  ELSE
    p_is_leap := FALSE;
  END IF;
END check_leap_year;
/

52. Procedure to Calculate Sum of First N Natural Numbers

Description: This procedure uses the arithmetic series formula to compute the sum.

CREATE OR REPLACE PROCEDURE sum_of_natural_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := (p_n * (p_n + 1)) / 2;
END sum_of_natural_numbers;
/

53. Procedure to Calculate Sum of Squares of First N Natural Numbers

Description: This procedure computes the sum of squares using a closed-form formula.

CREATE OR REPLACE PROCEDURE sum_of_squares(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := (p_n * (p_n + 1) * (2 * p_n + 1)) / 6;
END sum_of_squares;
/

54. Procedure to Calculate Sum of Cubes of First N Natural Numbers

Description: This procedure calculates the sum of cubes using the square-of-sum identity.

CREATE OR REPLACE PROCEDURE sum_of_cubes(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := POWER((p_n * (p_n + 1) / 2), 2);
END sum_of_cubes;
/

55. Procedure to Convert Binary to Decimal

Description: This procedure converts a binary string to its decimal value.

CREATE OR REPLACE PROCEDURE binary_to_decimal(p_binary IN VARCHAR2, p_decimal OUT NUMBER)
AS
  v_len NUMBER := LENGTH(p_binary);
  v_decimal NUMBER := 0;
BEGIN
  FOR i IN 1..v_len LOOP
    IF SUBSTR(p_binary, v_len - i + 1, 1) = '1' THEN
      v_decimal := v_decimal + POWER(2, i-1);
    END IF;
  END LOOP;
  p_decimal := v_decimal;
END binary_to_decimal;
/

56. Procedure to Convert Decimal to Binary

Description: This procedure converts a decimal number to a binary string.

CREATE OR REPLACE PROCEDURE decimal_to_binary(p_decimal IN NUMBER, p_binary OUT VARCHAR2)
AS
  v_remainder NUMBER;
BEGIN
  p_binary := '';
  WHILE p_decimal > 0 LOOP
    v_remainder := MOD(p_decimal, 2);
    p_binary := TO_CHAR(v_remainder) || p_binary;
    p_decimal := p_decimal / 2;
  END LOOP;
END decimal_to_binary;
/

57. Procedure to Check Strong Number

Description: This procedure checks whether a number equals the sum of factorials of its digits.

CREATE OR REPLACE PROCEDURE check_strong_number(p_num IN NUMBER, p_is_strong OUT BOOLEAN)
AS
  v_temp NUMBER := p_num;
  v_sum NUMBER := 0;
  v_fact NUMBER;
  v_digit NUMBER;
BEGIN
  WHILE v_temp > 0 LOOP
    v_digit := v_temp MOD 10;
    v_fact := 1;
    FOR i IN 1..v_digit LOOP
      v_fact := v_fact * i;
    END LOOP;
    v_sum := v_sum + v_fact;
    v_temp := v_temp / 10;
  END LOOP;
  IF v_sum = p_num THEN
    p_is_strong := TRUE;
  ELSE
    p_is_strong := FALSE;
  END IF;
END check_strong_number;
/

58. Procedure to Check Automorphic Number

Description: This procedure checks whether the square ends with the same digits as the number.

CREATE OR REPLACE PROCEDURE check_automorphic_number(p_num IN NUMBER, p_is_automorphic OUT BOOLEAN)
AS
  v_square NUMBER := p_num * p_num;
BEGIN
  IF SUBSTR(TO_CHAR(v_square), -LENGTH(TO_CHAR(p_num))) = TO_CHAR(p_num) THEN
    p_is_automorphic := TRUE;
  ELSE
    p_is_automorphic := FALSE;
  END IF;
END check_automorphic_number;
/

59. Procedure to Check Harshad Number

Description: This procedure checks whether a number is divisible by the sum of its digits.

CREATE OR REPLACE PROCEDURE check_harshad_number(p_num IN NUMBER, p_is_harshad OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
  v_digit NUMBER;
  v_temp NUMBER := p_num;
BEGIN
  WHILE v_temp > 0 LOOP
    v_digit := v_temp MOD 10;
    v_sum := v_sum + v_digit;
    v_temp := v_temp / 10;
  END LOOP;
  IF p_num MOD v_sum = 0 THEN
    p_is_harshad := TRUE;
  ELSE
    p_is_harshad := FALSE;
  END IF;
END check_harshad_number;
/

60. Procedure to Check Narcissistic Number

Description: This procedure checks whether a number equals the sum of its digits raised to digit count.

CREATE OR REPLACE PROCEDURE check_narcissistic_number(p_num IN NUMBER, p_is_narcissistic OUT BOOLEAN)
AS
  v_temp NUMBER := p_num;
  v_sum NUMBER := 0;
  v_digit NUMBER;
  v_len NUMBER := LENGTH(TO_CHAR(p_num));
BEGIN
  WHILE v_temp > 0 LOOP
    v_digit := v_temp MOD 10;
    v_sum := v_sum + POWER(v_digit, v_len);
    v_temp := v_temp / 10;
  END LOOP;
  IF v_sum = p_num THEN
    p_is_narcissistic := TRUE;
  ELSE
    p_is_narcissistic := FALSE;
  END IF;
END check_narcissistic_number;
/

61. Procedure to Check Happy Number

Description: This procedure checks whether repeated digit-square sums eventually reach 1.

CREATE OR REPLACE PROCEDURE check_happy_number(p_num IN NUMBER, p_is_happy OUT BOOLEAN)
AS
  v_sum NUMBER;
  v_digit NUMBER;
BEGIN
  LOOP
    v_sum := 0;
    WHILE p_num > 0 LOOP
      v_digit := p_num MOD 10;
      v_sum := v_sum + (v_digit * v_digit);
      p_num := p_num / 10;
    END LOOP;
    IF v_sum = 1 THEN
      p_is_happy := TRUE;
      EXIT;
    ELSIF v_sum = 4 THEN
      p_is_happy := FALSE;
      EXIT;
    ELSE
      p_num := v_sum;
    END IF;
  END LOOP;
END check_happy_number;
/

62. Procedure to Check Abundant Number

Description: This procedure checks whether the sum of proper divisors is greater than the number itself.

CREATE OR REPLACE PROCEDURE check_abundant_number(p_num IN NUMBER, p_is_abundant OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 1..p_num/2 LOOP
    IF p_num MOD i = 0 THEN
      v_sum := v_sum + i;
    END IF;
  END LOOP;
  IF v_sum > p_num THEN
    p_is_abundant := TRUE;
  ELSE
    p_is_abundant := FALSE;
  END IF;
END check_abundant_number;
/

63. Procedure to Check Perfect Square

Description: This procedure checks whether a number is a perfect square.

CREATE OR REPLACE PROCEDURE check_perfect_square(p_num IN NUMBER, p_is_perfect_square OUT BOOLEAN)
AS
  v_sqrt NUMBER;
BEGIN
  v_sqrt := SQRT(p_num);
  IF v_sqrt = TRUNC(v_sqrt) THEN
    p_is_perfect_square := TRUE;
  ELSE
    p_is_perfect_square := FALSE;
  END IF;
END check_perfect_square;
/

64. Procedure to Check Perfect Cube

Description: This procedure checks whether a number is a perfect cube.

CREATE OR REPLACE PROCEDURE check_perfect_cube(p_num IN NUMBER, p_is_perfect_cube OUT BOOLEAN)
AS
  v_cube_root NUMBER;
BEGIN
  v_cube_root := ROUND(EXP(LN(p_num) / 3));
  IF v_cube_root * v_cube_root * v_cube_root = p_num THEN
    p_is_perfect_cube := TRUE;
  ELSE
    p_is_perfect_cube := FALSE;
  END IF;
END check_perfect_cube;
/

65. Procedure to Check Pythagorean Triplet

Description: This procedure checks whether three numbers satisfy the Pythagorean relation.

CREATE OR REPLACE PROCEDURE check_pythagorean_triplet(p_a IN NUMBER, p_b IN NUMBER, p_c IN NUMBER, p_is_triplet OUT BOOLEAN)
AS
BEGIN
  IF p_a * p_a + p_b * p_b = p_c * p_c THEN
    p_is_triplet := TRUE;
  ELSE
    p_is_triplet := FALSE;
  END IF;
END check_pythagorean_triplet;
/

66. Procedure to Check Amicable Numbers

Description: This procedure checks whether two numbers are amicable.

CREATE OR REPLACE PROCEDURE check_amicable_numbers(p_a IN NUMBER, p_b IN NUMBER, p_are_amicable OUT BOOLEAN)
AS
  v_sum_a NUMBER := 0;
  v_sum_b NUMBER := 0;
BEGIN
  FOR i IN 1..p_a-1 LOOP
    IF p_a MOD i = 0 THEN
      v_sum_a := v_sum_a + i;
    END IF;
  END LOOP;
  FOR i IN 1..p_b-1 LOOP
    IF p_b MOD i = 0 THEN
      v_sum_b := v_sum_b + i;
    END IF;
  END LOOP;
  IF v_sum_a = p_b AND v_sum_b = p_a THEN
    p_are_amicable := TRUE;
  ELSE
    p_are_amicable := FALSE;
  END IF;
END check_amicable_numbers;
/

67. Procedure to Generate Floyd's Triangle

Description: This procedure prints Floyd's triangle row by row.

CREATE OR REPLACE PROCEDURE floyds_triangle(p_rows IN NUMBER)
AS
  v_num NUMBER := 1;
BEGIN
  FOR i IN 1..p_rows LOOP
    FOR j IN 1..i LOOP
      DBMS_OUTPUT.PUT(v_num || ' ');
      v_num := v_num + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END floyds_triangle;
/

68. Procedure to Generate Pascal's Triangle

Description: This procedure prints Pascal's triangle values using array logic.

CREATE OR REPLACE PROCEDURE pascals_triangle(p_rows IN NUMBER)
AS
  TYPE row_array IS VARRAY(100) OF NUMBER;
  v_array row_array;
BEGIN
  v_array := row_array();
  v_array.EXTEND(p_rows + 1);
  v_array(1) := 1;
  FOR i IN 1..p_rows LOOP
    FOR j IN REVERSE 2..i+1 LOOP
      v_array(j) := v_array(j) + v_array(j-1);
    END LOOP;
    FOR j IN 1..i+1 LOOP
      DBMS_OUTPUT.PUT(v_array(j) || ' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END pascals_triangle;
/

69. Procedure to Generate Arithmetic Progression

Description: This procedure creates an arithmetic sequence and stores it in a collection.

CREATE OR REPLACE PROCEDURE generate_arithmetic_progression(p_a IN NUMBER, p_d IN NUMBER, p_n IN NUMBER, p_ap OUT SYS.ODCINUMBERLIST)
AS
  v_term NUMBER;
BEGIN
  FOR i IN 0..p_n-1 LOOP
    v_term := p_a + i * p_d;
    p_ap.EXTEND;
    p_ap(p_ap.COUNT) := v_term;
  END LOOP;
END generate_arithmetic_progression;
/

70. Procedure to Generate Geometric Progression

Description: This procedure creates a geometric sequence and stores it in a collection.

CREATE OR REPLACE PROCEDURE generate_geometric_progression(p_a IN NUMBER, p_r IN NUMBER, p_n IN NUMBER, p_gp OUT SYS.ODCINUMBERLIST)
AS
  v_term NUMBER;
BEGIN
  FOR i IN 0..p_n-1 LOOP
    v_term := p_a * POWER(p_r, i);
    p_gp.EXTEND;
    p_gp(p_gp.COUNT) := v_term;
  END LOOP;
END generate_geometric_progression;
/

71. Procedure to Calculate HCF

Description: This procedure calculates the highest common factor using modulo logic.

CREATE OR REPLACE PROCEDURE calculate_hcf(p_num1 IN NUMBER, p_num2 IN NUMBER, p_hcf OUT NUMBER)
AS
BEGIN
  WHILE p_num2 != 0 LOOP
    p_hcf := p_num1 MOD p_num2;
    p_num1 := p_num2;
    p_num2 := p_hcf;
  END LOOP;
  p_hcf := p_num1;
END calculate_hcf;
/

72. Procedure to Calculate LCM

Description: This procedure calculates the least common multiple using HCF.

CREATE OR REPLACE PROCEDURE calculate_lcm(p_num1 IN NUMBER, p_num2 IN NUMBER, p_lcm OUT NUMBER)
AS
  v_hcf NUMBER;
BEGIN
  calculate_hcf(p_num1, p_num2, v_hcf);
  p_lcm := (p_num1 * p_num2) / v_hcf;
END calculate_lcm;
/

73. Procedure to Print Multiplication Table

Description: This procedure prints a multiplication table for the given number.

CREATE OR REPLACE PROCEDURE print_multiplication_table(p_num IN NUMBER)
AS
BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(p_num || ' * ' || i || ' = ' || p_num * i);
  END LOOP;
END print_multiplication_table;
/

74. Procedure to Calculate Nth Prime Number

Description: This procedure returns the Nth prime number.

CREATE OR REPLACE PROCEDURE nth_prime(p_n IN NUMBER, p_prime OUT NUMBER)
AS
  v_count NUMBER := 0;
  v_num NUMBER := 2;
  v_is_prime BOOLEAN;
BEGIN
  LOOP
    v_is_prime := TRUE;
    FOR i IN 2..SQRT(v_num) LOOP
      IF v_num MOD i = 0 THEN
        v_is_prime := FALSE;
        EXIT;
      END IF;
    END LOOP;
    IF v_is_prime THEN
      v_count := v_count + 1;
    END IF;
    IF v_count = p_n THEN
      p_prime := v_num;
      EXIT;
    END IF;
    v_num := v_num + 1;
  END LOOP;
END nth_prime;
/

75. Procedure to Check Perfect Number in a Range

Description: This procedure collects perfect numbers within a requested range.

CREATE OR REPLACE PROCEDURE check_perfect_number_in_range(p_start IN NUMBER, p_end IN NUMBER, p_result OUT SYS.ODCINUMBERLIST)
AS
  v_sum NUMBER;
BEGIN
  FOR v_num IN p_start..p_end LOOP
    v_sum := 0;
    FOR i IN 1..v_num/2 LOOP
      IF v_num MOD i = 0 THEN
        v_sum := v_sum + i;
      END IF;
    END LOOP;
    IF v_sum = v_num THEN
      p_result.EXTEND;
      p_result(p_result.COUNT) := v_num;
    END IF;
  END LOOP;
END check_perfect_number_in_range;
/

76. Procedure to Check Armstrong Number in a Range

Description: This procedure finds all Armstrong numbers in the given range.

CREATE OR REPLACE PROCEDURE check_armstrong_number_in_range(p_start IN NUMBER, p_end IN NUMBER, p_result OUT SYS.ODCINUMBERLIST)
AS
  v_sum NUMBER;
  v_digit NUMBER;
  v_temp NUMBER;
  v_len NUMBER;
BEGIN
  FOR v_num IN p_start..p_end LOOP
    v_temp := v_num;
    v_sum := 0;
    v_len := LENGTH(TO_CHAR(v_num));
    WHILE v_temp > 0 LOOP
      v_digit := v_temp MOD 10;
      v_sum := v_sum + POWER(v_digit, v_len);
      v_temp := v_temp / 10;
    END LOOP;
    IF v_sum = v_num THEN
      p_result.EXTEND;
      p_result(p_result.COUNT) := v_num;
    END IF;
  END LOOP;
END check_armstrong_number_in_range;
/

77. Procedure to Print Right Triangle Pattern

Description: This procedure prints a right-aligned triangle of asterisks.

CREATE OR REPLACE PROCEDURE print_right_triangle(p_rows IN NUMBER)
AS
BEGIN
  FOR i IN 1..p_rows LOOP
    FOR j IN 1..i LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_right_triangle;
/

78. Procedure to Print Inverted Triangle Pattern

Description: This procedure prints an inverted triangle of asterisks.

CREATE OR REPLACE PROCEDURE print_inverted_triangle(p_rows IN NUMBER)
AS
BEGIN
  FOR i IN REVERSE 1..p_rows LOOP
    FOR j IN 1..i LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_inverted_triangle;
/

79. Procedure to Print Pyramid Pattern

Description: This procedure prints a pyramid star pattern.

CREATE OR REPLACE PROCEDURE print_pyramid(p_rows IN NUMBER)
AS
BEGIN
  FOR i IN 1..p_rows LOOP
    FOR j IN 1..p_rows-i LOOP
      DBMS_OUTPUT.PUT(' ');
    END LOOP;
    FOR j IN 1..(2*i-1) LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_pyramid;
/

80. Procedure to Print Diamond Pattern

Description: This procedure prints a diamond made of asterisks.

CREATE OR REPLACE PROCEDURE print_diamond(p_rows IN NUMBER)
AS
BEGIN
  FOR i IN 1..p_rows LOOP
    FOR j IN 1..p_rows-i LOOP
      DBMS_OUTPUT.PUT(' ');
    END LOOP;
    FOR j IN 1..(2*i-1) LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
  FOR i IN REVERSE 1..p_rows-1 LOOP
    FOR j IN 1..p_rows-i LOOP
      DBMS_OUTPUT.PUT(' ');
    END LOOP;
    FOR j IN 1..(2*i-1) LOOP
      DBMS_OUTPUT.PUT('*');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_diamond;
/

81. Procedure to Check Krishnamurthy Number

Description: This procedure checks whether a number equals the sum of factorials of its digits.

CREATE OR REPLACE PROCEDURE check_krishnamurthy_number(p_num IN NUMBER, p_is_krishnamurthy OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
  v_digit NUMBER;
  v_fact NUMBER;
BEGIN
  FOR i IN 1..LENGTH(TO_CHAR(p_num)) LOOP
    v_digit := p_num MOD 10;
    v_fact := 1;
    FOR j IN 1..v_digit LOOP
      v_fact := v_fact * j;
    END LOOP;
    v_sum := v_sum + v_fact;
    p_num := p_num / 10;
  END LOOP;
  IF v_sum = p_num THEN
    p_is_krishnamurthy := TRUE;
  ELSE
    p_is_krishnamurthy := FALSE;
  END IF;
END check_krishnamurthy_number;
/

82. Procedure to Check Special Number

Description: This procedure checks a number using digit sum and product logic.

CREATE OR REPLACE PROCEDURE check_special_number(p_num IN NUMBER, p_is_special OUT BOOLEAN)
AS
  v_sum NUMBER := 0;
  v_product NUMBER := 1;
  v_digit NUMBER;
BEGIN
  WHILE p_num > 0 LOOP
    v_digit := p_num MOD 10;
    v_sum := v_sum + v_digit;
    v_product := v_product * v_digit;
    p_num := p_num / 10;
  END LOOP;
  IF v_sum + v_product = p_num THEN
    p_is_special := TRUE;
  ELSE
    p_is_special := FALSE;
  END IF;
END check_special_number;
/

83. Procedure to Calculate Sum of Odd Numbers

Description: This procedure calculates the sum of the first N odd numbers.

CREATE OR REPLACE PROCEDURE sum_of_odd_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := 0;
  FOR i IN 1..p_n LOOP
    p_sum := p_sum + (2 * i - 1);
  END LOOP;
END sum_of_odd_numbers;
/

84. Procedure to Calculate Sum of Even Numbers

Description: This procedure calculates the sum of the first N even numbers.

CREATE OR REPLACE PROCEDURE sum_of_even_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := 0;
  FOR i IN 1..p_n LOOP
    p_sum := p_sum + (2 * i);
  END LOOP;
END sum_of_even_numbers;
/

85. Procedure to Print Hollow Square Pattern

Description: This procedure prints a hollow square using asterisks.

CREATE OR REPLACE PROCEDURE print_hollow_square(p_size IN NUMBER)
AS
BEGIN
  FOR i IN 1..p_size LOOP
    FOR j IN 1..p_size LOOP
      IF i = 1 OR i = p_size OR j = 1 OR j = p_size THEN
        DBMS_OUTPUT.PUT('*');
      ELSE
        DBMS_OUTPUT.PUT(' ');
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_hollow_square;
/

86. Procedure to Convert Decimal to Hexadecimal

Description: This procedure converts a decimal number into hexadecimal notation.

CREATE OR REPLACE PROCEDURE decimal_to_hexadecimal(p_decimal IN NUMBER, p_hexadecimal OUT VARCHAR2)
AS
  v_remainder NUMBER;
BEGIN
  p_hexadecimal := '';
  WHILE p_decimal > 0 LOOP
    v_remainder := MOD(p_decimal, 16);
    p_hexadecimal := CASE
      WHEN v_remainder < 10 THEN TO_CHAR(v_remainder)
      ELSE CHR(55 + v_remainder)
    END || p_hexadecimal;
    p_decimal := p_decimal / 16;
  END LOOP;
END decimal_to_hexadecimal;
/

87. Procedure to Convert Hexadecimal to Decimal

Description: This procedure converts a hexadecimal string into a decimal number.

CREATE OR REPLACE PROCEDURE hexadecimal_to_decimal(p_hexadecimal IN VARCHAR2, p_decimal OUT NUMBER)
AS
  v_len NUMBER := LENGTH(p_hexadecimal);
  v_decimal NUMBER := 0;
  v_digit NUMBER;
BEGIN
  FOR i IN 1..v_len LOOP
    v_digit := CASE
      WHEN SUBSTR(p_hexadecimal, i, 1) BETWEEN '0' AND '9' THEN TO_NUMBER(SUBSTR(p_hexadecimal, i, 1))
      ELSE ASCII(SUBSTR(p_hexadecimal, i, 1)) - 55
    END;
    v_decimal := v_decimal + v_digit * POWER(16, v_len - i);
  END LOOP;
  p_decimal := v_decimal;
END hexadecimal_to_decimal;
/

88. Procedure to Convert Binary to Octal

Description: This procedure converts a binary value to octal by way of decimal conversion.

CREATE OR REPLACE PROCEDURE binary_to_octal(p_binary IN VARCHAR2, p_octal OUT VARCHAR2)
AS
  v_decimal NUMBER;
BEGIN
  binary_to_decimal(p_binary, v_decimal);
  decimal_to_octal(v_decimal, p_octal);
END binary_to_octal;
/

89. Procedure to Convert Octal to Binary

Description: This procedure converts octal to binary by way of decimal conversion.

CREATE OR REPLACE PROCEDURE octal_to_binary(p_octal IN VARCHAR2, p_binary OUT VARCHAR2)
AS
  v_decimal NUMBER;
BEGIN
  octal_to_decimal(p_octal, v_decimal);
  decimal_to_binary(v_decimal, p_binary);
END octal_to_binary;
/

90. Procedure to Convert Decimal to Octal

Description: This procedure converts a decimal number into octal notation.

CREATE OR REPLACE PROCEDURE decimal_to_octal(p_decimal IN NUMBER, p_octal OUT VARCHAR2)
AS
  v_remainder NUMBER;
BEGIN
  p_octal := '';
  WHILE p_decimal > 0 LOOP
    v_remainder := MOD(p_decimal, 8);
    p_octal := TO_CHAR(v_remainder) || p_octal;
    p_decimal := p_decimal / 8;
  END LOOP;
END decimal_to_octal;
/

91. Procedure to Convert Octal to Decimal

Description: This procedure converts an octal string into decimal form.

CREATE OR REPLACE PROCEDURE octal_to_decimal(p_octal IN VARCHAR2, p_decimal OUT NUMBER)
AS
  v_len NUMBER := LENGTH(p_octal);
  v_decimal NUMBER := 0;
  v_digit NUMBER;
BEGIN
  FOR i IN 1..v_len LOOP
    v_digit := TO_NUMBER(SUBSTR(p_octal, i, 1));
    v_decimal := v_decimal + v_digit * POWER(8, v_len - i);
  END LOOP;
  p_decimal := v_decimal;
END octal_to_decimal;
/

92. Procedure to Check Prime Number

Description: This procedure checks whether a number is prime.

CREATE OR REPLACE PROCEDURE check_prime_number(p_num IN NUMBER, p_is_prime OUT BOOLEAN)
AS
BEGIN
  p_is_prime := TRUE;
  FOR i IN 2..SQRT(p_num) LOOP
    IF p_num MOD i = 0 THEN
      p_is_prime := FALSE;
      EXIT;
    END IF;
  END LOOP;
END check_prime_number;
/

93. Procedure to Find Factorial of a Number

Description: This procedure calculates factorial using a loop.

CREATE OR REPLACE PROCEDURE find_factorial(p_num IN NUMBER, p_factorial OUT NUMBER)
AS
  v_fact NUMBER := 1;
BEGIN
  FOR i IN 1..p_num LOOP
    v_fact := v_fact * i;
  END LOOP;
  p_factorial := v_fact;
END find_factorial;
/

94. Procedure to Swap Two Numbers

Description: This procedure swaps two numeric values using IN OUT parameters.

CREATE OR REPLACE PROCEDURE swap_numbers(p_num1 IN OUT NUMBER, p_num2 IN OUT NUMBER)
AS
  v_temp NUMBER;
BEGIN
  v_temp := p_num1;
  p_num1 := p_num2;
  p_num2 := v_temp;
END swap_numbers;
/

95. Procedure to Calculate Sum of First N Primes

Description: This procedure sums the first N prime numbers.

CREATE OR REPLACE PROCEDURE sum_of_first_n_primes(p_n IN NUMBER, p_sum OUT NUMBER)
AS
  v_sum NUMBER := 0;
  v_count NUMBER := 0;
  v_num NUMBER := 2;
  v_is_prime BOOLEAN;
BEGIN
  LOOP
    v_is_prime := TRUE;
    FOR i IN 2..SQRT(v_num) LOOP
      IF v_num MOD i = 0 THEN
        v_is_prime := FALSE;
        EXIT;
      END IF;
    END LOOP;
    IF v_is_prime THEN
      v_sum := v_sum + v_num;
      v_count := v_count + 1;
    END IF;
    EXIT WHEN v_count = p_n;
    v_num := v_num + 1;
  END LOOP;
  p_sum := v_sum;
END sum_of_first_n_primes;
/

96. Procedure to Generate Fibonacci Series

Description: This procedure stores a Fibonacci sequence in a collection.

CREATE OR REPLACE PROCEDURE generate_fibonacci_series(p_n IN NUMBER, p_series OUT SYS.ODCINUMBERLIST)
AS
  v_a NUMBER := 0;
  v_b NUMBER := 1;
  v_fib NUMBER;
BEGIN
  p_series.EXTEND;
  p_series(p_series.COUNT) := v_a;
  p_series.EXTEND;
  p_series(p_series.COUNT) := v_b;
  FOR i IN 3..p_n LOOP
    v_fib := v_a + v_b;
    p_series.EXTEND;
    p_series(p_series.COUNT) := v_fib;
    v_a := v_b;
    v_b := v_fib;
  END LOOP;
END generate_fibonacci_series;
/

97. Procedure to Reverse an Integer

Description: This procedure reverses the digits of an integer.

CREATE OR REPLACE PROCEDURE reverse_integer(p_num IN NUMBER, p_reverse OUT NUMBER)
AS
  v_digit NUMBER;
BEGIN
  p_reverse := 0;
  WHILE p_num > 0 LOOP
    v_digit := p_num MOD 10;
    p_reverse := p_reverse * 10 + v_digit;
    p_num := p_num / 10;
  END LOOP;
END reverse_integer;
/

98. Procedure to Print Pascal's Triangle

Description: This procedure prints Pascal's triangle using an array structure.

CREATE OR REPLACE PROCEDURE print_pascals_triangle(p_rows IN NUMBER)
AS
  TYPE row_array IS VARRAY(100) OF NUMBER;
  v_array row_array := row_array();
BEGIN
  v_array.EXTEND(p_rows + 1);
  v_array(1) := 1;
  FOR i IN 1..p_rows LOOP
    FOR j IN REVERSE 2..i+1 LOOP
      v_array(j) := v_array(j) + v_array(j-1);
    END LOOP;
    FOR j IN 1..i+1 LOOP
      DBMS_OUTPUT.PUT(v_array(j) || ' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_pascals_triangle;
/

99. Procedure to Print Floyd's Triangle

Description: This procedure prints Floyd's triangle again in a compact form.

CREATE OR REPLACE PROCEDURE print_floyds_triangle(p_rows IN NUMBER)
AS
  v_num NUMBER := 1;
BEGIN
  FOR i IN 1..p_rows LOOP
    FOR j IN 1..i LOOP
      DBMS_OUTPUT.PUT(v_num || ' ');
      v_num := v_num + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END print_floyds_triangle;
/

100. Procedure to Calculate Sum of First N Odd Numbers

Description: This procedure calculates the sum of the first N odd numbers.

CREATE OR REPLACE PROCEDURE sum_of_first_n_odd_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
  p_sum := 0;
  FOR i IN 1..p_n LOOP
    p_sum := p_sum + (2 * i - 1);
  END LOOP;
END sum_of_first_n_odd_numbers;
/

---

Note: Several examples are illustrative and may need small adjustments before running in a real Oracle environment.