Forward Declaration

In the Event that you have more than one local declared procedure or Function within a block of Pl sql and the procedure must reference each Other, you won't be able to compile your code without getting an error.Each local procedure must be declared before the other.To get around this problem, you can use a forward declaration to specify the interface...

Read More

How to call stored procedure from within another......?

CREATE OR REPLACE PROCEDURE EMPANNSAL(P_SAL IN OUT NUMBER)ISBEGIN  P_SAL := P_SAL*12;END EMPANNSAL;/-- INVOKING FROM THE HOSTVARIABLE G_SAL NUMBERBEGIN  :G_SAL := 5000;END;/PRINT G_SALEXECUTE EMPANNSAL(:G_SAL)PRINT G_SAL-- INVOKE FROM ANOTHER PROCEDURECREATE OR REPLACE PROCEDURE EMPANSAL(P_DEPT IN NUMBER)IS  CURSOR EMPCUR IS SELECT LAST_NAME,SALARY ...

Read More

Procedure in Plsql

A procedure in oracle is program Unit that performs a particular task it also called as subprogram.this procedure can bebe invoked by another procedure or program which is called the calling program.It can be created in pl sql block,also inside a package,and schema level.SyntaxCREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_name [IN | OUT |...

Read More

For loop in oracle

Pl sql for loop is an iterative statement that allows to executea series of statements a fixed number of times.The foll is a syntax for loop statement.FOR loop_counter IN [REVERSE] lower_bound .. higher_boundLOOP   sequence_of_statements;END LOOP;SQL> declare  2  n1 number;  3  begin  4  for  i in 1.....

Read More

Top Ten Sql Queries

1.Q. How is the primary key different from a unique key? A. Both the primary and unique keys uniquely identify a record in a database table. main  difference is that you can have more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, where as  the unique key allows null value. 2.Q....

Read More

When Too_Many_Rows

Too_many exception is  ora error no ORA-01422 when you issue a select statement into single variable  that returns more than one row at that time this error will occur.if you want to handle this situation in your annonymous block or procedure use TOO_MANY_ROWSexception.The advantage of this approch is that you can customise your your messages...

Read More

No_data_Found

When Pl sql block detects an error during the  program executionnormal execution  stops and an exception block is raised.It is an pl sql error occur is raised during program execution, either implicitly by oracle server or explicitly by your program.Example 1. Write a  Pl sql Block that to find an Employee Number   using Exception.DECLARE ...

Read More

Conditional Statement

Conditional selection statements, which run different statements for different data values.The conditional selection statements are IF and and CASE.The IF THEN statement has this structure:IF condition THEN  statementsEND IF;If the condition is true, the statements run; otherwise,the IF statement does nothing. (For complete syntax, see "IF Statement".)DECLARE ...

Read More

Implicit Cursors Rowcount

Example :- write  PL/SQL Block, the salaries of all the employees in the ‘employees’ from department_no 50  are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries  are updated' Implicit cursors are automatically created when...

Read More