Select first_name,salary from employees where rownum<=5;
Convert into PLSQL BLOCK:-
========================
Begin
For X IN (Select first_name,salary from Employees where rownum<=5)loop
DBMS_OUTPUT.PUT_LINE(X.FIRST_NAME||' '||[Link]);
END LOOP;
END;
/
DBMS_OUTPUT PACKAGE
PUT_LINE PROCEDURE
UTL_FILE-PACKAGE
FOPEN -FUNCTION
PUT_LINE-PROCEDURE
NEW_LINE-PROCEDURE
FCLOSE -PROCEDURE
UTL_FILE:-
========
Whenever select statement retrns more than one row as output,we can use UTL FILE to
write output in a text file stored externally.
1st step:-
login as sys/admin
SQLPLUS SYS/ADMIN AS SYSDBA
Create Directory
================
Create directory extdir as 'c:/new';
Grant access to HR in Directory extdir
======================================
Grant Read,write on directory extdir to HR;
Grant access to UTL_FILE to HR
==============================
Grant execute on UTL_FILE to HR;
Declare
F UTL_FILE.FILE_TYPE;
i varchar(100);
Begin
F:=UTL_FILE.FOPEN('EXTDIR','[Link]','W',32767);
FOR X IN (SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE ROWNUM<=10)LOOP
i:=X.FIRST_NAME||' '||[Link];
UTL_FILE.PUT_LINE(F,i);
UTL_FILE.NEW_LINE(F);
End Loop;
UTL_FILE.FCLOSE(F);
END;
/
======================================================
XML FILE:-
************
In XML,Multiple rows will be under 1 row.
Select DBMS_XMLGEN.GETXML('Select first_name,salary from employees where
rownum<=5')FROM DUAL;
Create Directory
================
Create directory extdir as 'c:/new';
Grant access to HR in Directory extdir
======================================
Grant Read,write,on directory extdir to HR;
Grant access to UTL_FILE to HR
==============================
Grant execute on UTL_FILE to HR;
To write into a file:-
=====================
Declare
F UTL_FILE.FILE_TYPE;
i CLOB;
Begin
Select DBMS_XMLGEN.GETXML('Select first_name,salary from employees where
rownum<=5')into i FROM DUAL;
F:=UTL_FILE.FOPEN('EXTDIR','EMP_dep.xml','W',32767);
UTL_FILE.PUT_LINE(F,i);
UTL_FILE.FCLOSE(F);
END;
/
