PL/SQL FAQ

RETURN clause in functions is used to return value after executing series of execution steps in a function.

As function can be used in select statement, the output of this statement is nothing but the value RETURN from the function.

PRAGMA The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL. The following pragmas are available: AUTONOMOUS_TRA...

PRAGMA

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.

Types Of Pragmas :-

1 - AUTONOMOUS_TRANSACTION

2 - EXCEPTION_INIT

3 - RESTRICT_REFERENCES

4 - SERIALLY_REUSABLE

Very simple meaning is...
1) Pragama means force (like it says to compiler forcible do this operation)

2) It is used for committing the particular block.

3) We can't rollback this Pragama autonomous block

before that statement that is pragma auto_tra only will save ...

yes, it is possible, but u have to use triggers not constraints

To define a constraint at a table level simply reduces the typing part of the
Program...logically
But. The thing is when defining constraint at a column level the rules of it just stay for the particular column but when we want it for one or many column table level
Is to be given

before trigger
after trigger
row level trigger
statement level trigger

Row level trigger

Indexes are created for better performance of the DB ; for faster retrival of data from DB. There are diffierent types of indexes... (1) B tree index (2) Bitmap map index (3) Function based index (4) ...

Eg: Consider table emp with EMP_ID,EM|NAME,ADD columns You can define the column e_name as Unique constraint. DECLARE e_name number(10) NOT NULL; BEGIN ... .. END: Primary key cannot have NU...

unique key are also indexed. PRIMARY KEY is clustered index whereas UNIQUE KEY is non clustered.

Cursors when exected temporary allot spaces in DB. There are 2 types of Cursors: (1) Implicit (2) Explicit (3) Ref cursors Implicit types of cursors are those cursors that are internally used by the ...

when we execute the sql statement from pl/sql it assigns the private work area for that statement.that contain all the information in it.in simplest form cursor is like a pointer in database table

Create procedure_name will create a procedure. If there is an already existing procedure with the same name it will return an error.

Create or replace procedure procedure_name will create an procedure if the same procedure name is non existing else it it overwrite already existing procedure....

Replace just recompiles the if the procedure is already existing with the same session settings. However Drop will drop the object from database and then you may need to recreate using create clause as required.

normal cursors do have a return type. ex:

Code
CREATE package pkg_test IS
cursor c1 RETURN emp%rowtype;
end pkg_test;
 
CREATE package body pkg_test IS
cursor c1 RETURN emp%rowtype IS SELECT * FROM emp WHERE empno = 10;
begin
FOR emp_cv IN c1 loop
..
end pkg_test;
 

in cursor we can use same sql statements multiple times.
ref cursor we can use multiple sql statements.

Code
 SELECT *
        FROM (SELECT player_nm, gross_sal,
               RANK () OVER (ORDER BY gross_sal DESC) playerRank
              FROM player)
        WHERE playerRank = 4;

SELECT TOP 1 * FROM
(
    SELECT TOP 4 * FROM EMPLOYEE ORDER BY CTC desc
)A
ORDER BY CTC

Hi Everybody, I dont see much update on why package is more faster than function or procedure. Of corse package is must faster than procedure or function The reason being whenvever package is called f...

Procedure may or may not return one or more value'sFunction must Return a valueFunction can use in the SQL query while procedure can't thats the major Difference between procedure and functionPackage ...

DBMS_OUTPUT - is a Oracle supplied package. put_line() is a DBMS_OUTPUT packages inbuilt procedure. put_line() is a combination of two inbuilt procedures such as put() and new_line(). procedure put()...

DBMS_OUTPUT is a Package and PUT_LINE is a Procedure.

You can debug the procedure by writting following command :
alter procedure procedure name
debug ;

You can debug the procedure. Oracle gives a util programme DBMS_DEBUG by which you can debug a procedure.the full detail you can see at http://www.oracleutilities.com/wiki/index.php?title=Dbms_debugan...

Code
SELECT   *  INTO 
tablename_2 FROM tablename_1

Code
SELECT   *  INTO 
tablename_2 FROM tablename_1
WHERE  VNO='CV/2011/12'

declarecursor cursor_name isselect col1,col2,col3from tab_name;ab_record cursor_name%rowtype;beginopen cursor_name;loopfetch ab_record into cursor_name;exit when cursor_name%notfound;insert into tab_n...

A Ref Cursor is a variable that points to cursor.A ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table and then reference ...

REF Cursor OR Dynamic Cursors: Query associated with cursor is not known at the time of compilation. The select statement associated with cursor is changing at the time of running the program Syntax...

select '('||substr(phone,1,3)||')'||substr(phone,4) "formated phone number",phone from phone

Use this query

create table newphone as (select '(' || substr(phnumber, 1,3) || ')' || substr(phnumber,4,10) "phonenumber" from oldphone)

A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, coll...

A Variable holds a value in a memory location. The Association of a variable to other variables can be called as composite variables.