Solved

Stored proc to create user table via file import

Posted on 2016-09-27
9
53 Views
Last Modified: 2016-09-30
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
0
Comment
Question by:LuckyLucks
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41819062
Try ..

1. Create Table

CREATE TABLE TableName
(
    a INT
   ,b INT
   ,c INT
);

Open in new window


2. Right click on tableName --> Import Data -> Select CSV file --> Verify Data - > Create Script and you are done.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41819070
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?
0
 

Author Comment

by:LuckyLucks
ID: 41820117
No GUI, need to create a table and insert into it csv entries in something more automated script..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41820135
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 41822290
You will need a "create table..." script more like this:

CREATE TABLE [your_TableName]
(
    column_a  varchar2(30)
   ,column_b  varchar2(30)
   ,column_c varchar2(30)
)
organization external
(type oracle_loader
default directory [logical_dir_name]
access parameters
(records delimited by newline
 fields terminated by ','
 (column_a char(30,
 column_b  char(30),
 column_c  char(30)
)
location ('[filename.txt]')
);

Notes:
1. The [logical_dir_name] must be a valid logical directory in your database, so this must correspond to a physical directory on your server (or at least accessible by your database sever, like on a shared NFS directory for example)
2. The [filename.txt] value must be constant - you can't change this every day.  What you can do is copy a new file over the existing one to change the contents (but it must obviously have the same column structure).
3. I just guessed at a length of 30 bytes.  You will have to set that length to match your data.
4. If you use this "external table" option, you don't need a stored procedure to load it.  You just have to copy the new data file to the directory whenever you have new data to import.
0
 

Author Comment

by:LuckyLucks
ID: 41822561
external table option is the declaration : organization external??

If not using external table, can we implement in a stored proc?
0
 

Author Closing Comment

by:LuckyLucks
ID: 41823629
THX
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41823663
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 .
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41823673
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 66
Oracle Subquery bad Join 11 43
sql query 5 52
Schema creation in Oracle12c 6 23
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now