CREATE OR REPLACE PROCEDURE EMPANNSAL(P_SAL IN OUT NUMBER)
IS
BEGIN
P_SAL := P_SAL*12;
END EMPANNSAL;
/
-- INVOKING FROM THE HOST
VARIABLE G_SAL NUMBER
BEGIN
:G_SAL := 5000;
END;
/
PRINT G_SAL
EXECUTE EMPANNSAL(:G_SAL)
PRINT G_SAL
-- INVOKE FROM ANOTHER PROCEDURE
CREATE OR REPLACE PROCEDURE EMPANSAL(P_DEPT IN NUMBER)
IS
...
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...
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 be
be 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.
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name...
Monday, June 23, 2014
For loop in oracle
Pl sql for loop is an iterative statement that allows to execute
a series of statements a fixed number of times.
The foll is a syntax for loop statement.
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
sequence_of_statements;
END LOOP;
SQL> declare
2 n1 number;
3 begin
4 for...
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 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....
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_ROWS
exception.
The advantage of this approch is that you can customise your your...
Saturday, June 7, 2014
No_data_Found
When Pl sql block detects an error during the program execution
normal 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...
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
statements
END IF;
If the condition is true, the statements run; otherwise,
the IF statement does nothing. (For complete syntax,...
Implicit Cursors
Implicit Cursors
Implicit 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...
