[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1479
  • Last Modified:

When clause in sqlloader

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
pcorreya
Asked:
pcorreya
  • 4
  • 3
  • 3
  • +3
1 Solution
 
Andrei RodionovCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Ron WarshawskyCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Andrei RodionovCommented:
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
 
s_krasovskyCommented:
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
 
s_krasovskyCommented:
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
 
s_krasovskyCommented:
Sorry,I have made mistake - "in a real table directly".
0
 
s_krasovskyCommented:
Sorry,I have made mistake - "in a real table directly".
0
 
jtriftsCommented:
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
 
pcorreyaAuthor Commented:
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
 
jtriftsCommented:
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
 
pcorreyaAuthor Commented:
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
 
jtriftsCommented:
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
 
pcorreyaAuthor Commented:
what is std_warn.txt ? does this contain the text of the message. If so where do I place this file.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now