How attach the SQL script by SQLPlus to the bash loop?

Question

I want to clear the table from all data (Table_with_DB (it is in program sqldeveloper)) through sqlplus and based on filenames in the path (path/to/files) using SQLPlus completes the table (Table_with_DB) the filenames that are in the path.

I create 2 separate SQL files (clear.sql ; register.sql)

and 1 with bash (bashloop.sh)

clear.sql

BEGIN 
 package.clear('Table_with_DB'); 
END;

register.sql

BEGIN 
 package.register('folderName' , '&1);
END;

bashloop.sh

for i in path/to/files;
 do
  sqlplus -s userid/password@ @clear.sql
  sqlplus -s userid/password@ @register.sql
 done

I expect the query clear Table_with_DB and transfer the filenames from path/to/files to Table_with_DB using SQLPlus

but the actual it not work :(

Dmitry Demin · Accepted Answer · 2019-10-22 11:08:14Z

Example sqlplus in loop .

#!/bin/bash

username=system
password=passwordsystem
tns_alias=esmd


for i in /opt/oracle/example1/test*/file*.txt;
 do
$ORACLE_HOME/bin/sqlplus   $username/$password@$tns_alias <<EOF
SET SERVEROUTPUT ON SIZE 2000
    BEGIN
      dbms_output.put_line('$i');
    END;
/
EOF

done;

Example output

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> SQL>   2    3    4  /opt/oracle/example1/test2/file2.txt

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 22 13:59:24 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> SQL>   2    3    4  /opt/oracle/example1/test3/file3.txt

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 22 13:59:24 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> SQL>   2    3    4  /opt/oracle/example1/test4/file4.txt

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
oracle@esmd:~/example1>

Danny Schoemann · Accepted Answer · 2019-10-22 09:15:15Z

0

If I understand what you want to do then in your bashloop.sh you want to replace db_name with $i - or ${i} if you have filenames with spaces.

answered Oct 22, 2019 at 9:15

Danny Schoemann

1,36027 silver badges45 bronze badges

1 Comment

buka korek Over a year ago

I change this db_name. I want clear all data in table Table_with_DB using clear.sql and send new data (which are filenames) from path/to/files using register.sql

Collectives™ on Stack Overflow

How attach the SQL script by SQLPlus to the bash loop?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related