英文:
Loading data from csv file into oracle database
问题
我想将数据从CSV文件加载到Oracle数据库中的表中，使用外部表或PLSQL。请考虑我使用SQL*Plus远程连接到数据库。
我已阅读，要使用外部表或使用PLSQL加载数据，我需要首先创建一个目录，然后授予用户（需要访问该目录的用户）读取和写入权限。
- 我的第一步是以具有创建目录权限的用户身份连接到数据库，并执行以下语句：
SQL> create or replace directory MYCSV as 'c:\mycsv\';
但**c:**是什么意思？是我电脑的c驱动器，我能访问的那个吗？还是Oracle数据库所在的服务器的c驱动器，我无法访问的那个？
我的问题是我没有关于Oracle数据库运行的服务器的文件系统的任何信息。我不知道它是c:/、d:/还是e:/等。
我必须在哪里创建这个目录？在我的计算机（客户端）还是Oracle运行的服务器上。
MYCSV是一个物理目录吗？
- 第二个问题：我需要在我的计算机上安装SQL Loader或Oracle Data Pump来使用外部表吗？
英文:
I want to load data from a csv file into a table in the oracle database using external tables or plsql.
please considere that i connect remotly to the database using sql* plus.
I have read that to use external tables or load data with plsql i need to create a directory first and then grant read, write privileges to the user (the user that need access right to the directory).
- My first step would de to connect to the database as user that has create directory privilege and execute the following statement:
SQL> create or replace directory MYCSV as 'c:\mycsv\';
But what does the *c:* mean ? The c driver of my computer, which i have access to? Or the c driver of the server where the oracle database is running and which is not accessible for me?
My problem is that I do not have any information about the filesystem of the server where the oracle database is running. I do not know if it c:/, d:/ or e:/ etc.
Where do I have to create this directory? on my computer (client) or on the server where oracle is running.
Is mycsv is a physical directory?
2nd question: do I need to have sql Loader or oracle data pump on my computer to use external tables?
答案1
得分: 0
> 但是 c: 是什么意思？我可以访问我的计算机的c驱动器吗？还是指运行Oracle数据库的服务器的c驱动器，而我无法访问它？
从文档中（已加重）：
> 目录对象指定服务器文件系统上外部二进制文件LOBs（BFILEs）和外部表数据所在位置的别名。
目录对象无法看到您的客户端C：驱动器（除非您采取措施来共享它）；此外 - 主要是与安全相关的 - 不同的用户可以从不同的PC和客户端应用程序查看您的外部表，它们都需要看到相同的数据，这不能是个人的。
我需要在我的计算机上安装sql Loader或Oracle数据泵才能使用外部表吗？
不需要。外部表可以使用ORACLE_LOADER或ORACLE_DATAPUMP访问驱动程序，但这些不同于sqlldr或impdp工具。
但是...考虑到您的限制，最好使用SQL*Loader，通过sqlldr可执行文件，因为它将查看您计算机上的文件。
如果您尚未安装它，例如来自完整的Oracle客户端安装，那么您可以使用基本和工具包的即时客户端。如果您已经安装了用于SQL*Plus访问的即时客户端，那么您只需要在其上添加匹配版本的工具包。
英文:
> But what does the c: mean ? The c driver of my computer, which i have access to? Or the c driver of the server where the oracle database is running and which is not accessible for me?
From the documentation (emphasis added):
> A directory object specifies an alias for a directory on the server file system where external binary file LOBs (BFILEs) and external table data are located.
A directory object can't see your client C: drive (unless you jump through hoops to share it); apart from anything else - which is mostly security-related - different users could look at your external table from different PCs and client applications, and they all need to see the same data, which can't be personal to you.
> do I need to have sql Loader or oracle data pump on my computer to use external tables?
No. External tables can use the ORACLE_LOADER or ORACLE_DATAPUMP access drivers, but those are not the same as the sqlldr or impdp tools.
But... given your restrictions it would probably make sense to use SQL*Loader, via the sqlldr executable, as that will look at files on your PC.
If you don't already have it available, for example from a full Oracle client installation, then you can use instant client with the basic and tools packages. If you already have instant client for SQL*Plus access then you just need to add the matching version of the tools package on top.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论