PLSQL

[cb1] Can we use return statement in procedure?

Yes, we can use a return statement in a procedure in Oracle. However, the purpose of the return statement in a procedure is different from the return statement in a function.

In a procedure, the return statement is used to exit the procedure at a specific point and return control to the calling program. The return statement can have a value, which is an optional parameter that can be used to return a specific value to the calling program.

Here's an example of a procedure that uses a return statement:

CREATE OR REPLACE PROCEDURE my_procedure (

  p_value IN NUMBER

) AS

BEGIN

  IF p_value < 0 THEN

    DBMS_OUTPUT.PUT_LINE('Error: Value cannot be negative');

    RETURN; -- exit the procedure

  END IF;

  

  -- do some processing

  DBMS_OUTPUT.PUT_LINE('Value is ' || p_value);

END;

In this example, if the input parameter p_value is negative, the procedure will print an error message and exit using the RETURN statement. If p_value is positive or zero, the procedure will continue processing and print the value.

[cb2] Will function compile correctly without return statement in execution section?

Yes, it will compile but fail while run the function. A function must return a value or a NULL. If there is no return statement in the function, it will result in a execution error.

[cb3] How to tune a procedure in oracle?

Tuning a procedure in Oracle involves identifying and optimizing the sections of the code that are causing performance issues. Here are some general steps to follow:

1. Identify the performance bottleneck: Use SQL trace or tools like Oracle Enterprise Manager to identify the specific parts of the code that are taking the most time to execute. This could be a long-running SQL query, a loop with too many iterations, or inefficient use of indexes.
2. Optimize the SQL queries: Use the SQL tuning techniques to optimize the SQL queries used in the procedure. This could include optimizing the query plan, adding appropriate indexes, or rewriting the SQL to use more efficient joins or subqueries.
3. Optimize the PL/SQL code: Check for inefficiencies in the PL/SQL code. This could include optimizing loops, using bulk processing, and minimizing context switching between SQL and PL/SQL.
4. Use bind variables: Use bind variables instead of literals in SQL statements to avoid hard parsing and reduce the amount of memory used by the shared pool.
5. Use appropriate data types: Use the appropriate data types for the variables and parameters in the procedure. Using smaller data types where possible can reduce memory usage and improve performance.
6. Use hints: Use query hints to force specific execution plans for SQL queries that are not performing well.
7. Test and re-test: Test the procedure after each optimization step to ensure that it is still functioning correctly and has not introduced any new issues.

By following these steps, you can improve the performance of a procedure in Oracle.

[cb4] How oracle package optimized compare to procedure?

When a package is loaded into the Oracle database, all of its components (procedures, functions, variables, etc.) are loaded together. This means that the entire package is parsed and compiled at once, and then loaded into memory. This allows for efficient execution of the package's code, as the package is only loaded into memory once and can be reused multiple times without the need for recompilation. Additionally, when a package is loaded into memory, its state is preserved for the duration of the session, so any changes made to the package's variables or state will persist throughout the session.

Oracle packages are optimized compared to procedures in several ways:

1. Code encapsulation: Packages provide a way to encapsulate related procedures, functions, and variables in a single unit. This makes the code easier to read, maintain, and optimize. Procedures, on the other hand, are standalone units of code that can become unmanageable and difficult to optimize as the codebase grows.
2. Shared memory: When a package is compiled, its code is stored in memory, making it available to all sessions. This means that each session does not have to recompile the package every time it is used, resulting in faster execution times.
3. Package state: Packages can maintain state between calls, which can help optimize performance by reducing the amount of data that needs to be passed back and forth between the database and the client. Procedures do not have this capability.
4. Package initialization: Packages can have an initialization section that is executed when the package is first loaded into memory. This section can be used to pre-fetch data, set up temporary tables, or perform other tasks that can improve performance.
5. Better optimization opportunities: Packages can be optimized as a single unit, allowing the optimizer to make better decisions about query execution plans. This is not possible with standalone procedures.

[cb5] Serialization pragma in oracle?

The Serialization pragma in Oracle is used to ensure that only one session can execute a specific block of code at any given time. It is a way of controlling concurrent access to shared resources in a multi-user environment.

The syntax for using the Serialization pragma is as follows:

PRAGMA SERIAlIZATION (statement_id);

Here, statement_id is the unique identifier of the block of code that needs to be serialized.

When this pragma is used, any session that tries to execute the specified block of code while another session is already executing it will be blocked until the first session completes. This helps to avoid conflicts that could arise when multiple sessions are trying to modify the same data simultaneously.

For example, consider the following code block:

DECLARE

   PRAGMA SERIALIZATION (update_employees);

   CURSOR c1 IS

      SELECT * FROM employees;

BEGIN

   FOR rec IN c1 LOOP

      UPDATE employees

      SET salary = rec.salary * 1.05

      WHERE employee_id = rec.employee_id;

   END LOOP;

END;

Here, the PRAGMA SERIALIZATION statement is used to serialize the block of code that updates the salaries of employees. This ensures that only one session can execute this block of code at any given time, preventing conflicts that could arise if multiple sessions tried to update the same employee records simultaneously.

[cb6] How to define trigger on view in oracle?

We can define an INSTEAD OF trigger on a view. An INSTEAD OF trigger fires instead of the triggering operation (insert, update, or delete) and provides an alternative way to perform the operation on the view.

Here's an example of an INSTEAD OF trigger on a view:

CREATE VIEW my_view AS SELECT col1, col2 FROM my_table;

CREATE OR REPLACE TRIGGER my_trigger

INSTEAD OF INSERT ON my_view

FOR EACH ROW

BEGIN

  INSERT INTO my_table (col1, col2) VALUES (:new.col1, :new.col2);

END;

[cb7] How many pragma in plsql?

1. AUTONOMOUS_TRANSACTION - allows a subprogram to execute SQL statements outside the context of the caller
2. EXCEPTION_INIT - associates an exception name with an Oracle error number
3. RESTRICT_REFERENCES - instructs the PL/SQL compiler to enforce specific purity rules for a subprogram
4. SERIALLY_REUSABLE - indicates that a package state can be shared among multiple sessions
5. INLINE - specifies that a function should be compiled inline, as if the code were inlined in the calling program
6. OPTIMIZE - indicates that the PL/SQL compiler should optimize the code for best performance
7. INTERFACE - specifies that a subprogram is part of an interface, allowing the PL/SQL compiler to perform more extensive checking of the subprogram's syntax
8. There are more pragmas available in PL/SQL, and their use cases can vary depending on the specific needs of the code being written.

[cb8] What will be solution for below situation to deploy?

package p1

  function f1 

  function f2 being called to package p2

package p2

 function f1 

 function f2 being called to package p1

Described situation is a package dependency loop, where package p1 depends on package p2 and vice versa. This can create a compilation error when trying to deploy the packages. There are several ways to resolve package dependency loops:

Refactor the code to eliminate the circular dependency. This may involve moving some functions from one package to another, or breaking up the packages into smaller, more focused units.

Use the "alter package" command to compile the packages in a specific order that resolves the dependency loop. For example, you could compile p2 first, then compile p1.

Use the "pragma autonomous_transaction" to create a separate transaction for each package, which allows the packages to be compiled independently. However, this approach can have performance implications and should be used with caution.

Overall, it's best to avoid package dependency loops whenever possible by designing packages with clear interfaces and minimizing cross-dependencies.

How to use UTL_FILE Package in PLSQL environment?

Need: We need UTL_FILE Package to read write data from database server to operating system file.

Prerequisites: To implement file access from OS to database server, Please create directory and file on OS level.

Step:01 Create OS Directory & database directory with path.

                SQL>conn sys as sysdba 

                SQL>CREATE or REPLACE DIRECTORY emp_dir as 'd:\emp_dir'

                       msg Directory Created !!

    

                SQL>Grant read, write ON directory emp_dir TO PUBLIC;

                         

                SQL>conn hr/hr

Step:02 Create procedure to write data into file.

                SQL>CREATE OR REPLACE PROCEDURE sal_status (dir IN VARCHAR2, filename IN VARCHAR2) 

                        IS

                                file UTL_FILE.FILE_TYPE; --package type variable declared as file(pointer)

                                CURSOR empc IS  SELECT last_name, salary, department_id

                                                            FROM employees

                                                            ORDER BY department_id;

                                /*empc cursor declare to process last_name, salary, department_id from employees */

                                newdeptno employees.department_id%TYPE;

                                olddeptno employees.department_id%TYPE := 0;

                        BEGIN

                                file:= UTL_FILE.FOPEN (dir, filename, 'w'); --UTL_FILE.FOPEN used to open file with specified directory

                                UTL_FILE.PUT_LINE(file,'REPORT: GENERATED ON ' || SYSDATE);--Write msg in file using PUT_LINE

                                UTL_FILE.NEW_LINE (file);--Insert new line in file

    

                                FOR emp_rec IN empc LOOP    --everytime it will write DEPARTMENT: column value using LOOP

                                        IF emp_rec.department_id <> olddeptno THEN 

                                                UTL_FILE.PUT_LINE (file,'DEPARTMENT: ' || emp_rec.department_id);

                                        END IF;

                                        UTL_FILE.PUT_LINE (file, ' EMPLOYEE: ' || emp_rec.last_name ||' earns: ' || emp_rec.salary);

                                        olddeptno := emp_rec.department_id;

                                END LOOP;

                                UTL_FILE.PUT_LINE(file,'*** END OF REPORT ***');

                                UTL_FILE.FCLOSE (file);        

                        EXCEPTION

                                WHEN UTL_FILE.INVALID_FILEHANDLE THEN        --if file name 

                                            RAISE_APPLICATION_ERROR(-20001,'Invalid File.');

                                WHEN UTL_FILE.WRITE_ERROR THEN

                                            RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file');

                        END sal_status;

                        /

Step:03 Execute procedure.

                       

                        SQL>execute sal_status('EMP_DIR','info.txt');

Step: 04 See the result in info.txt
----------------------------------------------------------------------------------------------
How to handle Exception in PL/SQL?

PL/SQL Exception Overview: 
Exception is an error in execution of PL/SQL block. We can handle exception using optional keyword EXCEPTION next to BEGIN keyword & just before END; keyword.

Handling exception is termination of block gracefully else it will terminate abruptly or propagate to calling environment. Suppose we are getting exception in inner block then it can handle by outer block EXCEPTION section.

PL/SQL exceptions are divide in three categories:
[1] Predefined Oracle Server exception
[2] Non Predefined Oracle Server exception
[3] User Defined exception


Predefined Oracle Server exception: It means exceptions are already known to oracle server and handle implicitly. 
Example: 
[1] ORA-01476: divisor is equal to zero



--------------------------------------------------------------------------------------------
[2] ORA-01722: invalid number

 

--------------------------------------------------------------------------------------------
[3] ORA-06502: PL/SQL: numeric or value error



Non Predefined Oracle Server exception: It means user have to declare exception in DECLARE section but they have standard oracle errors no & raised implicitly. So it can associate using PRAGMA EXCEPTION_INIT function.


PRAGMA EXCEPTION_INIT is a function that is help in trapping non predefined oracle server errors. PRAGMA is a compiler directive. It directs the compiler to interpret all occurrence of exception in the block and associate with oracle server error number. Example: