Home » RDBMS Server » Server Utilities » SQL Loader and Escape Characteres (ORacle 11)
| SQL Loader and Escape Characteres [message #495852] |
Tue, 22 February 2011 16:04  |
 |
aucrun
Messages: 114
Registered: February 2011
|
Senior Member |
|
|
Hi,
I'm having a problem that is making me going crazy, and need some help about it.
I'm importing an informix database 9 to oracle 11, and informix uses | as separator.
So far so good ... but in some cases I have certains tables which columns have the | character (sometimes multiple times within that field).
I realise that Informix puts the backlash as a Escape Character but I am not being able to use it with SQL Loader.
Example of value: (with just 2 columns)
20|text1|xxx\|zzz\|aaa\|bbb)|
Where "xxx\|zzz\|aaa\|bbb)" is the value of the last field within the exported file. In informix database, that value is "xxx|zzz|aaa|bbb)" wich means that informix is creting the escape character correctly.
The problem is that I am not being able to use the Escape Character to identify that \| is just an |...
Can anyone help me on this?
Thanks!
|
|
|
|
| Re: SQL Loader and Escape Characteres [message #495856 is a reply to message #495852] |
Tue, 22 February 2011 20:08  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Hi,
I see this is your first post, so welcome to the OraFAQ forums.
There are various ways to approach this problem. In the following example, I have set the new line "str \n" to be both the record terminator and field terminator. I have added one column, named all_columns, of type boundfiller, to the control file, and loaded the whole row into that column. I have created a list_element function that accepts such a row as a string, replaces each | with ~| and then replaces each \~| with |. So, it takes that all_columns row of concatenated fields, converts the field terminator to ~ and leaves the other | intact without the \ escape character. This function then returns a field, depending on the numeric parameter passed to it. The function is used in the SQL*Loader control file to extract column1, column2, and column3 from the all_columns column. The end result is that in one pass of SQL*Loader, all three columns are loaded properly.
-- test.ctl:
load data
infile * "str \n"
into table target_table
fields terminated by "str \n"
trailing nullcols
( all_columns boundfiller char (4000)
, column1 expression "list_element (:all_columns, 1)"
, column2 expression "list_element (:all_columns, 2)"
, column3 expression "list_element (:all_columns, 3)")
begindata:
20|text1|xxx\|zzz\|aaa\|bbb)|
10|text2|ccc\|ddd\|eee\|fff)|
-- table to load data into:
SCOTT@orcl_11gR2> create table target_table
2 (column1 number,
3 column2 varchar2 ( 7),
4 column3 varchar2 ( 16))
5 /
Table created.
-- function to replace delimiters and extract fields:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION list_element
2 (p_string IN VARCHAR2,
3 p_element IN NUMBER)
4 RETURN VARCHAR2
5 AS
6 v_string VARCHAR2 (32767);
7 BEGIN
8 v_string := '~' || replace (replace (p_string, '|', '~'), '\~', '|') || '~';
9 RETURN SUBSTR
10 (v_string,
11 INSTR (v_string, '~', 1, p_element) + 1,
12 INSTR (v_string, '~', 1, p_element + 1)
13 - INSTR (v_string, '~', 1, p_element) - 1);
14 END list_element;
15 /
Function created.
-- load data:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
-- results:
SCOTT@orcl_11gR2> select * from target_table
2 /
COLUMN1 COLUMN2 COLUMN3
---------- ------- ----------------
20 text1 xxx|zzz|aaa|bbb)
10 text2 ccc|ddd|eee|fff)
2 rows selected.
SCOTT@orcl_11gR2>
[Updated on: Tue, 22 February 2011 20:16] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader and Escape Characteres [message #495935 is a reply to message #495931] |
Wed, 23 February 2011 06:01 |
|
aucrun
Messages: 114
Registered: February 2011
|
Senior Member |
|
|
Hi,
I've tried a simple alteration of your code, using the function only in the column I know that might have the '\|' characters, but it cointinues interpreting the '|' as a field separator ....
The problem, I think, is that Oracle is interpreting '|' character before executing the Function!
Running the function in PL/SQL it results correctly ... but running oon SQL Loader, it gives me allways the first chars before the first '\|'
[Updated on: Wed, 23 February 2011 06:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Loader and Escape Characteres [message #495987 is a reply to message #495947] |
Wed, 23 February 2011 11:10 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
aucrun wrote on Wed, 23 February 2011 05:51
1- With this model can I use custom formatting for each field?
For instance, a column1 like Current_date if it is null.
Yes, you can nest functions within the expressions, as long as you do not exceed the maximum character limit for the expression. If it is too long, then you need to put it all in one function and then use that function. In the revised example below, I have added a date_col column of date datatype. I have used to_date around the call to list_element and provided the date format that matches my sample data. I have then wrapped an nvl function around that to use the current_date if the value is null.
-- test.ctl:
load data
infile *
into table target_table
fields terminated by "str \n"
trailing nullcols
( all_columns boundfiller char (4000)
, column1 expression "list_element (:all_columns, 1)"
, date_col expression
"nvl
(to_date
(list_element (:all_columns, 2),
'yyyymmdd'),
current_date)"
, column2 expression "list_element (:all_columns, 3)"
, column3 expression "list_element (:all_columns, 4)")
begindata:
20||text1|xxx\|zzz\|aaa\|bbb)|
10|20110222|text2|ccc\|ddd\|eee\|fff)|
-- table with added column:
SCOTT@orcl_11gR2> create table target_table
2 (column1 number,
3 date_col date,
4 column2 varchar2 ( 7),
5 column3 varchar2 ( 16))
6 /
Table created.
-- same function as previous demo:
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION list_element
2 (p_string IN VARCHAR2,
3 p_element IN NUMBER)
4 RETURN VARCHAR2
5 AS
6 v_string VARCHAR2 (32767);
7 BEGIN
8 v_string := '~' || replace (replace (p_string, '|', '~'), '\~', '|') || '~';
9 RETURN SUBSTR
10 (v_string,
11 INSTR (v_string, '~', 1, p_element) + 1,
12 INSTR (v_string, '~', 1, p_element + 1)
13 - INSTR (v_string, '~', 1, p_element) - 1);
14 END list_element;
15 /
Function created.
-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
-- results with first row showing current_date instead of null value:
SCOTT@orcl_11gR2> select * from target_table
2 /
COLUMN1 DATE_COL COLUMN2 COLUMN3
---------- --------- ------- ----------------
20 23-FEB-11 text1 xxx|zzz|aaa|bbb)
10 22-FEB-11 text2 ccc|ddd|eee|fff)
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader and Escape Characteres [message #496829 is a reply to message #496805] |
Wed, 02 March 2011 11:31 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you have something like this in Informix:
CREATE PROCEDURE resume_test() RETURNING NUMBER;
indx INT;
FOR indx = 1 to 10 LOOP
IF(indx > 3 and indx < 7) THEN
CONTINUE FOR;
END IF
RETURN indx WITH RESUME;
END FOR;
END resume_test;
If you are using something like Oracle Migration Workbench, then it will duplicate the functionality, not necessarily in the most efficient way, so you will get something like this:
SCOTT@orcl_11gR2> CREATE GLOBAL TEMPORARY TABLE resume_table
2 (col00 NUMBER,
3 col01 NUMBER)
4 ON COMMIT DELETE ROWS
5 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE resume_proc
2 (p_ret_cv OUT SYS_REFCURSOR)
3 AS
4 v_resume_seq INTEGER := 0;
5 BEGIN
6 DELETE FROM resume_table;
7 FOR indx IN 1 .. 10 LOOP
8 IF indx > 3 and indx < 7 THEN
9 GOTO FOR_LABEL1;
10 END IF;
11 INSERT INTO resume_table
12 VALUES (v_resume_seq, indx);
13 v_resume_seq := v_resume_seq + 1;
14 <<FOR_LABEL1>>
15 NULL;
16 END LOOP;
17 OPEN p_ret_cv FOR
18 SELECT col01
19 FROM resume_table
20 ORDER BY col00;
21 END resume_proc;
22 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_ret_cv REFCURSOR
SCOTT@orcl_11gR2> SET AUTOPRINT ON
SCOTT@orcl_11gR2> EXECUTE resume_proc (:g_ret_cv)
PL/SQL procedure successfully completed.
COL01
----------
1
2
3
7
8
9
10
7 rows selected.
SCOTT@orcl_11gR2>
You can use a pipelined table function to do something similar, without a temporary table, like this:
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE resume_pkg
2 AS
3 TYPE v_resume_typ IS RECORD
4 (col01 NUMBER);
5 TYPE v_resume_tab IS TABLE OF v_resume_typ;
6 FUNCTION resume_func
7 RETURN v_resume_tab PIPELINED;
8 END resume_pkg;
9 /
Package created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY resume_pkg
2 AS
3 FUNCTION resume_func
4 RETURN v_resume_tab PIPELINED
5 IS
6 v_resume v_resume_tab := v_resume_tab ();
7 v_resume_seq INTEGER := 0;
8 BEGIN
9 FOR indx IN 1 .. 10 LOOP
10 IF NOT (indx > 3 and indx < 7) THEN
11 v_resume.EXTEND;
12 v_resume_seq := v_resume_seq + 1;
13 v_resume(v_resume_seq).col01 := indx;
14 END IF;
15 END LOOP;
16 FOR i IN 1 .. v_resume_seq LOOP
17 PIPE ROW (v_resume (i));
18 END LOOP;
19 RETURN;
20 END resume_func;
21 END resume_pkg;
22 /
Package body created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SELECT * FROM TABLE (resume_pkg.resume_func)
2 /
COL01
----------
1
2
3
7
8
9
10
7 rows selected.
SCOTT@orcl_11gR2>
However, frequently whatever results you are trying to achieve can be simplified to a simple select statement, like the example below, or such a thing could be used within a procedure that returns a ref cursor or within a pipelined table function.
SCOTT@orcl_11gR2> SELECT col01
2 FROM (SELECT ROWNUM col01
3 FROM DUAL
4 CONNECT BY LEVEL <= 10)
5 WHERE NOT (col01 > 3 AND col01 < 7)
6 /
COL01
----------
1
2
3
7
8
9
10
7 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 11:13:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
