Escape characters for SQLLoader

The LINC/EAE migration tool can automatically generate reports that can be used to extract your data from DMSII to your target database, for example Oracle.
In this scenarios the Oracle SQL Loader tool is used. However you might problems loading the data because the string values can contain the same characters you are using to enclose them.

Let’s see an example, taken from an oracle forum:

C:\ora>type buyer.ctl
LOAD DATA
INFILE 'buyer.data'
truncate into table BUYER
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
buyer_code,
BUYER_NAME
)

And suppose you have data like:

1,"XYZ IND"

2,"ABC"

3,"XYZ ABC"

4,"Your "offspring""

5,"ATUL"

How can you “escape” the enclosing characters. Well I found the answer in another forum:

If two delimiter characters are encountered next to each other, a single occurrence of the delimiter character is used in the data value. For example, 'DON''T' is stored as DON'T. However, if the field consists of just two delimiter characters, its value is null.

So just use something like:

1,"XYZ IND"

2,"ABC"

3,"XYZ ABC"

4,"Your ""offspring"""

5,"ATUL"

Easy way to see the Explain Plan in Oracle

Linc\EAE used profiles for their queries. Well the profile information is used by our migration tool to generate indexes.
In Java is easy to intercept all SQL statements used by the translated application and analyze them.

To analyse how a query is executed you have to study its explain plan. For go here an excellent guide on EXPLAIN PLAN.

After you read that page, you will find useful the following function, that will shorten the lines that you have to type to see the explain plan:

create OR REPLACE function  ShowPlan return sys_refcursor
  as
      c_test sys_refcursor;
BEGIN
  open c_test for select 
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", 
  object_name "Object"
  from 
  sys.plan_table$ start with id = 0 connect by prior id=parent_id;
  return c_test;
END;
SQL>

explain plan for select * from MY_TABLE

SQL> variable rc refcursor
SQL> exec :rc := testfunc()

PL/SQL procedure successfully completed.

SQL> print rc

Operation                      Object
------------------------------ ------------------------------
SELECT STATEMENT ()
 TABLE ACCESS (FULL)           MY_TABLE

Get Table Owner in Oracle

When we migrate from LINC/EAE to Oracle, the migration tool generates an schema an tables form the original ISPECS.
I came across with the problem that I had been playing around with a test database and I didn’t know who was the owner of the table.

Well just as a reminder this is what is needed:

select owner, table_name, tablespace_name   from dba_tables   where table_name='YOUR_TABLE';

This will return something as:

OWNER                    TABLE_NAME  TABLESPACE_NAME
------------------------------ ------------------------ ------------------------------
THE_OWNER               MY_TABLE       USERS