Sqlplus Statement from Command Line

sqlplus statement from command line

Just be aware that on Unix/Linux your username/password can be seen by anyone that can run "ps -ef" command if you place it directly on the command line . Could be a big security issue (or turn into a big security issue).

I usually recommend creating a file or using here document so you can protect the username/password from being viewed with "ps -ef" command in Unix/Linux. If the username/password is contained in a script file or sql file you can protect using appropriate user/group read permissions. Then you can keep the user/pass inside the file like this in a shell script:

sqlplus -s /nolog <<EOF
connect user/pass
select blah;
quit
EOF

How can I issue a single command from the command line through sql plus?

I'm able to run an SQL query by piping it to SQL*Plus:

@echo select count(*) from table; | sqlplus username/password@database

Give

@echo execute some_procedure | sqlplus username/password@databasename

a try.

SQLPLUS - Multiple sql statements from command line

Bit messy, but you can do:

C:>(echo set heading off & echo select count(*^^^) from v$session; & echo exit;) | sqlplus -s zabbix/pwd@localhost:1521/orcl

        53

The ^^^) part is to escape the parenthesis in the count(*), within the parentheses wrapping the two echo commands together - which provides a single input for SQL*Plus.

This has a blank line at the top; you might prefer to use set pagesize 0 instead of set heading off:

C:>(echo set pages 0 & echo select count(*^^^) from v$session; & echo exit;) | sqlplus -s zabbix/pwd@localhost:1521/orcl
        53

You can put multiple settings in one set command if you need to, as well.

Alternatively, just put all your commands in a script file, e.g. test.sql:

set pages 0
select count(*) from v$session;
exit

and then run that with:

sqlplus -s zabbix/pwd@localhost:1521/orcl @test

Run Oracle SQL script and exit from sqlplus.exe via command prompt

Another way is to use this command in the batch file:

echo exit | sqlplus user/pass@connect @scriptname

Sqlplus oracle : How can I run sql command on bash in 1 line?

You won't need the exit with automation because it should exit on end of file anyway. So on one line you could do:

echo 'EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);' | sqlplus / as sysdba

Run sql plus commands on Unix machine

User = 'PATH' # I can't read the file in this path
There is no file to read. You are assigning the string literal 'PATH' to the environment variable "User". You could just as well say "User = 'FUBAR'"

sqlplus $user << word # I don't know what it is used for

It is telling the OS to launch the executable 'sqlplus', pass it the value of the environment variable "$user", then redirect other input from the next lines of the script until it comes to the string literal 'word'. This is call 'input redirection, and the commands between this line and the line beginning with the word 'word' are sometimes referred to as "a 'here' document". Using the string literal 'word' to terminate it is wierd and misleading at best. Most people use some variation of 'EOF' for this purpose.

I don't know what it is expected to be used for either. In *nix, environment variables (as are file names) are case sensitive. So this variable , "user" is not the same variable, "User" as you set in the previous line.

And then i start writing sql commands then execute the script through
cmd

I'm not sure what you mean by this. Are you saying that at this point, your script has sql commands intended to be processed by sqlplus? As indicated by your use of input redirection?

But it prompted username : # which I don't know

Well, in spite of all the other issues, if you don't know the username and password, you will never be able to connect to the database.

How to make Oracle sqlplus command line utility non-interactive?

Looks like if sqlplus gets all of its commands piped to it into its standard input, it actually doesn't prompt the user for anything, no matter if the -S flag is provided or not. Also in this case the @script.sql parameter seems ignored (it doesn't get executed if given as a command line argument). So, to execute a script so that there are no prompts and errors make sqlplus actually exit with an error code, you have to call sqlplus like that:

sqlplus /nolog <<EOF
whenever sqlerror exit sql.sqlcode
connect $username/$password @ORCLPDB1
@script.sql
EOF

The $? variable is populated on error but the error code itself doesn't make much sense to me. It is not referring to the number in the ORA-XXXXX scheme.

ORACLE - How do I run a begin/end command in sqlplus command line

Add a slash at the end of the command.

BEGIN
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE('DEV_WORKSPACE', 'y', 'y');
END;
/

---

Related Topics