Multiple Line Variable into SQLPlus from Shell Script

Question

What is the best way to pass multiple values from one variable into separate records in an oracle db?

I want to take the output from:

hddlist=`iostat -Dl|awk '{print ""$1"="$(NF)}'

This returns output like this:

hdisk36=0.8
hdisk37=0.8
hdisk38=0.8
hdisk40=5.5   
hdisk52=4.9

I want to insert them into a database like so:

sqlplus -s /nolog <<EOF1
connect / as sysdba
set verify off
insert into my_table ##Single Record Here
EOF1

How can I systematically separate out the values so i can create individual records that look like this:

 Disk         Value
---------    -------
hdisk36       0.8
hdisk37       0.8
hdisk38       0.8
hdisk40       5.5   
hdisk52       4.9

I originally tried a while loop with a counter but could not seem to get it to work. An exact solution would be nice but some directional advice would be just as helpful.

sorpigal · Accepted Answer · 2012-06-25 17:03:21Z

3

Loop and generate insert statements.

sql=$(iostat -Dl | awk '{print ""$1"="$(NF)}' | while IFS== read -r k v ; do
    printf 'insert into mytable (k, v) values (%s, %s);\n' "$k" "$v"
done)

This output can be passed in some manner to sqlplus, perhaps like this

sqlplus -s /nolog <<EOF1
connect / as sysdba
set verify off
$sql
EOF1

Although, depending on the line format of iostat, it might be simpler to just omit awk and parse with read directly.

answered Jun 25, 2012 at 17:03

sorpigal

26.3k8 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A.B.Cade · Accepted Answer · 2012-06-25 17:02:55Z

2

You can redirect the output to a file and then use an external table

It should look something like this:

CREATE TABLE hddlist_ext_table (
  disk CHAR(16),
  value CHAR(3)
  ORGANIZATION EXTERNAL (
                         TYPE ORACLE_LOADER DEFAULT DIRECTORY tab_dir
                         ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
                                            FIELDS TERMINATED BY '=')
                         LOCATION ('your_file_name'));

Then you can either use this table for your data or insert-select from it to your table; insert into my_table

select disk, value from hddlist_ext_table;

answered Jun 25, 2012 at 17:02

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Comments

Trevor North · Accepted Answer · 2012-06-25 16:32:48Z

You can insert multiple rows in a single SQL statement in Oracle like this

 INSERT ALL
    INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
    INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
    INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
  SELECT * FROM dual;

If you intend to run this script automatically at intervals to then see the results of each disk, you will probably need additional columns to hold the date and time.

You might also look at sqlldr as you can specify a control file telling it what your data contains and then this will load the data into a table. It is more suited to the purpose if you are loading lots of data than SQL Plus.

Collectives™ on Stack Overflow

Multiple Line Variable into SQLPlus from Shell Script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related