What are Return Statement and OUT Parameter in PLSQL Function?

Function must have return statement by which it returns one value.

Though we can use out parameter in function (function not getting called from select statement or DML), it is not good programming practice to write OUT and IN OUT in function. In case we wanted to return values using OUT parameters, always use procedures.

Note: In case we wanted to return more than one value from function, use of ref cursor is preferred solution not OUT parameters.

Without closing the cursor, if you want to open it what will happen? If error, get what is the error?

It throws error message

ORA-06511: PL/SQL: cursor already open

How does trace (and explain) SQL statements from SQL* plus?

Auto trace facility:

Set autotrace on

Select * from emp where empno =’7369’;

DBMS_XPLAN package:

Explain plan for Select * from emp where empno =’7369’;

Select * from table (dbms_xplan.diplay);

Persistent and non-persistent collections

Index-by tables cannot be stored in database tables, so they are non-persistent.

You cannot use them in a SQL statement and are available only in PL/SQL blocks.

Nested tables and Varrays are persistent. You can use the CREATE TYPE statement to create them in the database; you can read and write them from/to a database column.

Nested tables and Varrays must have been initialized before you can use them.

How to declare collections?

Nested tables

TYPE type_name IS TABLE OF element_type [NOT NULL];

With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except: REF CURSOR

Nested tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:

BINARY_INTEGER, PLS_INTEGER

BOOLEAN

LONG, LONG RAW

NATURAL, NATURALN

POSITIVE, POSITIVEN

REF CURSOR

SIGNTYPE

STRING

PL/SQL Declare

   TYPE TYP_NT_NUM IS TABLE OF NUMBER;

SQL

   CREATE [OR REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER;

Varrays

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

size_limit is a positive integer literal representing the maximum number of elements in the array.

PL/SQL Declare

   TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;

SQL

   CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;

Index-by tables

TYPE type_name IS TABLE OF element_type [NOT NULL]

   INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

INDEX BY key_type;

The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).

It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760).

The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.

Declare

   TYPE TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

How to Initialization collections?

Only Nested tables and varrays need initialization.

To initialize a collection, you use the “constructor” of the collection which name is the same as the collection.

Nested tables

Declare

   TYPE TYP_NT_NUM IS TABLE OF NUMBER;

    Nt_tab TYP_NT_NUM;

Begin

   Nt_tab := TYP_NT_NUM( 5, 10, 15, 20 ) ;

End;

Varrays

Declare

   TYPE TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15);

   v_tab TYP_V_DAY;

Begin

   v_tab := TYP_NT_NUM( ‘Sunday’,’Monday’,’Tuesday’,’Wedneday’,’Thursday’,’Friday’,’Saturday’ ) ;

End;

It is not required to initialize all the elements of a collection. You can either initialize no element. In this case, use an empty constructor.

v_tab := TYP_NT_NUM();

This collection is empty, which is different than a NULL collection (not initialized).

Index-by tables

Declare

   TYPE TYP_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

   my_tab TYP_TAB;

Begin

   my_tab(1) := 5 ;

   my_tab(2) := 10 ;

   my_tab(3) := 15 ;

End;

Comparing Oracle Collection Types?

Characteristic	Associative Array	Nested Table	Varray
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as column datatype in a table?	No	Yes; data stored "out of line" (in separate table)	Yes; data stored "in line" (in same table , if < 4,000 bytes)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
Subscript Limit	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)	Positive integer between 1 and 2,147,483,647	Positive integer between 1 and 2,147,483,647
Sparse?	Yes	Initially, no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefined	No
Can be compared for equality?	No	Yes, from Oracle Database 10g	No
Can be manipulated with set operators?	No	Yes, from Oracle Database 10g	No
Retains ordering and subscripts when stored in and retrieved from database?	N/A	No	Yes

Describe Methods related to collections?

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Collection methods cannot be called from SQL statements. For Varrays, you can suppress only the last element. If the element does not exists, no exception is raised.

collection_name.method_name[(parameters)]

We can use the following methods on a collection:

EXISTS

COUNT

LIMIT

FIRST and LAST

PRIOR and NEXT

EXTEND

TRIM

DELETE

 EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are function and EXTEND, TRIM, and DELETE are procedures.

Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error.

EXISTS (index):

Returns TRUE if the index element exists in the collection, else it returns FALSE.

Use this method to be sure you are doing a valid operation on the collection. This method does not raise the SUBSCRIPT_OUTSIDE_LIMIT exception if used on an element that does not exists in the collection.

COUNT:

COUNT returns the number of elements that a collection currently contains. COUNT is useful because the current size of a collection is not always known. For varrays, COUNT always equals LAST. For nested tables, COUNT normally equals LAST. But, if you delete elements from the middle of a nested table, COUNT becomes smaller than LAST.

LIMIT:

For nested tables and associative arrays, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain

FIRST and LAST:

Returns the first or last subscript of a collection. If the collection is empty, FIRST and LAST return NULL.

PRIOR (index) and NEXT (index):

Returns the previous or next subscripts of the index element. If the index element has no predecessor, PRIOR (index) returns NULL. Likewise, if index has no successor, NEXT (index) returns NULL.

EXTEND [(n[,i]) :

Used to extend a collection (add new elements).

EXTEND appends one null element to a collection.

EXTEND (n) appends n null elements to a collection.

EXTEND (n,i) appends n copies of the ith element to a collection.

TRIM (n):

Used to decrease the size of a collection.

TRIM removes one element from the end of a collection.

TRIM (n) removes n elements from the end of a collection.

DELETE [(n[,m])] :

Used to delete the element from a collection.

DELETE removes all elements from a collection.

DELETE (n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE (n) does nothing.

 DELETE (n,m) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE (n,m) does nothing

Describe errors in collections?

COLLECTION_IS_NULL:                You try to operate on an atomically null collection.

NO_DATA_FOUND:                        A subscript designates an element that was deleted, or a      nonexistent element of an associative array.

SUBSCRIPT_BEYOND_COUNT:   A subscript exceeds the number of elements in a collection.

SUBSCRIPT_OUTSIDE_LIMIT:      A subscript is outside the allowed range.

VALUE_ERROR:                             A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

Query to findout Assemble to Order?

select segment1 item,description,inventory_item_status_code item_status,item_type user_item_type,ood.organization_code inventory_org,replenish_to_order_flag assemble_to_orderfrom mtl_system_items_b msib,org_organization_definitions oodwhere msib.organization_id = ood.organization_idand ood.operating_unit =100order by segment1,organization_code