-1

I have the data in the following form:

10334,99.4,4241
14354,99.5,6018
16807,93.9,2279
22042,97.5,70341
22117,96.4,70335
22138,95.7,70332
23869,94.4,135649
23937,97.5,135642
23982,96.6,135638
24055,95.7,135628
24119,98.7,135624
25803,96.5,135597
25852,98.8,135592

I can shape it other way, e.g. make it separated with spaces instead of commas — this output is produced by some Linux shell pipeline, in which last is awk.

I want sqlplus to interpret each line as a separate record to put into temporary table, which I then will be using in a quite simple join within a script.

I expect sqlplus to be able to parse whatever is put into its stdin, is it possible? Or make such data accessible in some other way. There will be typically no more than 30 records. The script must create a table and fill it with such input. How to do that?

Improve this question
2
  • 1
    What have you tried so far? The post right now seems like you want others to write you a script to do your task.
    – A-Tech
    Jun 14 at 13:47
  • I don't want others to write a complete script for me, I want others to give me hints as to how to attack this problem. I tried reading manual, found nothing like I expect, however the answer down there looks promising and I'll try that approach.
    – Nikita Kipriyanov
    Jun 14 at 15:08

1 Answer 1

Reset to default
2

Short answer: sqlplus cannot do this.
It reads [Oracle] SQL statements and executes them.
As far as I know, "10334" is not [the start of] a valid SQL statement.

Two possibilities:

  1. Use SQL Loader to get the data into a real table.
    This is probably your best option, as it will validate the data and tell you when the incoming data "doesn't fit" where it's going.
    You'll probably have to pair it up with sqlplus to empty out the table you're loading.

  2. External Tables.
    A bit like using SQL Loader, but done at run time.
    You deliver the file and then Oracle uses a definition, similar to one that SQL Loader would use, to read that data dynamically and to present it as if it were a regular table.
    OK, that sounds great and, as long as your data is absolutely sound, it's probably OK, but as soon as you get any "Bad" Data in there, this all falls apart because it just ignores the "Bad" Data - no warning, no messages, just "less" data than you expected. If you're not aware of this, you can get yourself into all sorts of trouble.

Improve this answer
3
  • Thank you. I can shape this into series of valid SQL statements; as I said, the last in the chain is awk and it's not a problem to change its script from print $1,$2,$3 into print INSERT INTO table VALUES ($1,$2,$3);. Also, even if a few lines from such dataset will be missing, it's not a big problem, it's already filtered with somewhat arbitrary threshold value.
    – Nikita Kipriyanov
    Jun 14 at 15:04
  • You would still have to write those commands to a file, then call sqlplus separately to run the SQL script. You cannot pipe or chain stdin to sqlplus.
    – pmdba
    Jun 14 at 15:14
  • Errrm - I rather think you /can/. I've been doing it in scripts for years. sqlplus <<EOL [sql statements] EOL The "trick" is avoiding having to embed the password into that redirected stream (which is, of course, what Wallets are for).
    – Phill W.
    Jun 15 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.