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.
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.
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
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
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
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
As you keep getting pages of results I’m assuming you started the session in SQL*Plus. If so, the easy thing to do is to bash ctrl + break many, many times until it stops. The more complicated and the more generic way(s) I detail below in order of increasing ferocity / evil. The first one … Read more
Try with: xargs –show-limits </dev/null Your environment variables take up 2446 bytes POSIX upper limit on argument length (this system): 2092658 POSIX smallest allowable upper limit on argument length (all systems): 4096 Maximum length of command we could actually use: 2090212 Size of command buffer we are actually using: 131072 There is no limit per … Read more
From Get table and index DDL the easy way: set heading off; set echo off; Set pages 999; set long 90000; spool ddl_list.sql select dbms_metadata.get_ddl(‘TABLE’,’DEPT’,’SCOTT’) from dual; select dbms_metadata.get_ddl(‘INDEX’,’DEPT_IDX’,’SCOTT’) from dual; spool off;
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