--login system
CREATE DIRECTORY | as '/appl/appldev2/dev2appl/ofc/11.5.0';
grant read,write on directory test_dir to apps;
--grant execute on utl_file to apps;
-- login apps
DECLARE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE := UTL_FILE.FOPEN('TEST_DIR', SYSDATE||'.txt', 'w',32767);
UTL_FILE.put_line(FILE_HANDLE, 'Test all directories!');
UTL_FILE.FCLOSE(FILE_HANDLE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
------------------------------------------------------------------
........
open c_gl_data;
loop
fetch c_gl_data into l_effective_date,l_currency_code,l_attribute
,l_proc_unit,l_business,l_cost_center
, l_acct, l_je_number, l_ref, l_desc1
, l_desc2, l_curr1, l_amt1, l_curr2, l_amt2
, l_posting_period, l_logonid, l_src, l_filter
, l_doc_sequence_value, l_header_name, l_je_line_num, l_batch_name, l_description;
EXIT WHEN c_gl_data%NOTFOUND;
if l_attribute != 'CNY' and l_attribute != 'USD' then
l_attribute := l_currency_code;
end if;
if substrb(l_desc2||l_desc1||'`',73,1) != '`' then
FND_FILE.PUT_LINE(FND_FILE.LOG,'');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Warning: Wrong encoding of descrption for this line: '
||'Account:'||l_acct
||' Document num:'||l_doc_sequence_value
||' Header Name:'||l_header_name
||' Line num:'||l_je_line_num
||' Batch name:'||l_batch_name
||' Description:'||l_description);
end if;
utl_file.put_line(fileHandler,
l_proc_unit ||l_business ||l_cost_center ||
l_acct ||l_je_number || l_ref || l_desc2 ||
l_desc1 || l_curr1 ||l_amt1 || l_curr2 || l_amt2 ||
l_posting_period ||l_logonid ||l_src ||l_filter);
------------------------------------------------------------------------------------
Create directory让我们可以在Oracle数据库中灵活的对文件进行读写操作,极大的提高了Oracle的易用性和可扩展性。
其语法为:
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
本案例具体创建如下:
|
目录创建以后,就可以把读写权限授予特定用户,具体语法如下:
GRANT READ[,WRITE] ON DIRECTORY directory TO username;
例如:
|
此时用户eygle就拥有了对该目录的读写权限。
|
类似的我们可以通过utl_file来读取文件:
|
可以查询dba_directories查看所有directory.
|
可以使用drop directory删除这些路径.
|