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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #495857 is a reply to message #495856] Tue, 22 February 2011 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
I am IMPRESSED!
Re: SQL Loader and Escape Characteres [message #495927 is a reply to message #495856] Wed, 23 February 2011 05:20 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Hi,

First of all, thakns for your time and for your quick answer.

Yes, it is my first post in here. I used to use DBForums instead, and only yesterday found this forum.
I think, i'll become an regular customer in here. Smile

I will try your solution, and post in here the result.

Thanks!
Re: SQL Loader and Escape Characteres [message #495928 is a reply to message #495857] Wed, 23 February 2011 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Wed, 23 February 2011 03:14
I am IMPRESSED!

Impressed? FLABBERGASTED! /forum/fa/5263/0/

Regards
Michel

[Updated on: Wed, 23 February 2011 05:32]

Report message to a moderator

Re: SQL Loader and Escape Characteres [message #495931 is a reply to message #495927] Wed, 23 February 2011 05:46 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Is there any other simpliest way?

I am not managing to use your solution ...
Re: SQL Loader and Escape Characteres [message #495935 is a reply to message #495931] Wed, 23 February 2011 06:01 Go to previous messageGo to next message
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 #495941 is a reply to message #495935] Wed, 23 February 2011 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 21624
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should post everything you did, EXACTLY as you did it. Basically, it should look like Barbara's message. It is impossible to debug code you can't see (and we can't see yours).
Re: SQL Loader and Escape Characteres [message #495947 is a reply to message #495941] Wed, 23 February 2011 07:51 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Hi again,

After all, the mistake was mine.
I used Barbaras code, excepting the "srn \n" parameter at Infile line, and it worked.

I have now two new questions:
1- With this model can I use custom formatting for each field?
For instance, a column1 like Current_date if it is null.

2- With CLOBs this might not work, right?

Thanks!
Re: SQL Loader and Escape Characteres [message #495987 is a reply to message #495947] Wed, 23 February 2011 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #495990 is a reply to message #495947] Wed, 23 February 2011 11:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
aucrun wrote on Wed, 23 February 2011 05:51

2- With CLOBs this might not work, right?


Clobs are loaded a little differently. The following section of the SQL*Loader section of the Utilities Guide in the online documentation describes some of the ways that clobs can be loaded:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/ldr_loading.htm#i1007590

Please provide some sample data that includes your clob, a create statement for the table that you wish to load into, and the results that you want.

Re: SQL Loader and Escape Characteres [message #496763 is a reply to message #495990] Wed, 02 March 2011 06:28 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
I just wanna say ... it works just fine.

After some noobism from me, I've managed to put it in order and it works on the fly ... it makes the load of data a little slower, but it works just as I want it to work.

Thanks a lot.

Now, I have another question if I may ...
Is there any way to create an Function in Oracle that returns data but without closing it self.

An example:
I want a function that has an cursor that foreach record found within the cursos, returns some value, but continues with the next record found.
Just like "RETURN WITH RESUME" from Informix!

Thanx
Re: SQL Loader and Escape Characteres [message #496782 is a reply to message #496763] Wed, 02 March 2011 07:06 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read up on ref cursors.
Re: SQL Loader and Escape Characteres [message #496790 is a reply to message #496782] Wed, 02 March 2011 07:43 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
You mean, using one Ref Cursor being returned by the function with an temporary table to assist it, right?

ORacle does not have anything like RETURN WITH RESUME, right?
Re: SQL Loader and Escape Characteres [message #496791 is a reply to message #496790] Wed, 02 March 2011 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
No need for a temporary table generally speaking.

I'm not exactly sure what return with resume does - so you'll have to explain.
Re: SQL Loader and Escape Characteres [message #496796 is a reply to message #496791] Wed, 02 March 2011 08:01 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
RETURN WITH RESUME is the act of returning some kind of result from within an Stored Function in Informix, without actually exit the function.
Which means that after you return that result, the function continues on and on until you declare to EXIT.

In Oracle when you RETURN any result from any function, the function closes, right? I want to not exit it after returning the result.

But it has some logic that you use an Cursor, or custom Type (VARRAY for instance) to return those values ...
Re: SQL Loader and Escape Characteres [message #496798 is a reply to message #496796] Wed, 02 March 2011 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you actually need this? Surely you can write the function in such a way as to do everything that needs doing before returning data.
A pipelined table function may do what you need.
Re: SQL Loader and Escape Characteres [message #496802 is a reply to message #496798] Wed, 02 March 2011 08:26 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Pipelined Table Function?? What is it? Could you explain it a little more, please?
Re: SQL Loader and Escape Characteres [message #496803 is a reply to message #496802] Wed, 02 March 2011 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
pipelined table functions
Re: SQL Loader and Escape Characteres [message #496805 is a reply to message #496803] Wed, 02 March 2011 08:44 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Thanks ... it can be one another solution, indeed.

I'll investigate it better and look into it to see if it solves my problem.

Thanks again!
Re: SQL Loader and Escape Characteres [message #496829 is a reply to message #496805] Wed, 02 March 2011 11:31 Go to previous message
Barbara Boehmer
Messages: 8922
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>


Previous Topic: Import Utility - Default Path Of Import Log File
Next Topic: IMP-00037: Character set marker unknown (2 threads merged by bb)
Goto Forum:
  


Current Time: Thu Dec 03 23:22:36 CST 2020