Oracle Note 14, querying XML operations, operating system files

--1. Random number

Select Dbms_random.value from dual;

Select mod (Dbms_random.random, ten) from dual;

--0-9 Random Number

Select ABS (mod (DBMS_RANDOM.RANDOM)) from dual;

--40-49 Random Number

Select + ABS (mod (DBMS_RANDOM.RANDOM)) from dual;

--2.xml

Declare

       Words Clob;

       Xmlstr varchar2 (32767);

       Line VARCHAR2 (2000);

       Line_no number: = 1;

Begin

     Words: = Dbms_xmlquery.getxml (' select * from Scott.emp ');

     XMLSTR: = Dbms_lob.substr (words, 32767);

     Loop

         Exit when (Xmlstr is null);

         Line: = substr (Xmlstr, 1, InStr (Xmlstr, Chr (10))-1);

         Dbms_output.put_line (Line_no | | ': ' | | line);

         XMLSTR: = substr (Xmlstr, InStr (Xmlstr, Chr (10)) + 1);

         Line_no: = line_no + 1;

     End Loop;

End

--3. File

--Define folder naming must be capitalized

Create directory My_dir as ' D:\TEMP ';

--Read the file

Declare

       Inputfile Utl_file.file_type; --File Object

       Input VARCHAR2 (2000);

Begin

     --Specify file

     --3 parameters are: Folder file open mode [R (Read) W (write) A (append)]

     Inputfile: = Utl_file.fopen (' My_dir ', ' demo.txt ', ' R ');

     Loop              

         Utl_file.get_line (inputfile, input);

         Dbms_output.put_line (input);             

     End Loop;

     --Close File

     Utl_file.fclose (Inputfile);

     exception

       When No_data_found then Dbms_output.put_line (' End of File! ');

End

--Writing files

Declare

       Inputfile Utl_file.file_type; --File Object

       Input VARCHAR2: = ' Hello world! ';

Begin

     --Specify file

     --3 parameters are: Folder file open mode [R (Read) W (write) A (append)]

     Inputfile: = Utl_file.fopen (' My_dir ', ' mydemo.txt ', ' a ');

     --Write Data

     Utl_file.put_line (inputfile, input);

     --Close File

     Utl_file.fclose (Inputfile);

     exception

       When No_data_found then Dbms_output.put_line (' End of File! ');

End

Oracle Note 14, querying XML operations, operating system files