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?
>Solution :
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.