Explore topic-wise InterviewSolutions in .

• The cursors that are declared in the procedures will have the local SCOPE and hence they cannot be used by other procedures.
• The cursors that are declared in package SPECIFICATIONS are treated with global scope and hence they can be used and ACCESSED by other procedures.

• The THEN and NOW tables are the virtual tables that are available during the DATABASE TRIGGER EXECUTION. The table columns are referred to as THEN.column and NOW.column respectively.
• Only the NOW.column is available for insert-related TRIGGERS.
• Only the THEN.column VALUES are available for the DELETE-related triggers.
• Both the virtual table columns are available for UPDATE triggers.

• SYS.ALL_DEPENDENCIES is used for describing all the dependencies between PROCEDURES, packages, triggers, FUNCTIONS that are accessible to the current USER. It returns the columns like NAME, dependency_type, TYPE, referenced_owner etc.

• ROLLBACK command is USED for rolling back all the CHANGES from the BEGINNING of the transaction.
• ROLLBACK TO command is used for undoing the transaction only till a SAVEPOINT. The transactions cannot be rolled back before the SAVEPOINT and hence the transaction remains ACTIVE EVEN before the command is specified.

• The functions that are used for manipulating the character data are CALLED String Functions.
  • LEFT: This function RETURNS the specified number of characters from the left part of a string.
    • Syntax: LEFT(string_value, numberOfCharacters).
    • For example, LEFT(‘InterviewBit’, 9) will RETURN ‘Interview’.
  • RIGHT: This function returns the DEFINED number of characters from the right part of a string.
    • Syntax: RIGHT(string_value, numberOfCharacters)
    • For example, RIGHT(‘InterviewBit’,3) would return ‘Bit’.
  • SUBSTRING: This function would select the data from a specified start position through the number of characters defined from any part of the string.
    • Syntax: SUBSTRING(string_value, start_position, numberOfCharacters)
    • For example, SUBSTRING(‘InterviewBit’,2,4) would return ‘terv’.
  • LTRIM: This function would trim all the white spaces on the left part of the string.
    • Syntax: LTRIM(string_value)
    • For example, LTRIM(’ InterviewBit’) will return ‘InterviewBit’.
  • RTRIM: This function would trim all the white spaces on the right part of the string.
    • Syntax: RTRIM(string_value)
    • For example, RTRIM('InterviewBit ') will return ‘InterviewBit’.
  • UPPER: This function is used for converting all the characters to the upper case in a string.
    • Syntax: UPPER(string_variable)
    • For example, UPPER(‘interviewBit’) would return ‘INTERVIEWBIT’.
  • LOWER: This function is used for converting all the characters of a string to lowercase.
    • Syntax: LOWER(string_variable)
    • For example, LOWER(‘INterviewBit’) would return ‘interviewbit’.

• %TYPE: This declaration is USED for the purpose of anchoring by PROVIDING the data type of any VARIABLE, column, or constant. It is useful during the declaration of a variable that has the same data type as that of its table column.
  • Consider the example of declaring a variable named ib_employeeid which has the data type and its size same as that of the column employeeid in table ib_employee. 
    The syntax would be : ib_employeeid ib_employee.employeeid%TYPE;
• %ROWTYPE: This is used for declaring a variable that has the same data type and size as that of a ROW in the table. The row of a table is called a record and its fields would have the same data types and names as the COLUMNS defined in the table.
  • For example: In order to declare a record named ib_emprecord for storing an entire row in a table called ib_employee, the syntax is:
    ib_emprecord ib_employee%ROWTYPE;

• SYSDATE:
  • This keyword returns the CURRENT time and date on the local database server.
  • The syntax is SYSDATE.
  • In order to EXTRACT part of the date, we USE the TO_CHAR function on SYSDATE and specify the format we need.
  • Usage:
    • SELECT SYSDATE FROM dual;
    • SELECT ID, TO_CHAR(SYSDATE, 'yyyy/mm/dd') from InterviewBitEmployeeTable where customer_id < 200;
• USER:
  • This keyword returns the user id of the current session.
  • Usage:
    • SELECT USER FROM dual;

The PL/SQL engine does the process of compilation and execution of the PL/SQL blocks and programs and can only work if it is installed on an Oracle server or any application tool that supports Oracle such as Oracle Forms.

• PL/SQL is one of the parts of Oracle RDBMS, and it is important to know that most of the Oracle applications are developed using the client-server architecture. The Oracle database forms the server-side and requests to the database form a part of the client-side.
• So based on the above fact and the fact that PL/SQL is not a standalone programming language, we must realize that the PL/SQL engine can RESIDE in EITHER the client environment or the server environment. This MAKES it easy to move PL/SQL modules and sub-programs between server-side and client-side applications.
• Based on the architecture shown below, we can UNDERSTAND that PL/SQL engine plays an important role in the process and execute the PL/SQL statements and whenever it encounters the SQL statements, they are sent to the SQL Statement Processor.

• Case 1: PL/SQL engine is on the server: In this case, the whole PL/SQL block gets passed to the PL/SQL engine present on the Oracle server which is then processed and the response is sent.
• Case 2: PL/SQL engine is on the client: Here the engine lies within the Oracle Developer tools and the processing of the PL/SQL statements is done on the client-side.
  • In case, there are any SQL statements in the PL/SQL block, then they are sent to the Oracle server for SQL processing.
  • When there are no SQL statements, then the whole block processing occurs at the client-side.