How to execute SQL from Oracle SQL Plus passing variable name SQL file from Unix

Question

For Oracle DB and HP-UX : I have a variable name SQL file like: my_sql_file_${TIMESTAMP}.sql

I assign this file to a variable: MYSQLFILE="my_sql_file_${TIMESTAMP}.sql"

So, I am trying to execute the MYSQLFILE inside SQL Plus, like:

@${MYSQLFILE}

but this appears that it cannot be done. Is there any way to achieve the above??

Thank you.

Can you edit your question to show how you're calling SQL*Plus and using the variable in context? Changing sensitive data of course (username/password/etc.) but otherwise leaving it exactly as you run it? — Alex Poole
– Alex Poole, Commented Jan 22, 2019 at 16:20

vc 74 · Accepted Answer · 2019-01-22 14:50:36Z

2

This should work:

MYSQLFILE="my_sql_file_${TIMESTAMP}.sql"

sqlplus -S user/password@db << EOF
@$MYSQLFILE
exit;
EOF

You can also pass user, password and db as parameters to your script

answered Jan 22, 2019 at 14:50

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

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

9 Comments

Kostas75 Over a year ago

Unfortunately, I have already tried that and does not work... :( I get response: SP2-3010: unable to open file "$MYSQLFILE".

Alex Poole Over a year ago

@Kostas75 - are you doing it exactly like this, or are you perhaps quoting the heredoc marker - i.e. << "EOF" ?

Kostas75 Over a year ago

I am doing exactly like this (with the EOF) and does not work, tried several times. Only the 1 line command works (with no EOF).

vc 74 Over a year ago

@Kostas75 Which shell are you using?

Kostas75 Over a year ago

@vc 74: #!/bin/sh (inside shell script) at HP-UX.

|

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-22 14:53:05Z

2

you can use

sqlplus user/pass@service_name @$MYSQLFILE

inside SQLPLUS, you can also use

sql> host echo $MYSQLFILE

And this is also valid,

sqlplus user/password@db <<EOF
  @$MYSQLFILE
  exit;
EOF

answered Jan 22, 2019 at 14:53

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

6 Comments

Kostas75 Over a year ago

The 1-line command works fine: sqlplus user/pass@service_name @$MYSQLFILE. The EOF is exactly what I have tried to do when I opened this questions here and does NOT work, at least not at my environment.

Derviş Kayımbaşıoğlu Over a year ago

Glad that you solved your problem. If you believe that this answer is useful for you please consider to upvote and/or mark it as answer so that community can also benefit from it :) thank you

Kostas75 Over a year ago

Problem solved with different approach, question was not answered exactly, but OK.

Derviş Kayımbaşıoğlu Over a year ago

If there is another approach can you please share your practice with us? besides I am not meaning check my post as an answer, but if you think any of them relevant to your problem you should mark it as answer. This is how community works and This is how we share out know how

Kostas75 Over a year ago

OK, basically I used the 1-line sqlplus call, but also had to include the sets, commit and quit at the SQL file, because everything should be done from within the SQL file, since I could not use the EOF (it does not work at my system).

|

Collectives™ on Stack Overflow

How to execute SQL from Oracle SQL Plus passing variable name SQL file from Unix

2 Answers 2

9 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related