i have an input file: $home/dir/subdir/input.txt

content of input.txt:

123,0000,11111,3,1,X
124,0001,11112,3,1,Y
125,0002,,4,2,Y
129,0003,11114,4,2,X

how do i use the input file to insert them into an sql table using unix scripting/plsql?

CodePudding user response：

using unix scripting/plsql?

Oracle offers several ways to do that.

One of them is to use SQL Loader. Its benefit is that you can do everything on your own PC (i.e. the source file can reside on your own computer). It runs really, really fast.

Another one is the external tables feature. It lets you access the source file as if it were an ordinary Oracle database table, i.e. you can write SELECT statements that fetch data from the file and simply insert those rows into the target table. Its drawback is that you have to create a directory, Oracle object that points to a file system directory which is usually located on the database server so - if you aren't a DBA - you'll have to talk to one to create the directory and let you access it (i.e. grant required privileges).

Yet another option - as you stated, PL/SQL, is to use the UTL_FILE package. You'd create a PL/SQL script which opens the file, reads it line-by-line and inserts rows into the target table. Similarly to previous option, there must be a directory you have access to.

As of Unix scripting, I'm not sure what you meant to say by that. Unix is operating system, it doesn't insert rows into a database. If you meant to say that you'd create a script which would then call another - database - script which would actually do the job, that's possible (e.g. you'd call sqlldr executable).

Therefore, it just depends on what you have access to, whether you can (or can not) get access to database server's directory. I'm not going to write any code (would take too much time to cover all those options), but I could assist if you choose which way to go (apart from the Unix part; I don't use it).

CodePudding user response：

Using sed to insert the header and csvlook from csvkit to format the data for Markdown rendering

$ sed '1i col1,col2,col3,col4,col5,col6' input.txt | csvlook
| col1 | col2 |   col3 | col4 | col5 | col6 |
| ---- | ---- | ------ | ---- | ---- | ---- |
|  123 |    0 | 11,111 |    3 |    1 | X    |
|  124 |    1 | 11,112 |    3 |    1 | Y    |
|  125 |    2 |        |    4 |    2 | Y    |
|  129 |    3 | 11,114 |    4 |    2 | X    |

Or better yet