Bulk Querying with the BULK COLLECT Clause

resolve lack aio library problem

rpm -qa --qf '%{name}-%{version}-%{release}.%{arch}.rpm\n' | grep binutils

ORBit-0.5.17-14.i386.rpm
ORBit-0.5.17-14.x86_64.rpm
binutils-2.15.92.0.2-21.x86_64.rpm
compat-db-4.1.25-9.i386.rpm
compat-db-4.1.25-9.x86_64.rpm
compat-libstdc++-33-3.2.3-47.3.x86_64.rpm
compat-libstdc++-296-2.96-132.7.2.i386.rpm
compat-libstdc++-33-3.2.3-47.3.i386.rpm
control-center-2.8.0-12.rhel4.5.x86_64.rpm
gcc-3.4.6-3.x86_64.rpm
gcc-c++-3.4.6-3.x86_64.rpm
glibc-2.3.4-2.25.x86_64.rpm
glibc-2.3.4-2.25.i686.rpm
glibc-common-2.3.4-2.25.x86_64.rpm
glibc-devel-2.3.4-2.25.i386.rpm
glibc-devel-2.3.4-2.25.x86_64.rpm
glibc-headers-2.3.4-2.25.x86_64.rpm
glibc-kernheaders-2.4-9.1.98.EL.x86_64.rpm
gnome-libs-1.4.1.2.90-44.1.x86_64.rpm
gnome-libs-1.4.1.2.90-44.1.i386.rpm
libaio-0.3.105-2.i386.rpm
libaio-0.3.105-2.i386.rpm
lack libaio-0.3.105-2.x86_64.rpm
libaio-devel-0.3.105-2.x86_64.rpm
libgcc-3.4.6-3.i386.rpm
libgcc-3.4.6-3.x86_64.rpm
libstdc++-3.4.6-3.x86_64.rpm
libstdc++-3.4.6-3.i386.rpm
libstdc++-devel-3.4.6-3.i386.rpm
libstdc++-devel-3.4.6-3.x86_64.rpm
make-3.80-6.EL4.x86_64.rpm
pdksh-5.2.14-30.3.x86_64.rpm
sysstat-5.0.5-11.rhel4.x86_64.rpm
xorg-x11-deprecated-libs-6.8.2-1.EL.13.36.x86_64.rpm
xorg-x11-deprecated-libs-6.8.2-1.EL.13.36.i386.rpm
xorg-x11-deprecated-libs-devel-6.8.2-1.EL.13.36.x86_64.rpm
xscreensaver-4.18-5.rhel4.11.x86_64.rpm

# run with root
rpm -ivh libaio-0.3.105-2.x86_64.rpm
rpm -ivh libaio-devel-0.3.105-2.x86_64.rpm

# run with oracle
$ORACLE_HOME/bin/relink all
$ORACLE_HOME/bin/ldd oracle

perl CPAM install ReadLine::Gnu

[root@test pkg]# perl pmlist.pl
IO::Tty -- 1.07
Perl -- 5.8.0

pmlist.pl
#!/usr/bin/perl

use ExtUtils::Installed;
my $instmod = ExtUtils::Installed->new();
foreach my $module ($instmod->modules()) {
my $version = $instmod->version($module) || "???";
       print "$module -- $version\n";
}

[root@test pkg]# perl -MCPAN -e shell

cpan shell -- CPAN exploration and modules installation (v1.61)
ReadLine support available (try 'install Bundle::CPAN')

cpan> install Term::ReadLine::Gnu
Running install for module Term::ReadLine::Gnu
Running make for H/HA/HAYASHI/Term-ReadLine-Gnu-1.16.tar.gz
CPAN: Digest::MD5 loaded ok
Checksum for /root/.cpan/sources/authors/id/H/HA/HAYASHI/Term-ReadLine-Gnu-1.16.tar.gz ok
Scanning cache /root/.cpan/build for sizes
Term-ReadLine-Gnu-1.16/
Term-ReadLine-Gnu-1.16/eg/
Term-ReadLine-Gnu-1.16/eg/fileman
Term-ReadLine-Gnu-1.16/eg/perlsh
Term-ReadLine-Gnu-1.16/eg/pftp
Term-ReadLine-Gnu-1.16/eg/ptksh+
Term-ReadLine-Gnu-1.16/Gnu/
Term-ReadLine-Gnu-1.16/Gnu/euc_jp.pm
Term-ReadLine-Gnu-1.16/Gnu/XS.pm
Term-ReadLine-Gnu-1.16/Gnu.pm
Term-ReadLine-Gnu-1.16/Gnu.xs
Term-ReadLine-Gnu-1.16/INSTALL
Term-ReadLine-Gnu-1.16/Makefile.PL
Term-ReadLine-Gnu-1.16/MANIFEST
Term-ReadLine-Gnu-1.16/META.yml
Term-ReadLine-Gnu-1.16/ppport.h
Term-ReadLine-Gnu-1.16/README
Term-ReadLine-Gnu-1.16/t/
Term-ReadLine-Gnu-1.16/t/button.pl
Term-ReadLine-Gnu-1.16/t/callback.t
Term-ReadLine-Gnu-1.16/t/comptest/
Term-ReadLine-Gnu-1.16/t/comptest/0123
Term-ReadLine-Gnu-1.16/t/comptest/012345
Term-ReadLine-Gnu-1.16/t/comptest/023456
Term-ReadLine-Gnu-1.16/t/comptest/a_b
Term-ReadLine-Gnu-1.16/t/comptest/README
Term-ReadLine-Gnu-1.16/t/history.t
Term-ReadLine-Gnu-1.16/t/inputrc
Term-ReadLine-Gnu-1.16/t/readline.t
Term-ReadLine-Gnu-1.16/typemap
Removing previously used /root/.cpan/build/Term-ReadLine-Gnu-1.16

  CPAN.pm: Going to build H/HA/HAYASHI/Term-ReadLine-Gnu-1.16.tar.gz

Found `/usr/lib/libtermcap.a'.
gcc  -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -DHAVE_STRING_H rlver.c -o rlver    -L/usr/local/lib -lreadline -ltermcap
It seems that you have the GNU Readline Library version 4.3.
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadLine::Gnu
cp Gnu.pm blib/lib/Term/ReadLine/Gnu.pm
cp Gnu/XS.pm blib/lib/Term/ReadLine/Gnu/XS.pm
AutoSplitting blib/lib/Term/ReadLine/Gnu/XS.pm (blib/lib/auto/Term/ReadLine/Gnu/XS)
cp Gnu/euc_jp.pm blib/lib/Term/ReadLine/Gnu/euc_jp.pm
/usr/bin/perl /usr/lib/perl5/5.8.0/ExtUtils/xsubpp  -typemap /usr/lib/perl5/5.8.0/ExtUtils/typemap -typemap typemap  Gnu.xs > Gnu.xsc && mv Gnu.xsc Gnu.c
gcc -c   -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -march=i386 -mcpu=i686   -DVERSION=\"1.16\" -DXS_VERSION=\"1.16\" -fPIC "-I/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE"  -DHAVE_STRING_H Gnu.c
Running Mkbootstrap for Term::ReadLine::Gnu ()
chmod 644 Gnu.bs
rm -f blib/arch/auto/Term/ReadLine/Gnu/Gnu.so
LD_RUN_PATH="/usr/lib:/lib" gcc  -shared -L/usr/local/lib Gnu.o  -o blib/arch/auto/Term/ReadLine/Gnu/Gnu.so   -lreadline -ltermcap
chmod 755 blib/arch/auto/Term/ReadLine/Gnu/Gnu.so
cp Gnu.bs blib/arch/auto/Term/ReadLine/Gnu/Gnu.bs
chmod 644 blib/arch/auto/Term/ReadLine/Gnu/Gnu.bs
Manifying blib/man3/Term::ReadLine::Gnu.3pm
  /usr/bin/make  -- OK
Running make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/callback....ok
        4/7 skipped: since Tk is not installed.

t/history.....FAILED tests 73-74
        Failed 2/82 tests, 97.56% okay

t/readline....ok        Try `/usr/bin/perl -Mblib t/readline.t verbose', if you will.
t/readline....ok
Failed Test Stat Wstat Total Fail  Failed  List of Failed
-------------------------------------------------------------------------------
t/history.t               82    2   2.44%  73-74
4 subtests skipped.
Failed 1/3 test scripts, 66.67% okay. 2/193 subtests failed, 98.96% okay.
make: *** [test_dynamic] エラー 29
  /usr/bin/make test -- NOT OK
Running make install
  make test had returned bad status, won't install without force

cpan> quit
Lockfile removed.

Confirm the error message

[root@test Term-ReadLine-Gnu-1.16]# pwd
/root/.cpan/build/Term-ReadLine-Gnu-1.16

I resolved it finally with

force install ...

uniread - the best oracle command mate

uniread adds readline (line editing, command history, etc.) support to any
existing interactive command-line program. You can use this to add support
to annoying programs which do not provide it built-in (a good example is
Oracle's sqlplus, or jython) or to easily add it to your own programs.

To build this distribution, run:

  perl Makefile.PL
  make
  make install

uniread requires:

- GNU readline
http://cnswww.cns.cwru.edu/php/chet/readline/rltop.html
- Term::ReadLine::Gnu
http://search.cpan.org/dist/Term-ReadLine-Gnu/
- IO::Tty
http://search.cpan.org/dist/IO-Tty/
or http://sourceforge.net/projects/expectperl


The latter two can be installed using the CPAN shell. Run:

  perl -MCPAN -e shell

  install Term::ReadLine::Gnu
  install IO::Tty

-----
By me:
Add a command to .bash_profile/profile will help common user to uniread

# proxy
export http_proxy=http://proxy:8080/
export ftp_proxy=http://proxy:8080/

# initialize CPAN, just press enter except download site
perl -MCPAN -e shell

# install the pre-required modules, force for 86-64
perl -MCPAN -e shell << __EOF__
force install Term::ReadLine::Gnu
install IO::Tty
__EOF__

# copy and install uniread package
cd ~
[ ! -d pkg ] && mkdir pkg
pushd pkg
cp -a ~oracle/work/fyang/ofpkg/uniread*.tar.gz .
tar -zxvf uniread*.tar.gz
cd uniread*
perl Makefile.PL
make install
popd

# profile
cat >> /etc/profile << __EOF__
alias ufsqlplus="echo enter popd to return to original dir. ; pushd ~oracle/work/fyang ; uniread --quiet sqlplus "
alias sqlplusuf="echo enter popd to return to original dir. ; pushd ~oracle/work/fyang ; uniread --quiet sqlplus "
alias usqlplus="uniread --quiet sqlplus "
alias sqlplusu="uniread --quiet sqlplus "
__EOF__

rami.mawas the tips of googlepages

Fw Google Appsをさらに使いやすく「Hosted Gmail Client」

http://bluedot.us/users/relia/tag/Google

Note of Hibernate & Spring Testing

Recently, I complete tested the function of Hibernate and Spring.
I wrote this note to memo the experience.

* install the version is a point of hibernate
download the version at least the same major version software.
it may be the permenant weak point of the open source.
* midgen maps bit (boolean) to object
manually modify the attibute of the field to make it map to right java type.
* to make the update effact to all related object
add cascade="save-update" to set tag
* add named query

* to make spring handle the transaction

PROPAGATION_REQUIRED
  

* generate hbm.xml from java soruce with xdoclet
ant generate-hbm-from-xdoclet

Oracle Killing Sessions Version 10.2

kill oracle sessions scrit (with sqlplus)

select s.sid, s.serial#, s.username, spid, 
osuser, s. program, schemaname 
from v$session s, v$process p 
where machine like '%ta%' and s.program like '%%' and s.paddr = p.addr
/

select 'alter system kill session ''' 
|| s.sid || ',' || s.serial# || '''; -- ' 
|| s.machine || ',' || s.program  
from v$session s, v$process p 
where machine like '%%' and s.program like '%%' and s.paddr = p.addr
/

---

Oracle Killing Sessions
Version 10.2

UNIX
Killing sessions in the UNIX environment ps -ef | grep ora to find Oracle processes. Be sure to get the process id of the session you are trying to kill.

kill -1
or
kill -9
kill -9 5745
All in one kill ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill -9

Windows
Killing sessions in the Windows environment with ORAKILL. orakill
SELECT instance_name
FROM gv$instance;

col program format a30

SELECT spid, osuser, s.program, schemaname
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr;

c:\oracle\product\ora102\bin> orakill orabase spid

All
Killing sessions from inside the database To kill sessions within the database requires the ALTER SYSTEM privilege and the sid and serial# of the session to be killed

GRANT alter system TO ;

SELECT sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = ;

ALTER SYSTEM KILL SESSION '< sid>,';
conn / as sysdba

GRANT alter system TO aqadmin;

conn aqadmin/aqadmin

SELECT sid, serial# , username, schemaname, osuser
FROM gv$session
WHERE username = 'AQUSER';

SQL> ALTER SYSTEM KILL SESSION '9,177' IMMEDIATE;

Kill All Sessions
Kill All Instance Sessions conn / as sysdba

set heading off
set termout off
set verify off
set echo off
set feedback off

ALTER SYSTEM enable restricted session;

ALTER SYSTEM checkpoint global;

spool kill_all.sql

SELECT 'execute kill_session('|| chr(39) || sid || chr(39) || ',' || chr(39) || serial# || chr(39) || ');'
FROM gv_$session
WHERE (username IS NOT NULL OR username <> 'SYS');

spool off

@kill_all

Session Kill Demo Procedure
An infinite loop for testing CREATE OR REPLACE PROCEDURE infinite_loop IS

BEGIN
LOOP
NULL;
END LOOP;
END infinite_loop;
/

SQL> exec infinite_loop

Oracle SQL*Plus Version 10.2

Oracle SQL*Plus
Version 10.2

General
Constants Constant Usage Example
SQL.LNO Line Number SELECT COUNT(*)
FROM all_objects;

show lno
SQL.PNO Page Number SELECT object_name
FROM all_objects;

show pno
SQL.RELEASE Oracle Version show release
SQL.SQLCODE Current error code show sqlcode
SQL.USER Currently connected user show user

Startup Parameters: Usage 1
Flags
Description
-H Displays the SQL*Plus version and the usage help
-V Displays the SQL*Plus version

sqlplus -C | -H
Startup Parameters: Usage 2
Flags
Description
-C Sets the compatibility of affected commands to the version specified. The version has the form "x.y[.z]. For example -C 10.2.0
-L Attempts to log on just once, instead of reprompting on error
-M
-R Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2, or 3 with R3 being the most restrictive and disables all such commands
-S Sets the silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands

sqlplus [ [] [] [start>] ]
Logon Parameters sqlplus [/password][@] | /
[AS SYSDBA | AS SYSOPER | /NOLOG
sqlplus system/manager@orabase AS SYSOPER /NOLOG
Start Parameters @|[.] [ ....]
TBD

View All Parameters
List SQL*Plus Parameters show all
SQL> show all

Column Formatting
Character COL FORMAT a
SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;

COL object_name FORMAT a30

SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;
Money COL FORMAT $99,999
COL salary FORMAT $99,999

Number
Element Example Description
9 9999 Number of significant digits returned
COL test FORMAT 99.99
SELECT 100/3 TEST FROM dual;

COL test FORMAT 99.9999
SELECT 100/3 TEST FROM dual;
0 0999
9990 Display a leading zero or a value of zero in this position as 0
COL test FORMAT 099.999
SELECT 100/3 TEST FROM dual;
$ $9999 Prefixes with dollar sign
CREATE TABLE t (
test NUMBER(10,4));

INSERT INTO t VALUES (1234);
INSERT INTO t VALUES (-234);
INSERT INTO t VALUES (0);

COL test FORMAT $9999

SELECT * FROM t;
B B9999 Display a zero value as blank
COL test FORMAT B9999

SELECT * FROM t;
MI 9999MI Display "-" after a negative value
COL test FORMAT 9999MI

SELECT * FROM t;
S S9999 Display "+" for positive values and "-" for negative values
COL test FORMAT S9999

SELECT * FROM t;
PR 9999PR Displays a negative value in
COL test FORMAT 9999PR

SELECT * FROM t;
D 99D99 Display the decimal character
COL test FORMAT 9999D99

SELECT * FROM t;
G 9G999 Display the group separator
COL test FORMAT 9G999

SELECT * FROM t;
C C999 Display the ISO currency symbol
COL test FORMAT C9999

SELECT * FROM t;
L L999 Display the local currency symbol
COL test FORMAT L9999

SELECT * FROM t;
, 9,999 Display a comma
COL test FORMAT 9,999

SELECT * FROM t;
. 99.99 Display a period
COL test FORMAT 9999.99

SELECT * FROM t;
V 999V99 Multiplies value by 10n, where n is number of "9"s after "V"
COL test FORMAT 9999V99

SELECT * FROM t;
EEEE 9.999EEEE Display value in scientific notation
COL test FORMAT 9999.99EEEE

SELECT * FROM t;
RN or rn RN Display upper or lowercase Roman numerals. Value can be an integer between 1 and 3999
COL test FORMAT RN

SELECT * FROM t;
DATE DATE Format a NUMBER columns that represent Julian dates as MM/DD/YY
COL test FORMAT DATE

SELECT * FROM t;


Arraysize

Array Size Demo ARRAYSIZE nnn
The default value of nnn is 15, which is way
too small for large data transfers. Try larger and larger values of nnn until response improvements are insignificant.

CREATE TABLE t AS
SELECT *
FROM all_objects;

SELECT COUNT(*)
FROM t;

CREATE INDEX t_idx
ON t(object_id)
PCTFREE 0;

set autotrace traceonly

show arraysize

SELECT * FROM t;

SELECT * FROM t;

SELECT * FROM t;

set arraysize 10

SELECT * FROM t;

set arraysize 100

SELECT * FROM t;

set arraysize 250

SELECT * FROM t;

Connect
CONN as SYS CONN AS
conn / as sysdba

conn sys@orabase AS SYSDBA
CONN as a user CONN / @
conn uwclass/uwclass@orabase

conn uwclass@orabase

Define / Undefine
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Nov 19 15:20:10 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> DEFINE _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "ORABASE" (CHAR)

SQL> DEFINE _DATE
DEFINE _DATE = "19-NOV-04" (CHAR)

SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)

SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)

SQL> DEFINE _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)

SQL> DEFINE _USER
DEFINE _USER = "SYS" (CHAR)

SQL>UNDEFINE _USER

Describe
Describe a function desc
CREATE OR REPLACE FUNCTION dayofweek (stringin VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
NULL;
END dayofweek;
/

desc dayofweek
Describe a package desc
CREATE OR REPLACE PACKAGE demopkg IS

PROCEDURE demoproc;
FUNCTION demofunc RETURN BOOLEAN;

END;
/

desc demopkg

CREATE OR REPLACE PACKAGE BODY demopkg IS

PROCEDURE demoproc IS
BEGIN
NULL;
END;

FUNCTION demofunc RETURN BOOLEAN IS
BEGIN
RETURN TRUE;
END;

END demopkg;
/

desc demopkg
Describe a procedure desc
CREATE OR REPLACE PROCEDURE demoproc (numbin NUMBER,
stringin IN OUT VARCHAR2, tfin OUT BOOLEAN)

BEGIN
NULL;
END demoproc;
/

desc demoproc
Describe a table desc
CREATE TABLE demotable (
Describe an object table set describe depth all {linenum } {INDENT }
CREATE OR REPLACE TYPE rectangle_t AS OBJECT (
h NUMBER,
w NUMBER,
x NUMBER,
y NUMBER);
/

CREATE TABLE rectable (
rectangle_name VARCHAR2(20),
rectangle rectangle_t);

desc rectable

set describe depth all

desc rectable

set describe depth all linenum on indent on

desc rectable
Describe a view desc
CREATE OR REPLACE VIEW demoview AS
SELECT *
FROM demotable;

desc demotable
Describe a synonym desc
CREATE SYNONYM demosyn FOR rectable;

desc demosyn

Display
Clear Screen clear scr
SELECT object_name, created
FROM all_objects
WHERE ROWNUM < 2;

clear scr
Column Separators SET COLSEP
set colsep ','

SELECT table_name, column_name, data_type
FROM user_tab_columns
WHERE rownum < 10;
Display Headers SET HEAD
SELECT table_name FROM all_tables;

set head off

SELECT table_name FROM all_tables;

set head on
Line Size SET LINESIZE
SELECT text
FROM all_source
WHERE rownum < 21;

set linesize 121

SELECT text
FROM all_source
WHERE rownum < 21;
Page Size SET PAGESIZE
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 20

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 0

SELECT object_name
FROM all_objects
WHERE rownum < 60;
Page Break BREAK ON [SKIP PAGE]
break on overload skip page

set pagesize 20
set linesize 121
col overload format a8

SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;
Pause SET PAUSE
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause on

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause off
Timing SET TIMING
SELECT COUNT(*)
FROM all_objects;

set timing on

SELECT COUNT(*)
FROM all_objects;
Title ttitle {LEFT } {CENTER } {RIGHT }
set pagesize 25

ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO

SELECT * FROM dual;

ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - 'Oracle Application Development'

SELECT object_name
FROM all_objects
WHERE rownum < 60;
Display Output From DBMS_OUTPUT.PUT_LINE built-in package set serveroutput
DECLARE
x VARCHAR2(20) := 'This is a test';
BEGIN
dbms_output.put_line(x);
END;
/

set serveroutput on

DECLARE
x VARCHAR2(20) := 'This is a test';
BEGIN
dbms_output.put_line(x);
END;
/

Edit / Editor
Define An Editor _editor
define _editor=vi
Edit The Last Command ed
ed

Error Handling
OS Errors WHENEVER OSERROR
WHENEVER OSERROR EXIT
@c:\temp\nofile.sql
SQL Errors WHENEVER SQLERROR
WHENEVER OSERROR EXIT SQL.SQLCODE

Execute
Run a stored procedure exec
CREATE OR REPLACE PROCEDURE demoexec IS
BEGIN
dbms_output.put_line('*** Executed ***');
END demoexec;
/

set serveroutput on

exec demoexec

Help
Display SQL*Plus Help help
help index

help variable

Host
Shell to the operating system host
host

exit
Unix Shell !
SQL> !

exit
Windows Shell $
SQL> $

exit

Password
Change Password password
password

Quit
Exit exit
exit
Quit quit
quit

Run
Run RUN
set serveroutput on

BEGIN
dbms_output.put_line('test');
END;
/

run

Run Script
@ @
@c:\oracle\product\ora102\rdbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

@?\rdbms\admin\catplan.sql
Get get
get c:\oracle\product\ora102\rdbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

get ?\rdbms\admin\catplan.sql

Save
Save the most recently executed SQL statement save
SELECT tablespace_name, status
FROM all_tablespaces;

save ts

SQL> host

$ more ts

$ exit

Spool
Spool

Short version: SPO spo[ol] [path_and_file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
spool c:\temp\zzyzx.txt

SELECT table_name
FROM all_tables;

spo off

spo c:\temp\zzyzx.txt app

SELECT object_id
FROM user_objects

spool off
Termout termout
termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files.

abc.sql:
set termout off
select 'abc' from dual

and run it like this in sqlplus:
@abc.sql
Trimspool trimspool
spool c:\temp\trimspool.txt

SELECT table_name
FROM all_tables
WHERE rownum < 11;

trimspool on

SELECT table_name
FROM all_tables
WHERE rownum < 11;

spool off

SQL Prompt
Set the SQL Prompt sqlprompt Predefined Variable Description
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available.
_DATE Current date, or a user defined fixed string.
_EDITOR Specifies the editor used by the EDIT command.
_O_RELEASE Full release number of the installed Oracle Database.
_O_VERSION Current version of the installed Oracle Database.
_PRIVILEGE Privilege level of the current connection.
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component.
_USER User name used to make connection.

SQL>

SQL> set sqlprompt "_user'@'_connect_identifier>"
SYS@orabase>conn uwclass/uwclass
Connected.
UWCLASS@orabase>

Variable
Define Variable variable
variable x VARCHAR2(20)

variable x

BEGIN
:x := 'ABC';
END;
/
View Variable Value print
print x
Undefine Variable undefine
undefine x

Save Settings
glogin.sql
login.sql
State File SQL> show linesize
SQL> set linesize 120
SQL> show linesize
SQL> STORE SET statefile REPLACE
SQL> EXIT

SQL> show linesize
SQL> @statefile
SQL> show linesize

cosmo gate - password protected homepage (basic)