Home » SQL & PL/SQL » Client Tools » Help with a check in SQL Plus (Oracle Enterprise, 10g, Linux)
Help with a check in SQL Plus [message #471397] Fri, 13 August 2010 19:16 Go to next message
Tr0cken
Messages: 22
Registered: August 2010
Junior Member
Hi everybody!
I have a Bash script that counts the rows of a csv file, extracts the fields and makes inserts in a sql file. Then it logs into SqlPlus and calls the insert file.
The sql file looks like this:
WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3) 
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3) 
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3) 
VALUES (VALUE1, VALUE2, VALUE3);
...

-- Here it makes an insert in TABLE2, summing up the inserts
-- made in TABLE1
INSERT INTO SCHEMA.TABLE2 (NUMBER_OF_ROWS, DATA1, DATA2)
VALUES (CSV_ROWS_NUMBER, VALUE1, VALUE2);

-- And then commits
COMMIT;


I rely on "WHENEVER SQLERROR EXIT" for things to go the right path. However sometimes because of the contents of the CVS files (which I can't control) some rows don't get inserted but SqlPlus doesn't see that as an error, doesn't exit and I end up with the wrong number of rows being informed in the second insert.
So finally here's the question:
Is there some kind of "if-then-else" construct in Sql?
After all the inserts are made, do a "select count (*)" and compare that number to the one informed by the script. If they match, make the final insert and commit; else exit.

Can anyone help? Any ideas are very welcome.

Thanks in advance!
Re: Help with a check in SQL Plus [message #471398 is a reply to message #471397] Fri, 13 August 2010 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
>Is there some kind of "if-then-else" construct in Sql?
Not really from a logic or flow control perspective.

If the CSV file were treated as an EXTERNAL TABLE,
then it could be accessed with the full power of a custom PL/SQL procedure.

Re: Help with a check in SQL Plus [message #471412 is a reply to message #471397] Sat, 14 August 2010 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
some rows don't get inserted but SqlPlus doesn't see that as an error

Post an example of this.

Regards
Michel
Re: Help with a check in SQL Plus [message #471415 is a reply to message #471412] Sat, 14 August 2010 02:07 Go to previous messageGo to next message
Tr0cken
Messages: 22
Registered: August 2010
Junior Member
Michel Cadot wrote on Sat, 14 August 2010 01:24
Quote:
some rows don't get inserted but SqlPlus doesn't see that as an error

Post an example of this.

Regards
Michel


Well, one time a csv came encoded in Little endian. The bash script processed it and made the sql file. Thing is every value was sorrounded by ^@. So the sql file looked like this:
WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3) 
VALUES (^@VALUE1^@, ^@VALUE2^@, ^@VALUE3^@);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, ^FIELD3) 
VALUES (^@VALUE1^@, ^@VALUE2^@, ^@VALUE3^@);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3) 
VALUES (^@VALUE1^@, ^@VALUE2^@, ^@VALUE3^@);
...

INSERT INTO SCHEMA.TABLE2 (NUMBER_OF_ROWS, DATA1, DATA2)
VALUES (CSV_ROWS_NUMBER, VALUE1, VALUE2);

COMMIT;


It didn't insert any row and didn't throw any error. Of course the last insert got inserted and stored a number of rows that didn't exist!

So what I'm looking for is a way to compare the actual number of rows that got inserted with the number of rows in the csv file and commit if they match. Any ideas?

Re: Help with a check in SQL Plus [message #471418 is a reply to message #471415] Sat, 14 August 2010 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you have another error as for me it works well:
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Sam. Ao¹t 14 09:28:40 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> @c
Connected.
SQL> drop table table1;

Table dropped.

SQL> create table table1 (field1 varchar2(100), field2 varchar2(100), field3 varchar2(100));

Table created.

SQL> WHENEVER SQLERROR EXIT
SQL> INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3)
  2  VALUES (^@VALUE1^@, ^@VALUE2^@, ^@VALUE3^@);
VALUES (^@VALUE1^@, ^@VALUE2^@, ^@VALUE3^@)
        *
ERROR at line 2:
ORA-00936: missing expression


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

C:\>

You should investigate to fix your script BEFORE executing it instead of checking the number of rows afterwards.

Regards
Michel

[Updated on: Sat, 14 August 2010 02:32]

Report message to a moderator

Re: Help with a check in SQL Plus [message #471430 is a reply to message #471415] Sat, 14 August 2010 08:26 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
I agree with BlackSwan. Create an external table based on the csv and use PL/SQL to process it. The decision to commit or not can be made by the PL/SQL code.
Use the right tool for the right job. Shell scripting is nice, but not for everything. This seems to be one thing where other tools outperform shell scripting.
Re: Help with a check in SQL Plus [message #471471 is a reply to message #471430] Sat, 14 August 2010 18:46 Go to previous messageGo to next message
Tr0cken
Messages: 22
Registered: August 2010
Junior Member
Frank wrote on Sat, 14 August 2010 08:26
I agree with BlackSwan. Create an external table based on the csv and use PL/SQL to process it. The decision to commit or not can be made by the PL/SQL code.
Use the right tool for the right job. Shell scripting is nice, but not for everything. This seems to be one thing where other tools outperform shell scripting.


I receive 100+ csv files per day and the script automates everything. Is there a way to do the same using external tables?
Re: Help with a check in SQL Plus [message #471472 is a reply to message #471418] Sat, 14 August 2010 18:49 Go to previous messageGo to next message
Tr0cken
Messages: 22
Registered: August 2010
Junior Member
Michel Cadot wrote on Sat, 14 August 2010 02:31
You should investigate to fix your script BEFORE executing it instead of checking the number of rows afterwards.

Regards
Michel


What do you mean?
Re: Help with a check in SQL Plus [message #471479 is a reply to message #471472] Sun, 15 August 2010 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean you know the problem is in the "^@" so add a "sed" to your shell script to convert them to "'" before executing it.

But as it has been said, it you be far more efficient to use an external table to load the csv file, you can even then load in parallel which is far more faster than a bunch of insert statements (even if it is not in parallel).
External is an ETL (Extract, Transform and Load) tool in one statement.

Regards
Michel
Re: Help with a check in SQL Plus [message #471486 is a reply to message #471471] Sun, 15 August 2010 03:29 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Tr0cken wrote on Sun, 15 August 2010 01:46
I receive 100+ csv files per day and the script automates everything. Is there a way to do the same using external tables?

Use the right tool for the right (part of the) job: move the files along using shell scripting, use the database to handle the data.
Since you code the data-handling in PL/SQL, you should be able to handle "strange" characters as well.
So, my idea would be to use a shell script to move the file, call sqlplus to run a PL/SQL-procedure which imports the data and finally use the same shell script to move the file again.
Previous Topic: splitted queries
Next Topic: unable to login through TOAD
Goto Forum:
  


Current Time: Sat Aug 02 03:38:11 CDT 2014

Total time taken to generate the page: 0.08395 seconds