Function Pipelined | Table

In Oracle, a table is a basic object that holds data in rows and columns. It is the primary way to store and manage data in a relational database. A table can have one or more columns, each of which has a specific data type and a name. Tables can also have constraints, indexes, and other attributes to define the data's properties and relationships.

A pipeline function, on the other hand, is a special type of function in Oracle that can be used to transform data and return it as a collection. Unlike regular functions, which return a single value, pipeline functions return a set of rows that can be queried and processed like a table.

A pipeline function can take one or more input parameters, and it produces a result set that can be used in SQL queries or other PL/SQL code. Pipeline functions can be used to perform complex transformations on data and return the results in a structured format that can be easily consumed by other code.

Here is an example of a pipeline function:

CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id IN NUMBER)

RETURN sys.odcivarchar2list PIPELINED

IS

  l_employee_name VARCHAR2(100);

BEGIN

  FOR r IN (SELECT first_name || ' ' || last_name AS full_name

            FROM employees

            WHERE department_id = dept_id)

  LOOP

    PIPE ROW(r.full_name);

  END LOOP;

  RETURN;

END;

In this example, we are creating a pipeline function called get_employees_by_dept that takes a department ID as input and returns a list of employee names in that department. The sys.odcivarchar2list PIPELINED specifies that the function returns a collection of strings.

The function queries the employees table to retrieve the employee names in the specified department, and then iterates through the results using a FOR loop. For each row, it calls the PIPE ROW function to add the row to the result set. Finally, it returns the result set using the RETURN statement.

Once the function is created, it can be used in SQL queries like a regular table:

SELECT * FROM TABLE(get_employees_by_dept(10));

This query will return a list of employee names in the department with ID 10.

ROWID Usages

 In Oracle, ROWID is a pseudo column that is used to uniquely identify a specific row in a table. It represents the physical address of a row and consists of the data file number, the data block number, and the location of the row within the block.

The ROWID column can be useful in several ways in DQL (Data Query Language), DML (Data Manipulation Language), and programming of PL/SQL. Here are some examples:

Retrieving specific rows:

Suppose you have a table with thousands of rows, and you need to retrieve a specific row or set of rows based on a condition. You can use the ROWID column to retrieve the rows efficiently. For example:

SELECT * FROM mytable WHERE ROWID = 'AAABBBCCC';

Updating specific rows:

If you want to update specific rows, you can also use the ROWID column in the UPDATE statement. For example:

UPDATE mytable SET column1 = 'value' WHERE ROWID = 'AAABBBCCC';

Deleting specific rows:

Similarly, you can use the ROWID column to delete specific rows. For example:

DELETE FROM mytable WHERE ROWID = 'AAABBBCCC';

PL/SQL programming:

In PL/SQL, you can use the ROWID column to loop through a table and perform operations on each row. For example:

DECLARE

  CURSOR c1 IS SELECT ROWID, column1 FROM mytable;

BEGIN

  FOR r1 IN c1 LOOP

    IF r1.column1 = 'value' THEN

      -- do something with the row

      UPDATE mytable SET column2 = 'newvalue' WHERE ROWID = r1.ROWID;

    END IF;

  END LOOP;

END;

In this example, we are using a cursor to loop through the table and retrieve each row's ROWID and column1 value. We then use the ROWID to update the row's column2 value if the column1 value is 'value'.

Overall, the ROWID column is a powerful tool in Oracle that can be used to efficiently retrieve, update, and delete specific rows, as well as perform operations on each row in a table using PL/SQL.

Analytical Functions

Oracle provides several analytical functions that enable users to perform complex calculations on sets of rows returned by a query. These functions allow users to compute cumulative, aggregate, and ranking values without the need for self-joins or subqueries.

Here are the commonly used Oracle analytical functions with examples:

RANK(): The RANK() function returns the rank of each row within a result set, with ties receiving the same rank and the next rank being skipped.

Example:

select salary, rank() over(order by salary desc) as r from employees;

DENSE_RANK(): The DENSE_RANK() function returns the rank of each row within a result set, with ties receiving the same rank and no rank being skipped.

Example:

select salary,dense_rank() over(order by salary desc) as dr from employees;

ROW_NUMBER(): The ROW_NUMBER() function returns a unique number for each row within a result set.

Example:

select employee_id,last_name, salary, ROW_NUMBER() over (order by salary desc) row_num from employees

LAG(): The LAG() function returns the value of a column from a previous row in the result set.

Example:

select employee_id,last_name,job_id,salary, lag(salary, 1, 0) over(order by salary) as sal_prev from employees;

LEAD(): The LEAD() function returns the value of a column from a following row in the result set.

Example:

select employee_id,last_name,job_id,salary, lead(salary, 1, 0) over (order by salary) as sal_prev from   employees;

FIRST_VALUE(): The FIRST_VALUE() function returns the value of the first row within a partition of the result set.

Example:

select employee_id,department_id,salary, first_value(salary ignore nulls) over (partition by department_id order by salary asc nulls last) value_result from employees;

LAST_VALUE(): The LAST_VALUE() function returns the value of the last row within a partition of the result set.

Example:

select employee_id,  department_id,  salary,   last_value(salary ignore nulls) over(partition by department_id order by salary asc nulls last) value_result  from employees;

SUM(): The SUM() function returns the sum of a column within a result set.

Example:

select employee_id,sum(salary) over(order by employee_id) as sum_sal from employees;

AVG(): The AVG() function returns the average of a column within a result set.

Example:

select employee_id,department_id,salary,avg(salary) over(partition by department_id order by department_id)as avg_dept_wise from employees;

MAX(): The MAX() function returns the maximum value of a column within a result set.

Example:

select employee_id,department_id,salary,max(salary) over(partition by department_id order by department_id) max_dept_wise from employees;

MIN(): The MIN() function returns the minimum value of a column within a result set.

Example:

select employee_id,department_id,salary,min(salary) over(partition by department_id order by department_id) min_dept_wise from employees;

These are some of the commonly used Oracle analytical functions with examples.

Views - Simple | Complex | Materialized

 In Oracle, there are several types of views. A view is a virtual table that does not store data but retrieves data from one or more tables. Views provide a way to simplify complex queries by presenting data in a more manageable format. The following are the most common types of views in Oracle:

Simple View:

A simple view is a view that is based on a single table. A simple view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table_name

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees whose salaries are greater than 5000 from the employees table, we can use the following syntax:

CREATE VIEW high_salary_employees AS

SELECT first_name, last_name, salary

FROM employees

WHERE salary > 5000;

Complex View:

A complex view is a view that is based on multiple tables or subqueries. A complex view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table1

JOIN table2 ON condition

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees who work in the IT department from the employees and departments tables, we can use the following syntax:

CREATE VIEW it_department_employees AS

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'IT';

Materialized View:

A materialized view is a view that stores data physically in the database. Unlike simple and complex views, materialized views can improve query performance by precomputing and caching the results of the view. A materialized view can be created using the following 

Syntax:

CREATE MATERIALIZED VIEW [schema.]view_name

REFRESH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND | SYSTEM]

[ENABLE | DISABLE] QUERY REWRITE

AS select_statement;

Explanation of the syntax elements:

• [schema.]view_name - the name of the materialized view, optionally prefixed with the schema name
• REFRESH - specifies the refresh method of the materialized view
• FAST | COMPLETE | FORCE - specifies the refresh method options
• ON - specifies when the materialized view is refreshed
• COMMIT | DEMAND | SYSTEM - specifies the refresh method options
• [ENABLE | DISABLE] QUERY REWRITE - specifies whether query rewrite is enabled or disabled for the materialized view
• AS select_statement - the SQL query that defines the materialized view

Example: 01

If we want to create a materialized view that displays the number of employees in each department from the employees and departments tables, we can use the following syntax:

CREATE MATERIALIZED VIEW employee_count_by_department AS

SELECT d.department_name, COUNT(e.employee_id) AS employee_count

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name

ORDER BY d.department_name;

Example: 02

Suppose we have two tables in the database - employees and departments. We want to create a materialized view that shows the total salary of each department. Here is the example SQL code to create such a materialized view:

CREATE MATERIALIZED VIEW emp_salary_by_dept

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT d.department_name, SUM(e.salary) as total_salary

FROM employees e, departments d

WHERE e.department_id = d.department_id

GROUP BY d.department_name;

In this example, we've created a materialized view named emp_salary_by_dept. The REFRESH clause specifies that the materialized view will be refreshed FAST and ON DEMAND. The ENABLE QUERY REWRITE clause enables query rewrite for the materialized view, which means that Oracle can use the materialized view to rewrite queries for improved performance.

The AS clause defines the SQL query that defines the materialized view. In this case, we're selecting the department_name column from the departments table and the sum of the salary column from the employees table, grouping the results.

Example:03

Let's say we have a table called sales that contains information about sales made by different employees in different regions. We can create a materialized view that calculates the total sales made by each employee by region as follows:

CREATE MATERIALIZED VIEW mv_sales_by_employee_region

REFRESH FAST ON COMMIT

AS

SELECT employee_id, region, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY employee_id, region;

In the above example, the materialized view mv_sales_by_employee_region is created with the REFRESH FAST ON COMMIT option, which means that the materialized view is refreshed automatically whenever there is a commit on the base table sales. The SELECT statement in the AS clause defines the columns and the data that are included in the materialized view. The GROUP BY clause groups the sales data by employee and region, and the SUM function calculates the total sales for each employee by region.

Example:04

Let's say we have two tables, employees and departments, with the following data:

Employees Table:

employee_id first_name last_name salary department_id

        1                 John         Smith         5000     1

        2                 Jane         Doe                6000     1

        3                 Bob                 Johnson 7000     2

        4                 Sally         Smith         8000     2

Departments Table:

department_id department_name

        1                     Sales

        2                     Marketing

We can create a materialized view that shows the total salary of each department using the following SQL statement:

CREATE MATERIALIZED VIEW department_salary_summary

REFRESH COMPLETE ON DEMAND

AS

SELECT d.department_name, SUM(e.salary) AS total_salary

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name;

In this example, the material    zed view is called department_salary_summary, and it will be refreshed completely on demand. The query in the materialized view calculates the total salary of each department by joining the employees and departments tables and grouping the data by department.

Once the materialized view is created, we can query it just like a regular table:

SELECT * FROM department_salary_summary;

Note - that the first time we query the materialized view, it will be populated with data from the underlying tables. Subsequent queries will use the cached data until the materialized view is refreshed.

Temporary vs External Tables

 Temporary Table:

A temporary table is a table that is created and used only for the duration of a transaction or a session. It is typically used to store intermediate results that are needed to perform complex queries or operations. Temporary tables are automatically dropped at the end of a transaction or a session. Temporary tables are useful in scenarios where you need to perform multiple queries on the same set of data or when you want to store intermediate results for further processing.

Syntax:

CREATE GLOBAL TEMPORARY TABLE table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

) ON COMMIT [ PRESERVE ROWS | DELETE ROWS ];

Here, table_name is the name of the temporary table that you want to create, and datatype represents the data type of the columns in the table. You can define as many columns as needed, each separated by a comma.

The ON COMMIT clause is used to specify when the data in the temporary table should be cleared. You can choose to PRESERVE ROWS, which means that the data will be preserved until the end of the session, or DELETE ROWS, which means that the data will be deleted as soon as the transaction ends.

Example:

Here's an example of creating a temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_customers

(

  id NUMBER(10) NOT NULL,

  name VARCHAR2(50) NOT NULL,

  email VARCHAR2(50),

  phone VARCHAR2(20),

  city VARCHAR2(30),

  country VARCHAR2(30)

) ON COMMIT PRESERVE ROWS;

This creates a temporary table called temp_customers with six columns: id, name, email, phone, city, and country. The data in this table will be preserved until the end of the session.

You can insert data into this table just like you would with a regular table:

INSERT INTO temp_customers (id, name, email, phone, city, country)

VALUES (1, 'John Doe', 'johndoe@example.com', '555-1234', 'New York', 'USA');

And you can select data from the temporary table just like any other table:

SELECT * FROM temp_customers;

External Table:

An external table is a table-like object that represents data stored outside of a database. It is a read-only table, which means that you cannot modify the data in the external table. You can create an external table to access data in a flat file, a remote database, or any other data source accessible by the database server. The structure of an external table is defined by a set of metadata that describes the data's layout, format, and location. An external table is accessed just like a regular database table, and you can query it using SQL.

Syntax:

CREATE TABLE table_name (

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

) 

ORGANIZATION EXTERNAL (

  TYPE type_name

  DEFAULT DIRECTORY directory_name

  ACCESS PARAMETERS (

    param1_name [ = value1 ],

    param2_name [ = value2 ],

    ...

  )

  LOCATION ( 'file_name' [ , 'file_name' ]* )

);

Let's break down the different parts of this syntax:

• CREATE TABLE: The statement used to create a new table in the database.
• table_name: The name of the table you want to create.
• column1, column2, etc.: The columns of the table, along with their data types.
• NULL | NOT NULL: An optional constraint that specifies whether a column can contain NULL values or not.

After the column definitions, you specify the ORGANIZATION EXTERNAL clause to indicate that you want to create an external table.

• TYPE type_name: The type of external table you want to create. For example, you can specify ORACLE_LOADER for a table that reads data from a flat file using SQL*Loader.
• DEFAULT DIRECTORY directory_name: The directory in the file system where the external data files are located. This directory must be defined in the database using the CREATE DIRECTORY statement.
• ACCESS PARAMETERS: A set of parameters that configure how the external table accesses the external data files.
• param1_name [ = value1 ], param2_name [ = value2 ], ...: The name and value of each parameter. The parameters you use depend on the type of external table you're creating.
• LOCATION ( 'file_name' [ , 'file_name' ]* ): The name or names of the external data files. You can specify multiple files separated by commas.

Example: 01 

Here's an example of how you might define an external table that reads data from a CSV file using the ORACLE_LOADER type:

CREATE TABLE my_external_table (

  id NUMBER,

  name VARCHAR2(100),

  salary NUMBER

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY my_data_dir

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

  )

  LOCATION ('employees.csv')

);

In this example, we're creating an external table called my_external_table that has three columns: id, name, and salary. The table is configured to use the ORACLE_LOADER type to read data from a CSV file called employees.csv located in the my_data_dir directory. We're also specifying some access parameters to indicate that the data is delimited by newlines and fields are separated by commas.

Example: 02

Suppose you have a CSV file containing sales data. You can create an external table to read data from that CSV file using the following SQL statement:

CREATE TABLE sales_ext (

    id NUMBER,

    product VARCHAR2(50),

    sale_date DATE,

    sale_amount NUMBER

)

ORGANIZATION EXTERNAL (

    TYPE CSV

    DEFAULT DIRECTORY data_dir

    LOCATION ('sales.csv')

);

In summary, an external table is used to access data stored outside the database, while a temporary table is used to store intermediate results within the database for the duration of a transaction or a session.

SQL Loader Utility:

SQL Loader is a command-line utility that is used to load data from external files into tables of an Oracle database. It is a powerful tool that can load large amounts of data quickly and efficiently.

Syntax:

The basic syntax for using SQL Loader is:

sqlldr userid=<username>/<password>@<database> control=<control file> log=<log file>

Here:

userid specifies the username, password, and database to connect to.

control specifies the name of the control file that contains the instructions for the load process.

log specifies the name of the log file to store any errors or status messages that occur during the load process.

Example 1: Simple Load

Suppose we have a data file employee.csv containing the following data:

1001,John Smith,10000

1002,Jane Doe,12000

1003,Bob Johnson,15000

We want to load this data into a table employees in the Oracle database. Here's what our control file employee.ctl looks like:

LOAD DATA

INFILE 'employee.csv'

INTO TABLE employees

FIELDS TERMINATED BY ','

(

  emp_id,

  name,

  salary

)

The LOAD DATA keyword indicates that we are starting a new load process. The INFILE keyword specifies the name of the data file we want to load. The INTO TABLE keyword indicates the name of the table we want to load the data into. The FIELDS TERMINATED BY keyword specifies the delimiter used in the data file. Finally, we specify the columns we want to load, along with their corresponding data types.

To execute this load, we run the following command:

sqlldr userid=scott/tiger@orcl control=employee.ctl log=employee.log

This will load the data from the file employee.csv into the table employees in the Oracle database. Any errors or status messages will be recorded in the log file employee.log.

Example 2: Conditional Load

Suppose we have a data file sales.csv containing the following data:

1001,2021-01-01,5000

1002,2021-01-01,6000

1003,2021-01-01,7000

We want to load this data into a table sales in the Oracle database, but only for records where the sales amount is greater than $6000. Here's what our control file sales.ctl looks like:

LOAD DATA

INFILE 'sales.csv'

INTO TABLE sales

FIELDS TERMINATED BY ','

(

  sale_id,

  sale_date "TO_DATE(:sale_date,'YYYY-MM-DD')",

  sale_amount

)

WHERE sale_amount > 6000

In this control file, we've added a WHERE clause to specify that we only want to load records where the sale_amount is greater than 6000. We've also added a conversion function to convert the sale_date field from a string to a date.

To execute this load, we run the following command:

sqlldr userid=scott/tiger@orcl control=sales.ctl log=sales.log

This will load the data from the file sales.csv into the table sales in the Oracle database, but only for records where the sales amount is greater than $6000. Any errors or status messages will be recorded in the log file sales.log.

PLSQL Quick Review Questions

1.	What is PL/SQL?
	PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
2.	What is the basic structure of PL/SQL?
	PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3.	What are the components of a PL/SQL block?
	A set of related declarations and procedural statements is called block.
4.	What are the components of a PL/SQL Block?
	Declarative part, Executable part and Execption part.
5.	What are the datatypes a available in PL/SQL?
	Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.
6.	What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
	% TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are: I. need not know about variable's data type ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
7.	What is difference between % ROWTYPE and TYPE RECORD ?
	% ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables. E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type ); e_rec emp% ROWTYPE Cursor c1 is select empno,deptno from emp; e_rec c1 %ROWTYPE.
8.	What is PL/SQL table?
	Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
9.	What is a cursor? Why Cursor is required?
	Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10.	Explain the two types of Cursors?
	There are two types of cursors, Implict Cursor and Explicit Cursor. PL/SQL uses Implict Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.
11.	What are the PL/SQL Statements used in cursor processing?
	DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO <variable list> or Record types, CLOSE cursor name.
12.	What are the cursor attributes used in PL/SQL?
	%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows featched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are featched. % NOT FOUND - to check whether cursor has featched any row. True if no rows are featched. These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.
13.	What is a cursor for loop?
	Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP;
14.	What will happen after commit statement ?
	Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;----- commit; end loop; end; The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK. The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
15.	Explain the usage of WHERE CURRENT OF clause in cursors ?
	WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.
16.	What is a database trigger ? Name some usages of database trigger ?
	Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
17.	How many types of database triggers can be specified on a table? What are they?
	Insert             Update             Delete Before Row                 o.k.                  o.k.                o.k. After Row                   o.k.                  o.k.                o.k. Before Statement        o.k.                  o.k.                o.k. After Statement          o.k.                  o.k.                o.k. If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the retruned boolean value.
18.	Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
	It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19.	What are two virtual tables available during database trigger execution?
	The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.
20.	What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
	Mutation of table occurs.
21.	Write the order of precedence for validation of a column in a table ?
	I. done using Database triggers. ii. done using Integarity Constraints.
22.	What is an Exception? What are types of Exception?
	Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are. CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS.
23.	What is Pragma EXECPTION_INIT? Explain the usage?
	The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
24.	What is Raise_application_error?
	Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.
25.	What are the return values of functions SQLCODE and SQLERRM?
	SQLCODE returns the latest code of the error that has occured. SQLERRM returns the relevant error message of the SQLCODE.
26.	Where the Pre_defined_exceptions are stored?
	In the standard package. Procedures, Functions & Packages;
27.	What is a stored procedure?
	A stored procedure is a sequence of statements that perform specific function.
30.	What is difference between a PROCEDURE & FUNCTION?
	A FUNCTION is alway returns a value using the return statement. A PROCEDURE may return one or more values through parameters or may not return at all.
31.	What are advantages of Stored Procedures?
	Extensible, Modularity, Reusability, Maintainability and one time compilation.
32.	What are the modes of parameters that can be passed to a procedure?
	IN,OUT,IN-OUT parameters.
33.	What are the two parts of a procedure?
	Procedure Specification and Procedure Body.
34.	Give the structure of the procedure?
	PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;
35.	Give the structure of the function?
	FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End;
36.	Explain how procedures and functions are called in a PL/SQL block ?
	Function is called as part of an expression. sal := calculate_sal ('a822'); procedure is called as a PL/SQL statement calculate_bonus ('A822');
37.	What is Overloading of procedures?
	The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line
38.	What is a package? What are the advantages of packages?
	Package is a database object that groups logically related procedures. The advantages of packages are Modularity, Easier Applicaton Design, and Information. Hiding,. Reusability and Better Performance.
39.	What are two parts of package?
	The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
40.	What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
	A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
41.	How packaged procedures and functions are called from the following ? a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS
	a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE b. BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCTION NAME (arguments); END; END EXEC; c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.
42.	Name the tables where characteristics of Package, procedure and functions are stored?
	User_objects, User_Source and User_error.