如何在 SQL Developer 中读取本地文件？
当我在 Oracle SQL Developer 中执行查询时，我想读取本地计算机上包含查询参数的文件。到目前为止我在网上找到的例子还不够。当我执行以下代码时，我不断收到“ORA-29283：无效文件操作”错误：
CREATE DIRECTORY SAMPLEDATA2 AS 'C:';
GRANT READ, WRITE ON DIRECTORY SAMPLEDATA2 TO PUBLIC;
declare
f utl_file.file_type;
s varchar2(200);
c number := 0;
BEGIN
f := utl_file.fopen('SAMPLEDATA2','sample2.txt','R');
loop
utl_file.get_line(f,s);
dbms_output.put_line(s);
c := c + 1;
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
dbms_output.put_line('Number of lines: ' || c);
end;
I want to read a file on my local machine that contains query parameters when I execute a query in Oracle SQL developer. The examples that I've found on the web so far are inadequate. I keep getting "ORA-29283: invalid file operation" errors when I execute the below code:
CREATE DIRECTORY SAMPLEDATA2 AS 'C:';
GRANT READ, WRITE ON DIRECTORY SAMPLEDATA2 TO PUBLIC;
declare
f utl_file.file_type;
s varchar2(200);
c number := 0;
BEGIN
f := utl_file.fopen('SAMPLEDATA2','sample2.txt','R');
loop
utl_file.get_line(f,s);
dbms_output.put_line(s);
c := c + 1;
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
dbms_output.put_line('Number of lines: ' || c);
end;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
UTL_FILE只能从存储在数据库服务器上的文件中读取数据。由于它是 PL/SQL 代码，因此它运行在数据库服务器上，并且只能访问数据库服务器上 Oracle 进程可用的资源。UTL_FILEcan only read data from files that are stored on the database server. Since it is PL/SQL code, it runs on the database server and only has access to the resources that are available to the Oracle process on the database server.似乎是一个不寻常的 - 我想说的是“特殊” - 架构决策。这些值从哪里来？为什么它们必须存储在文件中？它们多久更改一次？
这将是非常大的将本地 PC 文件的内容公开给远程数据库服务器很困难（即我们正在讨论使用 ftp 或涉及 WinSCP 之类的手动过程来自动化它）
。将一些查询参数应用于查询非常简单；例如使用 SYS_CONTEXT 和命名空间。但在提供替代解决方案之前，我需要了解更多详细信息。
This seems an unusual - I was going to say 'peculiar' -architectural decision. Where do these values comne from? Why do thay have to be stored in a file? How often do they change?
It is going to be very difficult to expose the contents of a local PC file to a remote database server (i.e. we're talking automating it with
ftpor a manual process involving something like WinSCP).On the other hand, it could be quite simple to apply some query parameters to a query; for instance by using SYS_CONTEXT and namespaces. But I need to know more details before I can provide an alternative solution.