PL/SQL FAQ

Main Difference : Referential
Index is created in both Cases (Unique and Primary key Constraint )

But can't references Columns with combination of UNIQUE and NOT NULL. Like in Primary key ,we Can references primary key to make foreign key constraint .

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...

"sql begin declare @inputstring varchar(100),@var1 int,@reversestring varchar(100),@string2 varchar(100) select @inputstring = REVERSE select @var1 = LEN(@inputstring) WHILE (@var1...

From Oracle 9i Oracle introduced Flashback query feature. It is useful to recover the data which is accidentally deleted. Suppose a user accidentally deletes rows from any table and commits it, by usi...

DDL is not possible inside procedure body

EXECUTE IMMEDIATE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

Select Trunc(SYSDATE,MON) From Dual;

select last_day(add_months(trunc(sysdate),-1)) + 1 from dual;

OR

select trunc(sysdate,MON) from dual;

Using trace_on utility

eg:- For HRMS hr_utility.trace_on ;
expression(here call the package which you want to debug);
HRMS hr_utility.trace_off;

Ref cursor is having return type where as cursor does not have .
syntax of ref cursor:- type ref_type_name is ref cursor [return return_type].

syntax of cursor:- declare
cursor cursor name select statement..

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;
 

Code
declare
type id_table IS TABLE of testemp.emp_id%type;
 v_id_table id_table;
 
 begin 
 UPDATE testemp SET subject = d1 WHERE emp_id = 1
 returning emp_id bulk collect INTO v_id_table;
 
 commit;
 dbms_output.put_line(count IS:==||v_id_table.count);
 end;

Use Bulk Update stmt.

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

The RETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse the RETURN ...

set serveroutput on;
begin
delete from employ where empid = 1;
commit;
dbms_output.put_line(NO of records deleted||sql%rowcount);
end;

Cursor - A cursor is like a virtual table, with row and columns specified by the query. A cursor also has a nation of current row, which is essentially a pointer to a row in the virtual table.
Types of cursors-
1) Static
2) Dynamic
3) Forwardonly
4) Keyset-driven.

I do not agree with this, Even a function can perform an action.

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...

There are two general ways to query a table that contains a collection type as a column or attribute. Nest the collections in the result rows that contain them. Distribute or unnest collections so t...

MUTATING : MUTATING TRIGGER IS TRIGGER WHICH IS USED TO PERFORM DML OPERATIONS WE CAN RESOLVE BY REGARDS ASHOK

Code
CREATE OR REPLACE TRIGGER TRG_UPDATE_SAL
BEFORE UPDATE ON EMP
FOR EACH ROW 
BEGIN
IF :NEW.SAL<:OLD.SAL THEN
UPDATE EMP SET COMM =2000;
ELSIF :NEW.SAL<:OLD.SAL THEN
UPDATE EMP SET COMM=1000;
END IF;
END;

There are cases when we have mutating table error inspite of using an "AFTER TRIGGER at ROW LEVEL".  This happens when you are using an after delete trigger.  For instance a delete...

select *from emp;
here * retriving all records from the db

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