|
|
|
|
Re: Load a CSV file into a GTT from a PL/SQL procedure [message #649216 is a reply to message #649215] |
Thu, 17 March 2016 17:48 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You could use an external table to access the data and load it through a procedure, without having any session issues. Please see the demonstration below.
-- test file for demonstration:
SCOTT@orcl> host type test.dat
-- one time creation of Oracle directory object, external table, global temporary table, and procedure:
SCOTT@orcl> create or replace directory mydir as 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl> create table staging
2 (col1 number,
3 col2 varchar2(4))
4 organization external
5 (type oracle_loader
6 default directory mydir
7 access parameters
8 (records delimited by newline
9 fields terminated by ','
10 missing field values are null
11 (col1, col2))
12 location (''))
13 /
Table created.
SCOTT@orcl> create global temporary table gtt1 (c1 number, c2 varchar2(4))
2 /
Table created.
SCOTT@orcl> create or replace procedure load_data
2 (p_file in varchar2)
3 as
4 begin
5 execute immediate 'alter table staging location (''' || p_file || ''')';
6 insert into gtt1 (c1, c2) select col1, col2 from staging;
7 end load_data;
8 /
Procedure created.
SCOTT@orcl> show errors
No errors.
-- execution of procedure, passing file name as parameter, and results:
SCOTT@orcl> exec load_data ('test.dat')
PL/SQL procedure successfully completed.
SCOTT@orcl> select * from gtt1
2 /
C1 C2
---------- ----
1 a
2 b
2 rows selected.
-- new session:
SCOTT@orcl> connect scott/tiger
Connected.
SCOTT@orcl> select * from gtt1
2 /
no rows selected
[Updated on: Thu, 17 March 2016 17:52] Report message to a moderator
|
|
|