ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 写文件

oracle 写文件

原创 Linux操作系统 作者:Nalternative 时间:2011-02-12 19:03:42 0 删除 编辑

/
--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;


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-687099/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle表空间查看
请登录后发表评论 登录
全部评论

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    177742