數據庫人員手冊之輸出Oracle對象源碼
2024-08-29 13:50:00
供稿：網友
Oracle數據庫數據對象中最基本的是表和視圖，其他還有約束、序列、函數、存儲過程、包、觸發器等。對數據庫的操作可以基本歸結為對數據對象的操作,理解和把握Oracle數據庫對象是學習Oracle的捷徑。 way 1:
通過spool輸出到文件:set serveroutput on
spool 'c:/log.txt'DECLARE
v_text VARCHAR2(1000);
--i number(3):=0;
CURSOR cur IS
SELECT text
FROM sys.DBA_SOURCE
WHERE OWNER = 'SCOTT'
AND NAME = 'TESTCCB';
BEGIN
OPEN cur;
LOOP
FETCH cur
INTO v_text;
--DBMS_OUTPUT.PUT_LINE(' cityname = ' v_name' count=' i);
DBMS_OUTPUT.put_line(v_text);
--i := i+ 1;
EXIT WHEN cur%NOTFOUND;
END LOOP; CLOSE cur;
END;
/
spool off
way2:
利用 utl_file 包直接寫出到文件
要求oracle用戶對os文件系統有操作權限DECLARE
v_text VARCHAR2(1000);
v_dir VARCHAR2(256);
v_owner VARCHAR2(128);
v_obj VARCHAR2(128);
l_output utl_file.file_type;
CURSOR cur IS
SELECT text
FROM dba_source
WHERE OWNER = v_owner
AND NAME = v_obj;
BEGIN
v_owner := 'SCOTT';
v_obj := 'EMP';
SELECT t.Directory_path INTO v_dir FROM all_directories t;
l_output := utl_file.fopen(v_dir, 'tab.txt', 'w');
utl_file.new_line(l_output);
utl_file.put_line(l_output,
'-- output owner :' v_owner ' object: '
v_obj); OPEN cur;
LOOP
FETCH cur
INTO v_text;
EXIT WHEN cur%NOTFOUND;
utl_file.new_line(l_output);
utl_file.put_line(l_output, v_text);
END LOOP;
utl_file.new_line(l_output);
utl_file.put_line(l_output, '-- output finished! '); utl_file.fclose(l_output);
CLOSE cur;
END;
/
