What is difference between Package and Stored Procedure?

Package:

• A package is a group of PL/SQL types, objects, stored procedures, and functions. it has two parts one is specification other is the body.
• In the specification, we mention procedures, functions & their parameters.
• In the body part, we define the whole operation performed by procedures and functions mentioned in the specification part.
• You can make your procedure private to the package by not declaring it in the package specification.
• packages cannot be called, passed parameters, or nested
• While calling a procedure from a package whole of the package is loaded into the memory. Like if a package consists of 4 procedures & we call 1 procedure then the whole 4 would be loaded to memory.

Procedure:

• The procedure is a standalone pl/sql unit in which all things related to procedure define in one go i.e parameters and whole functionality etc
• A procedure is a stored program in an oracle that is written down when a particular task has to be done.
• A procedure that resides in a package has to be called as <package_name>.<procedure_name>. On the other hand, a standalone procedure can be called by its name alone.
• We can pass IN, OUT parameters in the procedure.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle Bulk Collect and FORALL Explained with Examples

https://drive.google.com/open?id=17gSmFNEWMKNG-NGmk_Ku8T4QZGV7p7MY

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle EBS API to Create Application Users – Step-by-Step Guide

DECLARE
      lv_user_name varchar2(50) :='&user_name';
      lv_password varchar2(30) :='&password';
      lv_session_id integer    := userenv('sessionid');
BEGIN
   fnd_user_pkg.createuser(x_user_name           => lv_user_name,
                          x_owner                => NULL,
                          x_unencrypted_password => lv_password,
                          x_session_number       => lv_session_id,
                          x_start_date           => sysdate,
                          x_end_date             => NULL,
                          x_email_address        => NULL
                         );

       DBMS_OUTPUT.put_line('User:' || lv_user_name || 'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Unable to create User due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

API Error Handling in Oracle Apps

API Error Handling:

•   Every API has 3 out parameters as x_return_status, x_msg_count and x_msg_data.
• Using these 3 parameters, one can use the below code to log or debug errors in APIs.

dbms_output.put_line (SubStr('x_return_status = '||x_return_status, 1, 255));
dbms_output.put_line ('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line (SubStr('x_msg_data = '||x_msg_data, 1, 255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I ||'.'|| SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL Function in Oracle – Syntax, Parameters, and Real-Time Examples

1. NVL FUNCTION:

• The NVL function is used to replace NULL values with another value.
• The NVL function replaces an NA value or an empty string with a string.

syntax:
       NVL( value_in, replace_with )

• The NVL2 is an extended version of NVL. It takes three arguments.

syntax:
       NVL2(expression,value1,value2)


2. UID FUNCTIONS:

• The UID function returns the ID number for a user

syntax:
        UID

3. USERENV Function:

• The USERENV function can be used to retrieve information about the current Oracle session.

syntax:
       usernev(parameter)

      select userenv('language')from dual;
      select userenv('sid')from dual;         
      select userenv('lang')from dual;

4. NULLIF Function:

• NULLIF function compares expr1 and expr2.
• If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

Syntax:
      NULLIF( expr1, expr2 )

       select nullif('suriya','suriya')from dual;
       select nullif('suriya','parithy')from dual; 

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL %TYPE and %ROWTYPE – Usage, Differences, and Examples Explained

• %TYPE is used to declare a variable that is of the same type as a specified table’s column.

     
        example: Emp_number emp.empno%type;

• %ROWTYPE is used to declare a record (the variable that represents the entire row of a table).

         example: Emp_record emp%rowtype;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL BULK COLLECT INTO and CURSOR – Performance Tuning with Examples

BULK COLLECT INTO:

       Bulk collect can be used in the 'SELECT' statement to populate the records in bulk or in fetching the cursor in bulk. BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable.

• The FORALL statement
• The BULK COLLECT clause

The FORALL statement:

• The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches from PL/SQL.

       
           FOR i IN 1..10 LOOP
             INSERT INTO table_name
             VALUES (...);
           END LOOP;

• The bulk collect clause can appear in

              select into statement
              fetch into the statement
             -> returning into
                   delete statement
                   insert
                   update
                   execute immediately.

CURSOR:

• A cursor is a temporary work area created in the system memory when a SQL statement is executed.
• A cursor contains information on a select statement and the rows of data accessed by it.
• This temporary work area is used to store the data retrieved from the database and manipulate this data.
• A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code.

       Three different syntaxes to declare a cursor.

            * Cursor without parameters.
            * Cursor with parameters.
            * Cursor with a return clause.

Cursor without parameters:

     Syntax:
 
            CURSOR cursor_name
            IS
            SELECT statement;

Cursor with parameters:

    Syntax:
         
            CURSOR(parameter_list)
            IS
            SELECT statement;

Cursor with return clause:

    Syntax:
     
           CURSOR cursor_name
           RETURN field%ROWTYPE
           IS
           SELECT statement;



Retrieve data from the cursor

   Syntax:

          FETCH cursor_name INTO PL/SQL variable;
          or
          FETCH cursor_name INTO PL/SQL record;



Difference between cursor and ref cursor:

• A ref cursor is a pointer to a result set.
• This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs.
• A ref cursor is also a cursor, though normally the term cursor is used when discussing static SQL.

CURSOR ATTRIBUTES:

         Name             Description
       %FOUND            Returns TRUE if the record was fetched successfully, FALSE otherwise.
       %NOTFOUND    Returns TRUE if the record was not fetched successfully, FALSE otherwise.
       %ROWCOUNT    Returns the number of records fetched from the cursor at that point in time.
       %IS OPEN            Returns TRUE if the cursor is open, FALSE otherwise.

REF CURSOR:

• REF CURSOR is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.
• A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
• Cursor variables give you easy access to centralized data retrieval.
• REF CURSOR is a PL/SQL data type. create and return a REF CURSOR inside a PL/SQL code block.
• REF CURSOR is also referred to as Cursor Variables.
• Ref Cursor itself is a data type and easy to declare.
• Cursor variables are bind variables.
• PL/SQL cursor is static and the ref cursor is dynamic.

Dis advantage:

• Ref Cursors are not as efficient as Static cursors.
• Need additional code to print Ref Cursor values.

syntax:
       type<type_name>is ref cursor
       [return return_type];

strong and weak cursor:

• cursor variable has a return type then it is called a strong cursor.
• cursor variable has no return type then it is called a weak cursor.

Example:
         1) TYPE ref_item is ref cursor return item_master%rowtype.
         2) TYPE client is ref cursor;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL Exception Handling – Predefined, User-Defined & WHEN OTHERS Explained

Exception Handling:

• The exception is an error condition during program execution.
• Exceptions are used to handle errors that occur in your PL/SQL code.

 Types of exception:

1. System defined.
2. User-defined.

Different parts of the exception

• Declare the exception
• Raise an exception
• Handle the exception

SYNTAX:
       EXCEPTION
            WHEN OTHERS THEN
            <statements>

• no_data_found: when a select into statement returns no rows.
• program_error: when pl/sql has an internal problem.
• storage_error: memory was corrupted.
• too_many_rows: when a select into statement returns more than a row.
• value_error:  when arithmetic, conversion, truncation, or size constraint error occurs.
• Zero divide: when an attempt is made to divide a number by zero.
• invalid number.
• invalid cursor.

   This Exception Type of Error:

• ORA Core RDBMS errors
• PLS PL/SQL errors
• FRM Oracle Forms error
• REP Oracle Reports errors.

RAISE APPLICATION ERROR:

• Which is used to display the user-defined error messages along with the error number whose range is between -20000 and -20999.
• RAISE_APPLICATION_ERROR raises an exception but does not handle it.
• RAISE_APPLICATION_ERROR is used for the following reasons,

            a) to create a unique id for a user-defined exception.
            b) to make the user-defined exception look like an Oracle error.

Syntax:
       RAISE_APPLICATION_ERROR (error_number, error_message);

         • The Error number must be between -20000 and -20999
         • The Error_message is the message you want to display when the error occurs.

USER-DEFINED EXCEPTIONS:
     
        1. Declare exception
        2. RAISE exception
        3. Implement exception conditions.

Declare exception:

• must declare user user-defined exception name in the DECLARE block.

syntax:   
          user_define_exception_name EXCEPTION;

Raise exception:

• RAISE statement to raise defined exception name and control transfer to an EXCEPTION block.

syntax:
        RAISE user_define_exception_name;

Implement exception condition:

• In PL/SQL EXCEPTION block add the WHEN condition to implement a user-defined action.

syntax:
      WHEN user_define_exception_name THEN
User-defined statement (action) will be taken;


User-defined exception syntax:

DECLARE
user_define_exception_name EXCEPTION;
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;

     
Purpose of SQL code and sqlerrm:

• SQL code returns the numeric value for the error code.
• sqlerrm: returns the message associated with the error number.

           sqlcode                     description
            0                               no exception encountered.
            1                               user_defined exception.
            +100                         no_data_found exception.
           negative number    another oracle server error number.

            

DECLARE
           name employees.last_name%TYPE;
           v_code NUMBER;
           v_errm VARCHAR2(64);
BEGIN
       SELECT last_name INTO name FROM employees WHERE employee_id = 1000;
       EXCEPTION
           WHEN OTHERS THEN
             v_code := SQLCODE;
             v_errm := SUBSTR(SQLERRM, 1 , 64);
         Dbms_Output.Put_Line('The error code is ' || V_Code || '- ' || V_Errm);
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL Triggers in Oracle – BEFORE, AFTER, ROW-Level Trigger Examples Explained

1. Trigger:

• Triggers are stored programs, which are automatically executed or fired.
• When some event occurs.

   Uses:

• preventing invalid transactions.
• imposing security.
• auditing.

  Trigger allowed commit statement?

• Commit, save point, and rollback are not allowed within the trigger body.
• COMMIT - to save the changes.
• ROLLBACK - to roll back the changes.
• SAVEPOINT - creates points within the groups of transactions in which to ROLLBACK.
• It is possible to commit or roll back indirectly calling a procedure, but it is not recommended because of side effects to transactions.
• The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction.
• PRAGMA refers to a compiler directive. The directive restricts member subprograms to querying or modifying database tables and packaged variables.
• An autonomous transaction is an independent transaction of the main or parent transaction.
• If an Autonomous transaction is started by another transaction it is not nested, but independent of the parent transaction.

 AUTONOMUS TRANSACTIONS:

• An autonomous transaction is an independent transaction started by another transaction that is usually called the main transaction.
• Autonomous transactions may issue various DML statements and commit or roll them back.
• Trigger created earlier that fires after the UPDATE or DELETE statement is issued on the <my table> table where you record auditing data.
• Suppose you want to record auditing data even when the main transaction fails.
• The child transaction is called an autonomous transaction.
• An autonomous transaction is a completely independent transaction.
• An autonomous transaction does not share resources, locks, or any commit dependencies with the main transaction.
• Autonomous transactions are useful for creating software components that can be reused in numerous applications.
• A child table is commit are effect.

declare
       pragma_autonomous_transaction;

AUDITING:

• Auditing is the on-site verification activity, such as inspection or examination, of a process or quality system, to ensure compliance with requirements. An audit can apply to an entire organization or might be specific to a function, process, or production step.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.