oracle内置的程序包属于,oracle内置程序包

oracle内置程序包

SET SREVEROUTPUT ON;

--//扩大缓冲区

SET SERVEROUTPUT ON size 10000;

SQL> connect sys/beyond as sysdba

已连接。

SQL> drop directory TEST_DIR;

目录已丢弃。

SQL> CREATE DIRECTORY TEST_DIR AS 'C:/DEVELOP';

目录已创建。

SQL> GRANT READ,WRITE ON DIRECTORY TEST_DIR TO SCOTT;

授权成功。

SQL> connect scott/tiger

--//调用函数DBMS_XMLQuery.getXml把查询结果以CLOB类型的XML信息输出

SET PAGESIZE 1000;

SET LONG 20000;

SELECT DBMS_XMLQuery.getXml('SELECT * FROM stuInfo WHERE rownum < 5') FROM dual;

--//ora 9.2

DECLARE

result CLOB;

output_file   UTL_FILE.FILE_TYPE;

xmlstr VARCHAR2(32767);

line   VARCHAR2(2000);

line_no INTEGER := 1;

BEGIN

result := DBMS_XMLQuery.getXml('SELECT * FROM stuInfo WHERE rownum < 5');

xmlstr := DBMS_LOB.SUBSTR(result,32767);

output_file := UTL_FILE.FOPEN('TEST_DIR','emp.xml','w');--打开

LOOP

EXIT WHEN xmlstr IS NULL;

line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);

DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);

UTL_FILE.PUT(output_file,line || CHR(10));

xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);

line_no := line_no + 1;

END LOOP;

UTL_FILE.FCLOSE(output_file);--关闭

END;

/

--//随机数据

DECLARE

l_num   NUMBER;

counter NUMBER;

BEGIN

counter:=1;

WHILE counter <= 10

LOOP

l_num := DBMS_RANDOM.RANDOM;

DBMS_OUTPUT.PUT_LINE(l_num||'--'||ABS(l_num MOD 100));

counter:=counter+1;

END LOOP;

END;

--//读取文件

DECLARE

input_file   UTL_FILE.FILE_TYPE;

input_buffer VARCHAR2(4000);

BEGIN

input_file := UTL_FILE.FOPEN(

'TEST_DIR', 'emp.xml', 'r');

LOOP

UTL_FILE.GET_LINE(input_file,input_buffer);

DBMS_OUTPUT.PUT_LINE(input_buffer);

END LOOP;

UTL_FILE.FCLOSE(input_file);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('------------------');

END;

--//ora 9.0DECLARE  TYPE fType IS RECORD (id BINARY_INTEGER, datatype BINARY_INTEGER);  result CLOB;  output_file   fType;  xmlstr VARCHAR2(32767);  line   VARCHAR2(2000);  line_no INTEGER := 1;BEGIN  result := DBMS_XMLQuery.getXml('SELECT * FROM stuInfo WHERE rownum < 5');  xmlstr := DBMS_LOB.SUBSTR(result,32767);  output_file := UTL_FILE.FOPEN('TEST_DIR','emp.xml','w');--打开  LOOP    EXIT WHEN xmlstr IS NULL;    line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);    DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);    UTL_FILE.PUT(output_file,line || CHR(10));    xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);    line_no := line_no + 1;  END LOOP;   UTL_FILE.FCLOSE(output_file);--关闭END;