Menu
How do I pass arguments to a PL/SQL script on command line with SQLPLUS? I can call my PL/SQL script like so, but the script requires arguments in order for it to succeed. How can I run sqlplus.exe so that I can pass arguments to the script?
@ECHO off
// where HOST030 is a tnsnames alias to a machine, port, and instance
sqlplus.exe MYUSER/mypassword@HOST030 < refreshDataOnOracle.sql
pause
I tried to search for the answer but couldn't find an "argument example" anywhere for SQLPLUS. I suspect it would be a similar method of using the SQL*Plus "START" command?
828
You cannot call SQL*Plus commands (which run only on the client) from PL/SQL (which runs only on the server).
In PL/SQL, we can pass parameters to procedures and functions in three ways. 1) IN type parameter: These types of parameters are used to send values to stored procedures. 2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
Answer: To execute a script file in SQLPlus, type @ and then the file name. The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.) This command would run a script file called script.
How do I assign a value to a variable using a select statement and use it in a SQL*Plus script, something like this? VARIABLE FullCatCode VARCHAR2(7) exec :FullCatCode := (SELECT CatCode from draw_catcodes where series = 123 and base = 158); SELECT :FullCatCode || '-' || Other stuff... from table_name...
The parameters that are being passed from the command line are available in SQLPLUS as &1 and &2. To prevent problems with date formatting you may want to consider changing that to select * from table where create_date between to_date ('&1','DD-MM-YYYY') and to_date ('&2','DD-MM-YYYY');
SQL Command Line (SQL*Plus) is a command-line tool for accessing Oracle Database XE. It enables you to enter and run SQL, PL/SQL, and SQL*Plus commands and statements to: Query, insert, and update data. Execute PL/SQL procedures. Examine table and object definitions.
Dynamically passing parameters to SQL Script during run time, over a command prompt using command line utility SQLCMD or using PowerShell is described in this article. Let's take an example of the below SQL query. The following SQL to be executed on Remote Server Looking at the above SQL, One can easily identify the numbers of input parameters.
Running Scripts From SQL Command Line. You can use a text editor to create SQL Command Line script files that contain SQL*Plus, SQL, and PL/SQL statements. For consistency, use the .sql extension for the script file name. A SQL script file is executed with a START or @ command. For example, in a Windows environment,...
Firstly, you will need to invoke your script like so:
sqlplus.exe MYUSER/mypassword@HOST030 @refreshDataOnOracle.sql foo bar
Instead of the OS redirection you will use the "@" symbol to indicate the file name to execute. You will also supply the script parameters on the command line. In the script you will refer to the parameters using &1, &2 etc.
update mytable set mycol = '&2' where myid = '&1';
which will translate into
update mytable set mycol = 'bar' where myid = 'foo';
If you want log the run of sqlplus, you can use this syntax:
sqlplus.exe MYUSER/mypassword@HOST030 @refreshDataOnOracle.sql foo bar >> log.log
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
