Home » RDBMS Server » Server Utilities » Load a CSV file into a GTT from a PL/SQL procedure
Load a CSV file into a GTT from a PL/SQL procedure [message #648841] Fri, 04 March 2016 10:20 Go to next message
rami_8001
Messages: 1
Registered: March 2016
Junior Member
Hello,

I would like to know to load an CSV directly in a Global Temporary Table (from PL/SQL Procedure).
Loading it in an external table doesn't help, cause I need to deal with a session-based data.

Thanks in advance.
Re: Load a CSV file into a GTT from a PL/SQL procedure [message #648846 is a reply to message #648841] Fri, 04 March 2016 13:22 Go to previous messageGo to next message
John Watson
Messages: 8297
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You cannot create a global temporary table as an external table:
orclz> create global temporary table gtt1(c1 number) organization external;
create global temporary table gtt1(c1 number) organization external
                                                           *
ERROR at line 1:
ORA-30658: attempt was made to create a temporary table with EXTERNAL organization


orclz>
You could open and read your CSV file with the utl_file procedures.
Re: Load a CSV file into a GTT from a PL/SQL procedure [message #648847 is a reply to message #648841] Fri, 04 March 2016 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can load it in the same way than in a permanent table.

Re: Load a CSV file into a GTT from a PL/SQL procedure [message #649215 is a reply to message #648847] Thu, 17 March 2016 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
>Loading it in an external table doesn't help, cause I need to deal with a session-based data.
What does above mean?
Please demonstrate with actual example.
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 Go to previous message
Barbara Boehmer
Messages: 8922
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
1,a,
2,b,


-- 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

Previous Topic: loader utility - slow performance
Next Topic: Excluding jobs/ scheduler jobs /dblinks from impdp
Goto Forum:
  


Current Time: Tue Jul 14 09:39:15 CDT 2020