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

Question

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?

aturegano · Accepted Answer · 2020-06-19 10:49:17Z

1

The solution can be prepared looking at oracle blogs:

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;

answered Jun 19, 2020 at 9:37

aturegano

1,0701 gold badge18 silver badges35 bronze badges

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

2 Comments

"... looking at oracle blocks." What are these blocks? I'd like to look at them too.

@Littlefoot I wanted to refer to oracle blogs. I already fixed it.

Clarius · Accepted Answer · 2022-11-13 09:17:52Z

0

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.

answered Nov 13, 2022 at 9:17

Clarius

1,47918 silver badges13 bronze badges