oss db
QA, Interface team, Production Support team

Ref Cursor

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. 
One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units.
Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.
To give query result to front end developers in one variable such variable is declared as refcursor variable.

CREATE OR REPLACE PROCEDURE P1(O OUT SYS_REFCURSOR)
AS
BEGIN
OPEN O FOR 
SELECT * FROM TABLE_NAME;
END;
/

Bulk Collect

One method of fetching data is an Oracle bulk collect. 
With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection.  
During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them 
into the collection and switches back to the PL/SQL engine.  When rows are retrieved using Oracle bulk collect, 
they are retrieved with only two context switches.  
The larger the number of rows you would like to collect with Oracle bulk collect, 
the more performance improvement you will see using an Oracle bulk collect.

TYPE TNAME IS TABLE OF DATA TYPE INDEX BY BINARY_INTEGER;
cursor c1 is select columnname from tablename;
i tname;
begin
open c1;
fetch c1 bulk collect into i limit 1000;

forall    save exception

sql%bulk_exception.count

sql%bulk_exception.error_code

PLSQL Collections

	PLSQL TABLE	-	PLSQL
	VARRAY		-	SQL / plsql
	Nested Tables	-	SQL / plsql

.extend
.count
.first
.last

Indexes

	BITMAP and B-Tree Indexes
	Composite, Function Based index
	Reverse Key index
	Virtual Index

SQL Query Tuning

	AWR report
	TKPROF
	EXPLAIN PLAN
	TYPE OF LOOPS
		Nested loops
		hash join
		sort merge
	HINTS
	Indexes

Adding SQL “hints” to modify the execution plan 
Re-write SQL with Global Temporary Tables
Use SQL analytic function
Use minus instead of EXISTS subqueries
Rewrite complex subqueries with temporary tables 
using with clause
Index your NULL value
Leave column names alone - Never do a calculation on an indexed column 
Never mix data types
Use those aliases - Always use table aliases when referencing columns.

DBMS_PROFILER
DBMS_UTILITY.GET_TIME


SQL LOADER

Conventional path / direct path

The conventional path loader essentially loads the data by using standard INSERT statements.
 The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files.
 More information about the restrictions of direct path loading can be obtained from the Oracle Server Utilities Guide.
Some of the restrictions with direct path loads are:
Loaded data will not be replicated
Cannot always use SQL strings for column processing in the control file 


.csv / .dat
ctl
bad file
sqlldr 
discard file
log file

Append
Multiple table insert

EXTERNAL TABLES
UTL_FILE
	get_line
	put_line
	fopen
	fflush
	fclose

dictionary tables

user_dependencies
user_object
user_source
user_errors

IN and Exists



Society General Bank

Nelima

Advantages of Packages
Procedure & Functions
SQL query Tuning
PLSQL Collections
bulk collect
ref cursor
CBO - Cost based Optimizer
Hints
EXPLAIN PLAN