CODEWITHSUNDEEP
oracle-databasesqlplus
aturegano
aturegano

Reputation: 1036

Oracle sqlplus execute sql script from command line while passing parameters to the sql script

I have one script script.sql which I want to execute from command line using oracle and passing it two parameters, as shown below

sqlplus user/pass @ script.sql my_parameter1_value my_parameter2_value

What should it be in script.sql in order to be able to run it with the parameter values?

Upvotes: 1

Views: 6124

Answers (2)

Clarius
Clarius

Reputation: 1419

I wanted to run a script that would return all orders raised during the last seven days. Here's how...

the script

SELECT * FROM orders_detail WHERE order_date BETWEEN '&1' AND '&2';
EXIT;

the command

sqlplus ot/Orcl1234@xepdb1 @"/opt/oracle/oradata/Custom Scripts/orders_between_dates.sql" $(date +%d-%b-%Y -d '-7 days') $(date +%d-%b-%Y)

Hope that helps someone. Luck.

Upvotes: 0

aturegano
aturegano

Reputation: 1036

The solution can be prepared looking at oracle blogs:

https://blogs.oracle.com/opal/sqlplus-101-substitution-variables#2_7

For the question above, the solution would be to create a script.sql like this:

DEFINE START_VALUE = &1;
DEFINE STOP_VALUE = &2;

SELECT * FROM my_table
WHERE
 value BETWEN &&START_VALUE AND &&STOP_VALUE;

Upvotes: 1

Related Questions