Solved

When clause in sqlloader

Posted on 2000-04-11
14
1,422 Views
Last Modified: 2010-08-05
I am using the when clause with multiple AND conditions. I need to use an OR condition to check if either of two fields are not blank and load data based on it.

eg. WHEN ITEM != '  ' OR SUB_ITEM != '  '

The sqlloader doesn't like the OR condition and I get an error, is the OR condition supported ?

Is there some other work around to this problem.
0
Comment
Question by:pcorreya
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 2

Expert Comment

by:AndrewRodionov
Comment Utility
Hi pcorreya,

As far as I know you can use AND-conditions only. The OR-condition does not supported.
If your task allow to load data by several chunks you can create two .ctl-files: one with WHEN ITEM != ' ' and another with WHEN SUB_ITEM != ' '. Then load you data sequentially.

Andrew.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Two other possibilities to consider for loading data when it requires manipulation that SQL*Loader does not support:

1. Create a work table with the same structure as the input data file with all varchar2 columns.  Load the data into that with SQL*Loader then use a PL/SQL process to manipulate and/or filter the data and insert into the real table.

2. Use a PL\SQL process with utl_file to read the ASCII file directly, filter or manipulate the data as required, then insert it into your table.

Both of these approaches require more programming and are slower than SQL*Loader by itself, but can handle data problems that SQL*Loader cannot.
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility
pcorreya,

  From boolean (logics) algerba:

  ( A <> x OR B <> y ) is equivalent to
NOT ( A=x AND B=y).

  Use this instead of OR clause

regards,

  rwarsh
0
 
LVL 2

Expert Comment

by:AndrewRodionov
Comment Utility
rwarsh:
Did you try to use Boolean algebra in SQL*Loader? For exemple, the NOT keyword?

pcorreya:
Look at markgeer's comment. His first method is rather flexible.

Andrew
0
 

Expert Comment

by:s_krasovsky
Comment Utility
Other suggestion could be to create check constraint,like this
(ITEM is not null or SUB_ITEM is not null)
on real table,if it is possible, or work table, so it allow you to load data for one step.Agree with AndrewRodionov that you could not resolve this task using boolean algerba in when clause.
0
 

Expert Comment

by:s_krasovsky
Comment Utility
By the way,you may try to use ENFORCE option  ALTER TABLE ... CONSTRAINT command in Oracle8,so you may load data
in a work table directly.
0
 

Expert Comment

by:s_krasovsky
Comment Utility
Sorry,I have made mistake - "in a real table directly".
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:s_krasovsky
Comment Utility
Sorry,I have made mistake - "in a real table directly".
0
 
LVL 4

Accepted Solution

by:
jtrifts earned 50 total points
Comment Utility
I agree with Andrew that OR is ot supported.

I have always found SQL*LOADER to be too inflexible for real-world use when loading external data.

Have you considered using UTL_FILE (server-based) or TEXT_IO (client/forms based) packages for loading?

If you use these tools, you can use PL/SQL to handle the various cases and take advantage of other neat Oracle capabilities such as pl/sql tables, varrays, etc.

Let us know if you would like to consider this option.  I'm sure we can provide you with some sample code...

Regards,

JTrifts
0
 
LVL 1

Author Comment

by:pcorreya
Comment Utility
I have used sqlloader to import the data into a temporary table and then process it using pl/sql. However, I would like to see some sample code.
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
PCORREYA: The following is a sample procedure that uses UTL_FILE to load the data into a temp table, but also does some pre-processing, such as giving id's to each record (to maintain the sequential characteristic of the original data file) and giving a group_id to maintain the logical grouping of the data file.  Please note that I had to make some changes to the code so nobody would see exactly what the real data I am handling is (i.e. for confidentiality).

You could do more or less pre-processing, depending on your needs.  I prefer to put stuff into a temp table in the first step, with a bit of pre-processing to provide some ids, indexes, and frequently used substrings, and then REALLLLLLY process the data in a second step by another procedure.

But here's the code:

PROCEDURE LOAD_DATA AS

   v_action            CHAR(1);
   v_commit_counter        INTEGER := 0;
   v_group_id          NUMBER(10);
   v_err_count                 NUMBER  := 0;
   v_err_flag          CHAR(1);
   v_err_msg           VARCHAR2(400);
   v_err_type          VARCHAR2(20);
   v_filename          VARCHAR2(200) := 'YourFileName.dat' ;
   v_file_handle       UTL_FILE.FILE_TYPE;
   v_id                             NUMBER(10);
   v_location          VARCHAR2(200) := 'e:\YourLocationOnTheServerWhereYourDataFileIsStored';
   v_max_linesize           BINARY_INTEGER := 1000; -- not strictly necessary as this
                                               -- is for the overloaded version of UTL_FILE
   v_newline           VARCHAR2(1000);
   v_version           NUMBER(5);
   v_status            CHAR(1);
   v_type              VARCHAR2(3);
   v_value_1           CHAR(1);
   v_raw_data          VARCHAR2(1000); -- the whole unprocessed record from the data file

BEGIN
   -- define the file handle
   v_file_handle := UTL_FILE.FOPEN(v_location,v_filename,'r',v_max_linesize);

   -- For every record/line in the data extract...
   LOOP
      BEGIN
         --read a line into v_newline
         UTL_FILE.GET_LINE(v_file_handle, v_newline);

             -- set val1
             v_value_1 := SUBSTR(v_newline,1,1);

       -- set action based on val1
             IF v_value_1 IN ('T','L','R','U') THEN     -- Top Ind,L-detail, D-detail, Undo
                v_action := NULL;
             ELSIF v_value_1 = 'M' THEN               -- Memo
                  v_action := 'A';
             ELSE
                v_action := SUBSTR(v_newline,2,1); -- All others
             END IF;

             IF v_value_1 <> 'T' THEN
                v_version := TO_NUMBER(SUBSTR(v_newline,4,4));
             ELSE
                v_version := NULL;
             END IF;

             IF v_value_1 = 'V' and v_action = 'N' THEN
                v_type := SUBSTR(v_newline,102,2);
             END IF;

             -- For First record of record group, advance seq
             IF v_value_1 = 'Z' THEN
          -- in this example Z marks the first record of a collection of related records
          -- thus I will advance the sequence when I get a Z (i.e. nextval),
          -- but use currval for subsequent records

                --Convert the status code to the corresponding **** status code
              v_status := SUBSTR(v_newline,33,1);

                -- Write the data to the temporary table
                  INSERT INTO tmp_table (id
                                                         ,group_id
                                                         ,val1
                                                         ,status
                                                         ,type
                                                         ,action
                                                         ,version
                                                         ,err_flag
                                                         ,err_type
                                                         ,raw_data)
                                         VALUES (tmp_id_seq.nextval
                                                         ,group_id_seq.nextval
                                                         ,v_value_1
                                                         ,v_status
                                                         ,v_type
                                                         ,v_action
                                                         ,v_version
                                                         ,v_err_flag
                                                         ,v_err_type
                                                         ,v_newline); -- the whole raw data

         ELSE  -- use group_id_seq.currval,
               -- but the record id, being the PK and unique, still uses nextval

                INSERT INTO tmp_table (id
                                                         ,group_id
                                                         ,val1
                                                         ,status
                                                         ,type
                                                         ,action
                                                         ,version
                                                         ,err_flag
                                                         ,err_type
                                                         ,raw_data)
                                         VALUES (tmp_id_seq.nextval
                                                         ,group_id_seq.currval
                                                         ,v_value_1
                                                         ,v_status
                                                         ,v_type
                                                         ,v_action
                                                         ,v_version
                                                         ,v_err_flag
                                                         ,v_err_type
                                                         ,v_newline);
         END IF;


         v_err_count := v_err_count + 1;

             -- Issue commit for every one hundred records inserted
             v_commit_counter := v_commit_counter + 1;
             IF v_commit_counter = 100 THEN
                v_commit_counter := 0;
                  COMMIT;
             END IF;

      EXCEPTION
         -- this exception will be raised when you get to the end of the data file
         WHEN NO_DATA_FOUND THEN
            EXIT;
      END;
   END LOOP;

   COMMIT;

   UTL_FILE.FCLOSE(v_file_handle);

EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('INVALID PATH');
      UTL_FILE.FCLOSE(v_file_handle);
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('INVALID MODE');
      UTL_FILE.FCLOSE(v_file_handle);
   WHEN UTL_FILE.INVALID_OPERATION THEN
      DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION');
        UTL_FILE.FCLOSE(v_file_handle);
   WHEN UTL_FILE.INTERNAL_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('INTERNAL_ERROR');
      UTL_FILE.FCLOSE(v_file_handle);
   WHEN OTHERS THEN
      v_err_msg := SUBSTR(SQLERRM, 1, 400);
      DBMS_OUTPUT.PUT_LINE('ERROR FOR RECORD ' || TO_CHAR(v_err_count));
      DBMS_OUTPUT.PUT_LINE(v_err_msg);
      UTL_FILE.FCLOSE(v_file_handle);

END LOAD_DATA;


0
 
LVL 1

Author Comment

by:pcorreya
Comment Utility
Thanks for your code, I would also like to know if it is possible to send an email to a designated person when the job has run.

I shall raise this as a new question
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
Absolutely.
I would simply add a statement or procedure outside the loop, perhaps directly after the FCLOSE statement.
This would use the HOST command...example follows:

PROCEDURE Mail_Warning( send_to VARCHAR2) IS
  the_username VARCHAR2(40);
  the_password VARCHAR2(40);
  the_connect  VARCHAR2(40);
  the_command  VARCHAR2(2000);
BEGIN
  /*
  ** Get Username, Password, Connect information
  */
  Get_Connect_Info(the_username,the_password,the_connect);
  /*
  ** Concatenate together the static text and values of
  ** local variables to prepare the operating system command
  ** string.
  */
  the_command := 'orasend '||
      ' to='||send_to||
      ' std_warn.txt '||
      ' subject="## LATE PAYMENT ##"'||
      ' user='||the_username||
      ' password='||the_password||
      ' connect='||the_connect;
 
  Message('Sending Message...', NO_ACKNOWLEDGE);
  Synchronize;
  /*
  ** Execute the command string as an O/S command The
  ** NO_SCREEN option tells forms not to clear the screen
  ** while we do our work at the O/S level "silently".
  */
  Host( the_command, NO_SCREEN );
  /*
  ** Check whether the command succeeded or not
  */
  IF NOT Form_Success THEN
    Message('Error -- Message not sent.');
  ELSE
    Message('Message Sent.');
  END IF;
END;
0
 
LVL 1

Author Comment

by:pcorreya
Comment Utility
what is std_warn.txt ? does this contain the text of the message. If so where do I place this file.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now