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 ...
Thursday, June 26, 2014
How to check if a stored procedure is exist........?
Oracle data dictionary also called as system catalogue.the data dictionary which contain information about database.1.The data dictionary contains defination of all schema objects in the database,that is tables,view,stored procedure,sequences,synonyms,indexes etc.2.it also privides information about how much space has been allocated for these...
Tuesday, June 24, 2014
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 |...
Monday, June 23, 2014
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.....
Thursday, June 19, 2014
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 havemore than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, whereas the unique key allows null value.2.Q. What...
Friday, June 13, 2014
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...
Saturday, June 7, 2014
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 ...
Monday, June 2, 2014
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 ...
Implicit Cursors
Implicit CursorsImplicit cursors are automatically created by Oracle whenever an we SQL statement is process, when there is no explicit cursor for the statement.Programmers cannot control the implicit cursors and the information in it.Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this...
