从where子句中的txt文件中提取数据的脚本 [英] script that pulls data from a txt file in the where clause

问题描述

我正在尝试使用 toad for oracle 9.5 编写一个sql脚本，该脚本在运行时将允许用户从输入参数/变量的c驱动器的txt文件中选择数据. oracle的版本是10g

I am trying to write an sql script in toad for oracle version 9.5 that when run will allow the user to select data from a txt file from the c drive where the parameters / variables are entered. The version of oracle is 10g

推荐答案

听起来像更多.

Sounds like an Oracle external directory is perfect for the job. Keep in mind this approach is fraught with difficulties if this is a text file that users can write to. Users will do a hundred things you never believed possible that will cause errors and more.

摘自文章:

创建目录

授予读取权限，在目录data_dir上写入到your_user；

grant read, write on directory data_dir to your_user;

创建一个外部表

CREATE TABLE test_ext (
  test_code      VARCHAR2(5),
  test_name      VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      test_code      CHAR(5),
      test_name      CHAR(50)
    )
  )
  LOCATION ('test1.txt','test2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

然后从表中读取

SELECT *
    FROM   test_ext
    ORDER BY test_name;

您仍然可以使用不在数据库服务器上的目录来执行此操作，但是需要做更多的工作，这对数据库安全性和数据质量构成了更大的风险. 这种方法也无法扩展.您是否打算在每次添加新用户时添加新目录?

you can still do this with directories that are not located on the database server but more work is required and this represents even more of a risk to database security and data quality. This approach does not scale either. Do you intend to add a new directory every time a new user is added?

允许Oracle访问位于另一台计算机(假定为Windows操作系统)上的文件的步骤

Steps to allow Oracle to access files located on another machine (assuming Windows operating system)

• 创建一个Windows或域用户，该用户将对您希望访问的每个目录具有读取文件权限
• 在数据库服务器上运行services.msc，并将运行Oracle数据库服务的用户更改为您的新域用户.将此用户添加到数据库服务器上名为ORA_DBA的本地组中
• 重新启动数据库以使更改生效
• 使用类似//clientPc/sharedFolder
的路径在数据库中创建目录
• 授予数据库用户读取权限
• 在客户端计算机上授予对该文件夹中的域用户的读取权限
• 使用UTLFILE验证连接 在客户端PC上读取示例文件

• create a windows or domain user who will have read file permissions on every directory that you wish to access
• on the database server run services.msc and change the user that the Oracle database service runs on to the your new domain user. Add this user to the local group called ORA_DBA on the database server
• restart the database for the changes to take effect
• create a directory in the database using a path like //clientPc/sharedFolder
• grant read permissions to your database user
• on the client machine grant read to the domain user on that folder
• verify connectivity by using UTLFILE to read a sample file on the client pc

这篇关于从where子句中的txt文件中提取数据的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！