Asp.net,PL SQL Tips for error resolution,Research,Development,SQL 2014,2012,2008,Procedural language,SP,excel macro ,Musical notations.

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;