| Query Issue and Help in Query merged [message #537022] |
Mon, 26 December 2011 02:13  |
 |
aallan
Messages: 150
Registered: October 2011
|
Senior Member |
|
|
 Dear All;
My issue is:
i used sql loader to import data from csv file to my db.
but every time the columns places are changed.
so i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.
also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
i think its will be looks like a tree...first row in the query will be in level = 1 and the rest will be in level = 2
any idea ??
-
Attachment: 1.PNG
(Size: 54.71KB, Downloaded 1555 times)
[Updated on: Mon, 26 December 2011 02:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query Issue [message #537104 is a reply to message #537101] |
Mon, 26 December 2011 15:00  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can load it into a staging table using a sequence so that the heading line with the column names is the row with sequence 1. Then you can load that from the staging table to the correct columns in the target table, based on the headings in the row with sequence 1. In the demonstration below, I have assumed that the data is tab-delimited. It uses two data files with the columns and corresponding headings in different orders. I have used a procedure to do the loading from staging table to target table.
-- test1.dat:
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
-- test2.dat:
DNAME LOC DEPTNO
SALES CHICAGO 30
OPERATIONS BOSTON 40
-- test.ctl:
load data
replace
into table staging
fields terminated by x'09'
trailing nullcols
(seq sequence, col1, col2, col3)
-- staging table:
SCOTT@orcl_11gR2> create table staging
2 (seq number,
3 col1 varchar2 (4000),
4 col2 varchar2 (4000),
5 col3 varchar2 (4000))
6 /
Table created.
-- target table:
SCOTT@orcl_11gR2> create table target
2 (deptno number,
3 dname varchar2 (15),
4 loc varchar2 (15))
5 /
Table created.
-- procedure to load data from staging table to target table:
SCOTT@orcl_11gR2> create or replace procedure staging_to_target
2 as
3 v_sql varchar2 (32767);
4 begin
5 select 'insert into target ('
6 || col1 || ',' || col2 || ',' || col3 || ')
7 select col1, col2, col3
8 from staging
9 where seq > 1'
10 into v_sql
11 from staging
12 where seq = 1;
13 execute immediate v_sql;
14 end staging_to_target;
15 /
Procedure created.
-- loads:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl data=test1.dat log=test1.log
SCOTT@orcl_11gR2> exec staging_to_target
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl data=test2.dat log=test2.log
SCOTT@orcl_11gR2> exec staging_to_target
PL/SQL procedure successfully completed.
-- results:
SCOTT@orcl_11gR2> select * from target
2 /
DEPTNO DNAME LOC
---------- --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
|
|
|
|
| Re: Query Issue [message #537110 is a reply to message #537104] |
Mon, 26 December 2011 18:58 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This thread got me thinking about the possibilities of creating a generic method for creating a table from a delimited file with column headings without knowing anything but the location of the file, name of the file, and the delimiter. I came up with the following method that should work for most typical delimited files with column headings and rows <= 4000 characters that can be placed into a directory on the server. Once you have done a one-time creation of the Oracle directory object, the external table, and the procedure, all you have to do to create the table from the file is execute the procedure, passing the file name, delimiter, and table name. I have demonstrated it below.
-- create oracle directory object that points to operating system path
-- on server where files to be loaded into tables will be:
SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
2 /
Directory created.
-- create staging table:
SCOTT@orcl_11gR2> create table staging
2 (col1 varchar2(4000))
3 ORGANIZATION external
4 (
5 TYPE oracle_loader
6 DEFAULT DIRECTORY MY_DIR
7 ACCESS PARAMETERS
8 (
9 RECORDS DELIMITED BY NEWLINE
10 LOGFILE 'staging.log'
11 FIELDS LDRTRIM
12 MISSING FIELD VALUES ARE NULL
13 REJECT ROWS WITH ALL NULL FIELDS
14 ("COL1" CHAR(4000))
15 )
16 location ('staging.dat')
17 )REJECT LIMIT UNLIMITED
18 /
Table created.
-- create procedure to accept file name, delimiter, and table name as parameters
-- and create table of specified table name from file name:
SCOTT@orcl_11gR2> create or replace procedure create_source_tab
2 (p_filename in varchar2,
3 p_delim in varchar2,
4 p_tablename in varchar2)
5 as
6 v_sql varchar2 (32767);
7 v_count number;
8 begin
9 execute immediate 'alter table staging location (''' || p_filename || ''')';
10 select 'create table ' || p_tablename || '(rn,'
11 || replace (col1, p_delim, ',')
12 || ') as select * from (select rownum rn'
13 into v_sql
14 from staging
15 where rownum = 1;
16 --
17 select regexp_count (col1, p_delim) + 1
18 into v_count
19 from staging where rownum = 1;
20 --
21 for i in 1 .. v_count
22 loop
23 v_sql := v_sql ||
24 ',substr
25 (''' || p_delim || ''' || col1 || ''' || p_delim || ''',
26 instr (''' || p_delim || ''' || col1 || ''' || p_delim || ''', '''
27 || p_delim || ''', 1, ' || i || ') + ' || length (p_delim) || ',
28 instr (''' || p_delim || ''' || col1 || ''' || p_delim
29 || ''', ''' || p_delim || ''', 1, ' || to_char (i + 1) || ')
30 - instr (''' || p_delim || ''' || col1 || ''' || p_delim || ''', '''
31 || p_delim || ''', 1, ' || i || ')
32 - ' || length (p_delim) || ')';
33 end loop;
34 v_sql := v_sql || ' from staging) where rn > 1';
35 execute immediate v_sql;
36 end create_source_tab;
37 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
-- test1.dat:
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
-- test2.dat:
DNAME LOC DEPTNO
SALES CHICAGO 30
OPERATIONS BOSTON 40
-- create source_tab1 from test1.dat:
SCOTT@orcl_11gR2> exec create_source_tab ('test1.dat', chr(9), 'source_tab1')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> column deptno format a15
SCOTT@orcl_11gR2> column dname format a15
SCOTT@orcl_11gR2> column loc format a15
SCOTT@orcl_11gR2> select * from source_tab1
2 /
RN DEPTNO DNAME LOC
---------- --------------- --------------- ---------------
2 10 ACCOUNTING NEW YORK
3 20 RESEARCH DALLAS
2 rows selected.
-- create soruce_tab2 from test2.dat:
SCOTT@orcl_11gR2> exec create_source_tab ('test2.dat', chr(9), 'source_tab2')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select * from source_tab2
2 /
RN DNAME LOC DEPTNO
---------- --------------- --------------- ---------------
2 SALES CHICAGO 30
3 OPERATIONS BOSTON 40
2 rows selected.
-- create target table:
SCOTT@orcl_11gR2> create table target
2 (deptno number,
3 dname varchar2 (15),
4 loc varchar2 (15))
5 /
Table created.
-- insert into target table from source_tab1 and source_tab2:
SCOTT@orcl_11gR2> insert into target
2 select deptno, dname, loc from source_tab1
3 union all
4 select deptno, dname, loc from source_tab2
5 /
4 rows created.
-- results:
SCOTT@orcl_11gR2> column deptno format 9999
SCOTT@orcl_11gR2> select * from target
2 /
DEPTNO DNAME LOC
------ --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
[Updated on: Mon, 26 December 2011 19:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help in Query [message #537268 is a reply to message #537265] |
Tue, 27 December 2011 07:52 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Yes; something like this:
SQL> with test as
2 (select 'abc,123,xyz' col from dual)
3 select length(col) - length(replace(col, ',', '')) + 1 v_count
4 from test;
V_COUNT
----------
3
P.S. Barbara's code, rewritten:
select length(col1) - length(replace(col1, p_delim, '')) + 1
into v_count
from staging where rownum = 1; [Updated on: Tue, 27 December 2011 07:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Help in Query [message #537278 is a reply to message #537275] |
Tue, 27 December 2011 08:24 |
|
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Unless I'm wrong, it is supposed to execute CREATE TABLE statement. Do you have that privilege?
P.S. Forgot to mention: even if you do, was it granted to you via role or directly to your user? Because, privileges acquired via roles won't work in PL/SQL - you'll need to be granted directly.
[Updated on: Tue, 27 December 2011 08:25] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query Issue and Help in Query merged [message #537303 is a reply to message #537022] |
Tue, 27 December 2011 12:01 |
|
aallan
Messages: 150
Registered: October 2011
|
Senior Member |
|
|
first, i wanna tell u that your a genius man, its very great idea.
no errors and the table doesn't create...
and when i put messages to trace the code it came into 1 and didnt show message # 2
message(1);pause;
Create_Source_Tab(:Blk_1.File_Name, ';', 'Import_Stc_Daily_Reports_Tbl');
message(2);pause;
Please help
[Updated on: Tue, 27 December 2011 12:05] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Issue and Help in Query merged [message #537319 is a reply to message #537313] |
Tue, 27 December 2011 13:02 |
|
aallan
Messages: 150
Registered: October 2011
|
Senior Member |
|
|
I added a lot of insert statement as:
CREATE OR REPLACE Procedure SALE.Create_Source_Tab (P_Filename In Varchar2,
P_Delim In Varchar2,
P_Tablename In Varchar2)
As
V_Sql Varchar2 (32767);
V_Count Number;
Begin
insert into orafaq (datum) values (1);
Execute Immediate 'alter table staging location (''' || P_Filename || ''')';
insert into orafaq (datum) values (2);
Select 'create table ' || P_Tablename || '(rn,'
|| Replace (Col1, P_Delim, ',')
|| ') as select * from (select rownum rn'
Into V_Sql
From Staging
Where Rownum = 1;
insert into orafaq (datum) values (3);
Select Length(Col1) - Length(Replace(Col1, P_Delim, '')) + 1
Into V_Count
From Staging
Where Rownum = 1;
insert into orafaq (datum) values (4);
For I In 1 .. V_Count
Loop
insert into orafaq (datum) values (5);
V_Sql := V_Sql ||
',substr
(''' || P_Delim || ''' || col1 || ''' || P_Delim || ''',
instr (''' || P_Delim || ''' || col1 || ''' || P_Delim || ''', '''
|| P_Delim || ''', 1, ' || I || ') + ' || Length (P_Delim) || ',
instr (''' || P_Delim || ''' || col1 || ''' || P_Delim
|| ''', ''' || P_Delim || ''', 1, ' || To_Char (I + 1) || ')
- instr (''' || P_Delim || ''' || col1 || ''' || P_Delim || ''', '''
|| P_Delim || ''', 1, ' || I || ')
- ' || Length (P_Delim) || ')';
End Loop;
insert into orafaq (datum) values (6);
V_Sql := V_Sql || ' from staging) where rn > 1';
insert into orafaq (datum) values (7);
Execute Immediate V_Sql;
Execute Immediate 'Alter Table '||P_Tablename ||' Add '||'Import_Id'||' '|| 'Number';
Execute Immediate 'Alter Table '||P_Tablename ||' Add '||'Import_Date'||' '|| 'Date';
End Create_Source_Tab;
/
just value 1 inserted!!
Privileges like what ?
|
|
|
|
|
|
|
|
|
|
| Re: Query Issue and Help in Query merged [message #537330 is a reply to message #537329] |
Tue, 27 December 2011 13:22 |
|
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Well, no - you don't grant privileges to yourself.
Connected as SALE to SQL*Plus, does this work (for me, it does - ALTER TABLE finished successfully):SQL> create table orafaq (col number(1));
Table created.
SQL> alter table orafaq modify col number(2);
Table altered.
SQL>
[EDIT] Uh, oh ... now I'm being stupid. Owner has full control over his objects, so CREATE TABLE covers ALTER TABLE as well, no additional privilege is required.
It means that READ on a directory is all that remains, eh?
[Updated on: Tue, 27 December 2011 13:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Issue and Help in Query merged [message #537336 is a reply to message #537334] |
Tue, 27 December 2011 13:45 |
|
aallan
Messages: 150
Registered: October 2011
|
Senior Member |
|
|
Barbara Boehmer wrote on Tue, 27 December 2011 13:35What is the value of PKG_FICHIERS.Selection that is being passed to p_filename? It should be just a file name, not a path. Does it have an extension? Perhaps it is case sensitive on your system. Is it upper or lower or mixed case? Does it match what is on your operating system? Is it the same file, with the same delimiter, that you used in your previous test from Toad?
That's right...
when i changed:
Sale.Create_Source_Tab(Substr(:Blk_1.File_Name, 4), ';', 'Import_Stc_Daily_Reports_Tbl');
because the file name value was passed like:
I cut and pass just a file name>>>>> wok well....
Thank you littlefoot...
Thank you Genius man Barbara Boehmer...
so, if i want to repeate the process, i mean if i want to upload file for second tile i should drop the table ?
[Updated on: Tue, 27 December 2011 13:47] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
