LuckyLucks
asked on
Stored proc to create user table via file import
HI
I need to create a user space table that is shared by every one with access to that schema. I have a csv file with three columns (all text) that I want to import into this table. I dont know if I can import into a user table in a stored proc in Oracle. Please help with syntax.
Thank you
I need to create a user space table that is shared by every one with access to that schema. I have a csv file with three columns (all text) that I want to import into this table. I dont know if I can import into a user table in a stored proc in Oracle. Please help with syntax.
Thank you
Creating a table is easy.
Creating a global temporary table is easy.
Loading a CSV file into a table is easy.
Creating a stored procedure to do work is easy.
Is the file on the client machine or the same server as the database?
Exactly what you are wanting to do is confusing. Please provide specifics on what you need to do.
>>2. Right click on tableName --> Import Data -> Select CSV file --> Verify Data - > Create Script and you are done.
Assuming they are using the same GUI as you... Can you specify what tool that is for?
Creating a global temporary table is easy.
Loading a CSV file into a table is easy.
Creating a stored procedure to do work is easy.
Is the file on the client machine or the same server as the database?
Exactly what you are wanting to do is confusing. Please provide specifics on what you need to do.
>>2. Right click on tableName --> Import Data -> Select CSV file --> Verify Data - > Create Script and you are done.
Assuming they are using the same GUI as you... Can you specify what tool that is for?
ASKER
No GUI, need to create a table and insert into it csv entries in something more automated script..
two options:
1-Create the table and use SQL*Loader to load the data
2-If the CSV file is on the database server create an External Table and it saves a couple of steps.
1-Create the table and use SQL*Loader to load the data
2-If the CSV file is on the database server create an External Table and it saves a couple of steps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
external table option is the declaration : organization external??
If not using external table, can we implement in a stored proc?
If not using external table, can we implement in a stored proc?
ASKER
THX
external table option is the declaration: "organization external"?
Yes.
"If not using external table, can we implement in a stored proc?"
Yes, that is another option to get data from a file into an Oracle table. For that you need:
1. A logical directory on the database server (just like for an "external table").
2. A PL/SQL procedure that uses procedures and functions in the supplied PL/SQL package: "utl_file" to: open, read a line (usually in a loop), and close the file after reading to the end.
3. This procedure can insert each record that it reads into your table.
A third option for getting data from a file into an Oracle table is to use SQL*Loader .
Yes.
"If not using external table, can we implement in a stored proc?"
Yes, that is another option to get data from a file into an Oracle table. For that you need:
1. A logical directory on the database server (just like for an "external table").
2. A PL/SQL procedure that uses procedures and functions in the supplied PL/SQL package: "utl_file" to: open, read a line (usually in a loop), and close the file after reading to the end.
3. This procedure can insert each record that it reads into your table.
A third option for getting data from a file into an Oracle table is to use SQL*Loader .
I would not use PL/SQL to load a CSV into an Oracle table. If you cannot use External Tables for some reason, I would use SQL*Loader to load the CSV into the Oracle table.
The downside is, the load process is done outside of the database.
The downside is, the load process is done outside of the database.
1. Create Table
Open in new window
2. Right click on tableName --> Import Data -> Select CSV file --> Verify Data - > Create Script and you are done.