RAC SQL and PLSQL (e)
Next: RAC Backups and Recovery
Our second PL/SQL test will look at the UTL_FILE package. With any PL/SQL operations on RAC you must be aware that the code could execute on any node where its service lives. This could also impact packages like DBMS_PIPE, UTL_MAIL, UTL_HTTP (proxy server source IP rules for example), or even DBMS_RLS (refreshing policies).
Login to RAC1 as sh and create a file that we can try reading later.
collabn1:/home/oracle[RAC1]$ sqlplus sh/sh@RAC1 create directory orahome as '/home/oracle'; declare fl utl_file.file_type; begin fl := utl_file.fopen('ORAHOME','data.txt','w'); utl_file.put_line(fl, 'Some sample data for an oracle test.', TRUE); utl_file.fclose(fl); end; /
Exit SQLPLUS. At the prompt, copy this command to connect to the RAC service as sh again and attempt to read the file you just wrote. Run this command 10-20 times in a row. (Cut-and-paste is recommended.) What happens? Why?
sqlplus -S sh/sh@RAC <<EOF declare fl utl_file.file_type; data varchar2(1024); begin fl := utl_file.fopen('ORAHOME','data.txt','r'); utl_file.get_line(fl, data); utl_file.fclose(fl); end; / exit; EOF