10 oracle tips part 1

 

1) how define an editor in sqlplus:

define _editor=vi

2)how to see editor values:

define;

3) how to write a select:

select ename,sal
from emp
where 1=1
and sal>2000
---and ename like 'B%';

4) how to create a dummy table
create table test as
select 1 as empid,1 as deptid,'a' as name,to_date('12/12/2000','dd/mm/yyyy') as dateaniv from dual
union all
select 2 as empid,2 as deptid, 'b' as name,to_date('01/01/2001','dd/mm/yyyy') as dateaniv from dual;

5)how to create or replace table:
CREATE OR REPLACE can only be used on functions, procedures, types, views, or packages - it will not work on tables.


6) history (oracle 12 only)
SQL> set history on

SQL> show history

SQL> show history
history is ON and set to “100”

SQL> history

SQL> history 4 run

SQL history 4 edit

SQL> clear history

7) how to change date format
permanent:
CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
AFTER LOGON ON DATABASE
call DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','dd/mm/yyyy');
temporary:
alter session NLS_DATE_FORMAT='dd/mm/yyyy';

8)How can I use the "up arrow" and down arrow" keys to display the command line history for all SQL*Plus commands IN LINUX?

Question:  I've been told that I need to use the rlwrap utility with SQL*Plus.  What is rlwrap and how does it help me in SQL*Plus?

Answer:  One nice features of using SQL*Plus in Windows is that you can use the "up arrow" and down arrow" keys to display the command line history for all SQL*Plus commands.

It's also possible to do this in Linux and UNIX (AIX, Solaris, HP/UX) with the freeware rlwrap utility.

You can download the freeware rlwrap utility at this link.

The readline wrapper (rlwrap) utility uses the GNU readline library.  Hence, rlwrap is not Oracle-centric, it's a standard OS utility available for all flavors of UNIX, Linux and even Windows.  The rlwrap software installs easily on UNIX/Linux with these standard GNU unzip and make commands:

I downloaded: rlwrap-0.43.tar.gz

gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Aliasing rlwrap

Following the install, you use a standard UNIX alias in your shell signon file (.cshrc for S shell, .bashrc for Bourne shell, or .profile for Korn shell) to source-in the rlwrap utility, using a different command name:

alias rl_sqlplus='rlwrap sqlplus'
alias rl_rman='rlwrap rman'
alias rl_asmcmd='rlwrap asmcmd'

Rampant author Laurent Schneider notes why you should never use an alias over-ride of the default Oracle names of "sqlplus", "asmcmd" or "rman":

Using rlwrap may affect the behavior of CTRL+C during interactive sessions. I've done a few tests on this and I can't see a difference in behavior of CTRL+C with or without rlwrap. Perhaps this was a problem with earlier versions.  Note that rlwrap only supports interactive sessions, so scripts like the following may not work as expected.

sqlplus alias:  From what I can see the alias doesn't seem to work from within a shell script (bash, ksh or csh), so it doesn't really present a danger. Remember, rlwrap is not an Oracle tool! I guess it's best to leave the sqlplus and rman commands clean and alias using a different name, just in case.

Using rlwrap

The rlwrap utility is a nice utility for command-line editing and an easy display of SQL*Plus command history.

9)how to add a column in a table

alter table test add dateaniv2 date;

10) not between AND between inversed

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;

 

 

this works in db2 and oracle:
and '01/02/1977' between dateaniv and dateaniv2;