PLSQL 8

From bd_en
Jump to: navigation, search

http://docstore.mik.ua/orelly/oracle/bipack/ch06_02.htm

Predefined packages

In one of the previous laboratories, you have created a package which contained a series of functions and procedures which had the same goal. In Oracle, there are some predefined packages that you can use to make your work easier ( you have already used the package DBMS_OUTPUT and you have probably used the most popular function from there: PUT_LINE). Here are some other available packages for PLSQL ( you can find the complete list at : https://docs.oracle.com/database/121/ARPLS/intro.htm#ARPLS65018) :

  • UTL_FILE - it allows working with files stored directly on the server's HDD (reading/ writing from/to files).
  • UTL_HTTP - allows accessing HTML pages from PLSQL using the HTTP protocol ( unfortunately there are very few websites that are still using this unsafe protocol).
  • UTL_TCP - allows communication by TCP/IP protocol with diverse applications.
  • DBMS_SQL - allows accessing the database using dynamic SQL.
  • DBMS_OUTPUT - allows showing information in the Oracle console and for this reason, it is useful in debugging - usually it shouldn't be used because the database server is not accessed through the console, but through a connection and a request that is being executed through that connection;

etc. (If you find in the list above other packages that you find interesting, you can create a wiki page for it and you will receive 10 points for it - for a page realized "by the book").

UTL_FILE

The package UTL_FILE is defined in the file utilfile.sql ( that you can find in the directory where you have installed Oracle, in the section of administration). It is executed only once from the administrator account at the installation of the Oracle server and only the administrator is allowed access to its functionality - obviously, if he doesn’t allow other users to access it, giving them access with a grant : GRANT EXECUTE ON UTL_FILE TO STUDENT; (for the user STUDENT).


Coming back to the administrator account, to grant access to a user to write on the HDD, that user must be able to create a logical connection between the physical directory and the identifier. After this link has been established, the user will use only the identifier to access the content of the directory. Therefore, you should execute the command GRANT CREATE ANY DIRECTORY TO STUDENT; also from the administrator account.

Now, from the STUDENT account, you must create the logical link that I was talking about before. The directory to be visited will be "D:\STUDENT". For this, create the directory STUDENT (case sensitive), then, from the student account execute the command CREATE OR REPLACE DIRECTORY MYDIR as 'D:\STUDENT';

And as if all precautions were not yet sufficient, you must come back to the administrator account and grant to the user STUDENT to write or read to/from the directory newly created: GRANT READ, WRITE ON DIRECTORY MYDIR TO STUDENT;

If you have succeeded in doing all the steps above, the next code should create a file in which it should write the letters “abcdefg”.

DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_filer:=UTL_FILE.FOPEN('MYDIR','myfile.txt','W');
  UTL_FILE.PUTF(v_file,'abcdefg');
  UTL_FILE.FCLOSE(v_file);
END;
/

To see all the functions and procedures that you can use from UTL_FILE you can execute the command : DESC UTL_FILE;

To read the contents of a file you must open the file in reading mode (replace W with R in the function for opening the file) and after that, the reading is done with get_line:

set serveroutput on;
DECLARE
  v_file UTL_FILE.FILE_TYPE;
  v_string VARCHAR2(50);
BEGIN
  v_file:=UTL_FILE.FOPEN('MYDIR','myfile.txt','R');
  UTL_FILE.GET_LINE(v_file,v_string);
  DBMS_OUTPUT.PUT_LINE(v_string);
  UTL_FILE.FCLOSE(v_file);
END;
/


Exercises

1. (3pt) DB exporter:

Create a stored procedure that can export all the tables from the current user in a chosen format ( except SQL - that is without generating insert commands that could automatically populate the tables).

You might also want to try the commands:

SELECT table_name FROM user_tables;
SELECT column_name FROM all_col_comments WHERE lower(table_name) LIKE 'stud%'; 

2. (4pt) DB importer:

Create a stored procedure that receives as a parameter the name of a file and recreates the exported tables from the previous exercise (the tables will be created beforehand). Pay attention to the uniqueness constraint.

3. (3pt) Create a PHP application that will connect to an Oracle database server and exemplify through this application a SQL injection attack.