How to View Oracle Stored Procedure using SQLPlus?
check your casing, the name is typically stored in upper case SELECT * FROM all_source WHERE name=”DAILY_UPDATE” ORDER BY TYPE, LINE;
sqlplus
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.
How to insert a string which contains an “&”
I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far. Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem – when it appears, it is expected to be part of a variable name. SET DEFINE OFF will stop … Read more
oracle SQL plus how to end command in SQL file?
For normal SQL statements, either a / on a line by itself, or a ; at the end of the command, will work fine. For statements that include PL/SQL code, such as CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, CREATE TYPE, or anonymous blocks (DECLARE/BEGIN/END), a ; will not execute the command. Since PL/SQL uses semicolons … Read more
Convert epoch to date in sqlplus / Oracle
In Oracle, adding X to a DATE will return you a DATE X days later. If ESTIMATEDENDTIME is milliseconds since Epoch then you could do DATE ‘1970-01-01’ + ( 1 / 24 / 60 / 60 / 1000) * ESTIMATEDENDTIME and then use to_char to achieve the correct format of the resulting date. e.g: SELECT … Read more
DBMS_OUTPUT.PUT_LINE not printing
What is “it” in the statement “it just says the procedure is completed”? By default, most tools do not configure a buffer for dbms_output to write to and do not attempt to read from that buffer after code executes. Most tools, on the other hand, have the ability to do so. In SQL*Plus, you’d need … Read more
SQL*Plus how to accept text variable from prompt?
You have to specify the data type as part of the ACCEPT statement. If none is given, it assumes a number. Try ACCEPT myVariable CHAR PROMPT ‘Input value: ‘; instead.
Connect to sqlplus in a shell script and run SQL scripts
For example: sqlplus -s admin/password << EOF whenever sqlerror exit sql.sqlcode; set echo off set heading off @pl_script_1.sql @pl_script_2.sql exit; EOF
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 … Read more
Sql*plus always returns exit code 0?
You have to explicitly tell sqlplus to do that, in your script. Basically, there are two statements that you can use: WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT For example: WHENEVER SQLERROR EXIT SQL.SQLCODE begin SELECT COLUMN_DOES_NOT_EXIST FROM DUAL; END; / And for OS errors: WHENEVER OSERROR EXIT FAILURE START no_such_file For more information, see … Read more