32767oracle写文件,oracle 写文件

/

--CREATE OR REPLACE DIRECTORY FILEPATH1 AS 'D:\MYPROC'

--PROC_COLLEGE_SMS_DISPATCHER_V2

--PROC_IMPORT_NODEAL.SQL

/*

将数据写入文件，以下sql将存储过程的代码分别写入文件；

视图ALL_DIRECTORIES可以查看创建的DIRECTORY

有时会出现：oracle字符串缓冲区太小的错误

写excel也可以

*/

DECLARE

TYPE CUR IS REF CURSOR;

V_CUR CUR;

MYTXT VARCHAR2(4000);

L_FILE UTL_FILE.FILE_TYPE;   --utl_file包的操作需要指定一个file_type型的对象

MX VARCHAR2(100);

BEGIN

FOR X IN (

SELECT AO.OBJECT_NAME ONAME

FROM DBA_OBJECTS AO

WHERE AO.OWNER='EDU'

AND AO.OBJECT_TYPE='PROCEDURE'

)

LOOP

MX:=X.ONAME;

L_FILE :=UTL_FILE.FOPEN('FILEPATH1',MX||'.SQL','W');

UTL_FILE.PUT_LINE(L_FILE,'CREATE OR REPLACE');

OPEN V_CUR FOR

SELECT DS.TEXT

FROM DBA_SOURCE DS

WHERE DS.OWNER='EDU'

AND DS.NAME=MX;

LOOP

FETCH  V_CUR INTO MYTXT;

EXIT WHEN V_CUR%NOTFOUND;

UTL_FILE.PUT(L_FILE,MYTXT);

END LOOP;

CLOSE  V_CUR;

UTL_FILE.FFLUSH(L_FILE);

UTL_FILE.FCLOSE(L_FILE);

END LOOP;

END;

/*

SELECT DS.TEXT

FROM DBA_SOURCE DS

WHERE DS.OWNER='JASON'

AND DS.NAME=''

create or replace directory UTL_DIR as 'D:\wang';

/

create or replace procedure p_exportDLL(p_table_name varchar2,p_filename varchar2) is

begin

declare

l_file     UTL_FILE.FILE_TYPE;

l_buffer   VARCHAR2(1000);

l_amount   BINARY_INTEGER := 100;

l_pos      INTEGER := 1;

l_clob     clob;

l_clob_len INTEGER;

begin

select dbms_metadata.get_ddl('PROCEDURE',p_table_name,'WARDER' )||';' into l_clob from dual;

l_clob_len := DBMS_LOB.GETLENGTH(l_clob);

l_file     := UTL_FILE.FOPEN('UTL_DIR', p_filename||'.sql', 'a', 1000);

WHILE l_pos < l_clob_len LOOP

DBMS_LOB.READ(l_clob, l_amount, l_pos, l_buffer);

UTL_FILE.put(l_file, l_buffer);

l_pos := l_pos + l_amount;

END LOOP;

UTL_FILE.FCLOSE(l_file);

--if @@error

end;

end p_exportDLL;

/

CREATE OR REPLACE PROCEDURE p_whole AS

BEGIN

FOR x IN (SELECT OBJECT_NAME FROM ALL_OBJECTS AO WHERE AO.OBJECT_TYPE='PROCEDURE' AND WNER='WARDER') LOOP

p_exportDLL(x.OBJECT_NAME,x.OBJECT_NAME);

END LOOP;

END  p_whole;

/

begin

p_whole;

end;

/

CREATE OR REPLACE DIRECTORY UTL_DIR AS 'D:\WANG';

/

CREATE OR REPLACE PROCEDURE P_EXPORTDLL(P_TABLE_NAME VARCHAR2,P_FILENAME VARCHAR2) IS

BEGIN

DECLARE

L_FILE     UTL_FILE.FILE_TYPE;

L_BUFFER   VARCHAR2(1000);

L_AMOUNT   BINARY_INTEGER := 100;

L_POS      INTEGER := 1;

L_CLOB     CLOB;

L_CLOB_LEN INTEGER;

V_BUFFER VARCHAR2(100);

V_NUM NUMBER;

BEGIN

--获得DDL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',P_TABLE_NAME,'WARDER' ) INTO L_CLOB FROM DUAL;

--增加存储过程的权限

FOR X IN(SELECT  'EDU' USER_NAME FROM DUAL

UNION

SELECT  'WEBUSER' FROM DUAL

UNION

SELECT  'JSPUSER'FROM DUAL

UNION

SELECT 'GATEWAY'FROM DUAL

UNION

SELECT 'EDUCATE' FROM DUAL)

LOOP

IF DBMS_LOB.INSTR(

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', X.USER_NAME),

'GRANT EXECUTE ON "WARDER"."'||P_FILENAME||'" TO "'||X.USER_NAME||'"',

1,

1)>0 THEN

V_BUFFER:=chr(10)||'/'||chr(10)||'GRANT EXECUTE ON WARDER.'||P_FILENAME||' TO '||X.USER_NAME||chr(10);

V_NUM:=LENGTH(V_BUFFER);

DBMS_LOB.WRITEAPPEND(L_CLOB,V_NUM,V_BUFFER);

END IF;

END LOOP;

V_BUFFER:='/';

V_NUM:=LENGTH(V_BUFFER);

DBMS_LOB.WRITEAPPEND(L_CLOB,V_NUM,V_BUFFER);

L_CLOB_LEN := DBMS_LOB.GETLENGTH(L_CLOB);

L_FILE     := UTL_FILE.FOPEN('UTL_DIR', P_FILENAME||'.SQL', 'A', 1000);

--写文件

WHILE L_POS < L_CLOB_LEN LOOP

DBMS_LOB.READ(L_CLOB, L_AMOUNT, L_POS, L_BUFFER);

UTL_FILE.PUT(L_FILE, L_BUFFER);

L_POS := L_POS + L_AMOUNT;

END LOOP;

UTL_FILE.FCLOSE(L_FILE);

--IF @@ERROR

END;

END P_EXPORTDLL;

/

CREATE OR REPLACE PROCEDURE P_WHOLE AS

BEGIN

FOR X IN (SELECT OBJECT_NAME FROM ALL_OBJECTS AO WHERE AO.OBJECT_TYPE='PROCEDURE' AND WNER='WARDER') LOOP

P_EXPORTDLL(X.OBJECT_NAME,X.OBJECT_NAME);

END LOOP;

END  P_WHOLE;

/

BEGIN

P_WHOLE;

END;

/

CREATE OR REPLACE DIRECTORY UTL_DIR AS 'D:\WANG';

/

CREATE OR REPLACE PROCEDURE P_EXPORTDLL(P_TABLE_NAME VARCHAR2,--表名

P_FILENAME VARCHAR2,--文件名

P_USER VARCHAR2) IS--用户名

BEGIN

DECLARE

L_FILE     UTL_FILE.FILE_TYPE;

L_BUFFER   VARCHAR2(1000);

L_AMOUNT   BINARY_INTEGER := 100;

L_POS      INTEGER := 1;

L_CLOB     CLOB;

L_CLOB_LEN INTEGER;

V_BUFFER VARCHAR2(100);

V_NUM NUMBER;

BEGIN

--获得DDL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',P_TABLE_NAME,P_USER ) INTO L_CLOB FROM DUAL;

--增加存储过程的权限

FOR X IN(SELECT  'EDU' USER_NAME FROM DUAL

UNION

SELECT  'WEBUSER' FROM DUAL

UNION

SELECT  'JSPUSER'FROM DUAL

UNION

SELECT 'GATEWAY'FROM DUAL

UNION

SELECT 'EDUCATE' FROM DUAL)

LOOP

IF DBMS_LOB.INSTR(

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', X.USER_NAME),

'GRANT EXECUTE ON "'||P_USER||'"."'||P_FILENAME||'" TO "'||X.USER_NAME||'"',

1,

1)>0 THEN

V_BUFFER:=CHR(10)||'/'||CHR(10)||'GRANT EXECUTE ON '||P_USER||'.'||P_FILENAME||' TO '||X.USER_NAME||CHR(10);

V_NUM:=LENGTH(V_BUFFER);

DBMS_LOB.WRITEAPPEND(L_CLOB,V_NUM,V_BUFFER);

END IF;

END LOOP;

--权限后边加上/

V_BUFFER:='/';

V_NUM:=LENGTH(V_BUFFER);

DBMS_LOB.WRITEAPPEND(L_CLOB,V_NUM,V_BUFFER);

L_CLOB_LEN := DBMS_LOB.GETLENGTH(L_CLOB);

L_FILE     := UTL_FILE.FOPEN('UTL_DIR', P_FILENAME||'.SQL', 'A', 1000);

--写文件

WHILE L_POS < L_CLOB_LEN LOOP

DBMS_LOB.READ(L_CLOB, L_AMOUNT, L_POS, L_BUFFER);

UTL_FILE.PUT(L_FILE, L_BUFFER);

L_POS := L_POS + L_AMOUNT;

END LOOP;

UTL_FILE.FCLOSE(L_FILE);

END;

END P_EXPORTDLL;

/

DECLARE

V_USER VARCHAR2(30):='WARDER';--查询WARDER下的对象

BEGIN

FOR X IN (SELECT OBJECT_NAME FROM ALL_OBJECTS AO WHERE AO.OBJECT_TYPE='PROCEDURE' AND WNER=V_USER) LOOP

P_EXPORTDLL(X.OBJECT_NAME,X.OBJECT_NAME,V_USER);

END LOOP;

END  P_WHOLE;