February 5, 2015

Redirect output in a file,Check Exist validation,Cursor Example in PL SQL

Redirect output in a file in Oracle PL SQL

There are different ways:
a) Before you execute the package you can enable SPOOL.
or
b) Create a temporary package and insert the log messages into that so that data can be viewed later on the temporary table.
or
c) Use DBMS_FILE package procedure to open and write data into files.

Check exists validation using pl/sql

declare
v_deptno dept.deptno%type:=&gdeptno;
cursor deptcursor
is
select * from dept where deptno=v_deptno;
v_deptcursor deptcursor%rowtype;
begin
open deptcursor;
fetch deptcursor into v_deptcursor;
if deptcursor%found then
dbms_output.put_line('v_deptcursor.ename);
else
dbms_output.put_line('sorr..! no deptloyee exists..');
end if;
end;

PL Sql program example of cursor on employee table

This is a PL Sql program example of cursor on employee table to check whether employee exist or not.

declare
v_empno emp.empno%type:=&gempno;
cursor empcursor
is
select *
from emp
where empno=v_empno;
v_empcursor empcursor%rowtype;
begin
open empcursor;
fetch empcursor into v_empcursor;
if empcursor%found then
dbms_output.put_line('v_empcursor.ename);
else
dbms_output.put_line('sorr..! no employee exists..');
end if;
end;