Ugly formatting in SQL*Plus
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
sqlplus
How to output oracle sql result into a file in windows?
Use the spool: spool myoutputfile.txt select * from users; spool off; Note that this will create myoutputfile.txt in the directory from which you ran SQL*Plus. If you need to run this from a SQL file (e.g., “tmp.sql”) when SQLPlus starts up and output to a file named “output.txt”: tmp.sql: select * from users; Command: sqlplus … Read more
executing a function in sql plus
declare x number; begin x := myfunc(myargs); end; Alternatively: select myfunc(myargs) from dual;
PL/SQL ORA-01422: exact fetch returns more than requested number of rows
A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, … Read more
Oracle query execution time
One can issue the SQL*Plus command SET TIMING ON to get wall-clock times, but one can’t take, for example, fetch time out of that trivially. The AUTOTRACE setting, when used as SET AUTOTRACE TRACEONLY will suppress output, but still perform all of the work to satisfy the query and send the results back to SQL*Plus, … Read more
sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
The minimum configuration to properly run sqlplus from the shell is to set ORACLE_HOME and LD_LIBRARY_PATH. For ease of use, you might want to set the PATH accordingly too. Assuming you have unzipped the required archives in /opt/oracle/instantclient_11_1: $ export ORACLE_HOME=/opt/oracle/instantclient_11_1 $ export LD_LIBRARY_PATH=”$ORACLE_HOME” $ export PATH=”$ORACLE_HOME:$PATH” $ sqlplus SQL*Plus: Release 11.1.0.7.0 – Production on … Read more
How do I Suppress “PL/SQL procedure successfully completed” message in sqlplus?
Use the command: SET FEEDBACK OFF before running the procedure. And afterwards you can turn it back on again: SET FEEDBACK ON
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
How do I resolve this “ORA-01109: database not open” error?
As the error states – the database is not open – it was previously shut down, and someone left it in the middle of the startup process. They may either be intentional, or unintentional (i.e., it was supposed to be open, but failed to do so). Assuming that’s nothing wrong with the database itself, you … Read more
insert a multiline string in Oracle with sqlplus
Enable SQLBLANKLINES to allow blank lines in SQL statements. For example: SET SQLBLANKLINES ON insert into table(id, string) values (1, ‘Line1goesHere Line2GoesHere blablablabla ‘); The premise of this question is slightly wrong. SQL*Plus does allow multi-line strings by default. It is only blank lines that cause problems.