LevSelector.com New York
home > Oracle

Oracle
On This Page Other Pages
* intro
* tools
* books
* hints

* Oracle Bulk Operations
*
*
*



 
Intro home - top of the page -

* technet.oracle.com - Oracle Technology Network
* asktom.oracle.com - Thomas Kyte's site
* directory.google.com/Top/Computers/Software/Databases/Oracle - Oracle on Google
* www.learningoracle.com - book recomendations
* oracle.oreilly.com - Oracle books on O'Reilly
* www.oramag.com - Oracle Magazine
* oracle.ittoolbox.com -
* education.oracle.com -Oracle courses and certification tests
* http://www.orafaq.com = http://www.orafaq.net - FAQ, Scripts, whole world
* http://www.orafaq.net/faqscrpt.htm - Oracle Scripts - very good
* www.oriolecorp.com/index1.html - Free Oracle SQL scripts and DBA tools

* www.revealnet.com - RevealNet, Oracle information portal.
* www.dbasupport.com/cert/ - Oracle Certification training
* www.dbasupport.com/dbres/Technical_Docs/Tutorials/ - tutorials
* www.dbasupport.com/faq/ - Oracle FAQ.
* www.oraclenotes.com/ - Study Oracle Notes and Forums

* www.animatedlearning.com/ - animated tutorials
* www.sideris.com/curriculum.asp - training (for $$ - but has free contents)
* Google search for video+training+oracle - find video training on Google

* www.oracle.com/oramag/code/ - Tips from Oracle
* www.revealnet.com/dbapipe/tips.htm - tips on RevealNet

* www.osborne.com/oracle/dbafaq/dbafaq1.htm - A good FAQ for administrators.
* www.theoracleresourcestop.com/ - discussions, scripts, info, links, white papers

* www.geocities.com/SiliconValley/Peaks/4535/oracle/ - Biju's Oracle DBA page - scripts, tips, tools, utilities, unix scripts, gensql, hints, rdbms, oracle FAQ
* www.rchath.com - Rakesh Chathli's Site on Oracle Financials' implementation issues -
Solutions to various Oracle Financials' implementation issues as posted on OraApps List


 
Tools home - top of the page -

Tools For Oracle:

Oracle download page - here you can download the client and the database:
    http://www.oracle.com/technology/software/products/database/
To play with Oracle on your PC you can download Oracle Express Edition:
    http://www.oracle.com/technology/products/database/xe/
It comes with a client - so that all your tools will work.



 
Books home - top of the page -

Books:

Old books:

 


 
Hints home - top of the page -

Put SQL commands into a file (e.g.  test.sql) and run it from unix prompt:

cat  test.sql  |  sqlplus scott/tiger@starter

Note: remember to put a semicolon at the end of every SQL statement
 
 
login: oracle 
password: (root password) 
ps -ef | grep smon 
export ORACLE_SID=starter 
sqlplus system/manager@starter 
// or from SQL> connect system/manager@starter 
// SQL clients: sqlplusw, SQL Navigator, TOAD 
select name from v$database 
select * from cat 
select * from dict 
!pwd 
spool datadict.txt 
... 
spool off 

desc cat 
desc dict 
column table_name format A16; 
column comments format A50; 
select * from dict where rownam<10; 
select table_name from dict where table_name like '%USER%'; 
connect scott/tiger@starter 
select table_name from user_tables; 
desc all_objects 
select owner, object_name from all_objects where owner = 
select table_name from user_tables; 
select user_name from dba_users; 
sho user 
select tablespace_name from dba_tables; 

create user myuser identified by //here is her password 
default tablespace USERS 
temporary tablespace TEMP; 
grant connect, create table to myuser
desc dba_sys_priv 

select table_name from dict where table_name like '%PRIV%'; 
desc dba_role_privs 

 

Here is how to get 10 rows of the result - and then next 10:
SELECT *
  FROM ( SELECT * FROM my_table ORDER BY my_col )
  WHERE ROWNUM <= 10;

Next 10: just grab all of them out to the end, and only show the last 10 (possibly adding
ROWNUM as a SELECTed column.

SELECT col1, col2, col3
  FROM ( SELECT ROWNUM AS row_num, col1, col2, col3
           FROM ( SELECT col1, col2, col3 FROM my_table ORDER BY col1 )
           WHERE ROWNUM <= 20 )
  WHERE row_num >= 11;
 

How to update / delete depending on a join to another table:
 
update maintab m 
set (m.fname,m.lname) = 
      (select u.fname,u.lname from updates u where m.id=u.id) 
where exists 
      (select u.fname,u.lname from updates u where m.id=u.id)

Note: if you omit the "where exists" clause - then all rows in the maintab will be updated (those rows which don't belong to inner join will be updated by NULLs).

update maintab m 
set m.name = (select u.name from updates u where m.id=u.id) 
where exists  (select u.name from updates u where m.id=u.id) 

delete from maintab m 
where exists  (select u.name from updates u where m.id=u.id and u.dept='RESEARCH') 

Outer join - include all departments:
SELECT  d.deptno,e.empno, e.ename, e.job
FROM   emp e, dept d
WHERE   d.deptno = e.deptno(+); 

Working with Oracle from Perl:
 
Setting up DBD-Oracle: 
untar the files as usual. 
procedure standard: 
perl Makefile.PL 
make 
make test 
make install

but before running all this make sure that you've done 2 things: 
1. add env. variables - here is part of .bash_profile : 
ORACLE_BASE=/usr/local/oracle/8i/u01/app/oracle 
ORACLE_HOME=$ORACLE_BASE/product/8.1.6 
ORACLE_SID=starter
CLASSPATH=.:~oracle/jre118_v1/lib/rt.jar 
PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin 
ENV=$HOME/.bashrc 
USERNAME="root" 
PS1="\u@\h \w#  " 
mesg n 
export USERNAME ENV PATH ORACLE_BASE ORACLE_HOME ORACLE_SID CLASSPATH 
------------------------------------------------------- 
2. put your $ORACLE_HOME/lib in your /etc/ld.so.conf
(expand it to the explicit path) and then re-run  /sbin/ldconfig.

You may need to add database name into test.pl (or edit other env. accordingly) 
------------------------------------------------------------------------------ 
log files: 
  $ORACLE_BASE/admin/starter/udump 
  $ORACLE_BASE/admin/starter/bdump 

Here is an example of simple Perl script using DBD-Oracle: 
#!/usr/local/bin/perl 
# oratest.cgi
use DBI; 
$ENV{ORACLE_HOME}='/usr/local/oracle/8i/u01/app/oracle/product/8.1.6'; 
my $dbh = DBI->connect("dbi:Oracle:starter", "scott", "tiger", { RaiseError => 1, PrintError => 1 } ) 
  or die $DBI::errstr; 
my $sql = "SELECT * FROM EMP"; 
my $sth = $dbh->prepare($sql); 
my $rv  = $sth->execute(); 

print "Content-Type: text/html\n\n"; 
print "<html><head><title>test</title></head><body><table>\n"; 

while(@row = $sth->fetchrow_array) { 
   print "<tr>\n"; 
        map { print "<td>$_</td>\n"; } @row; 
   print "</tr>\n"; 
} 

print "</table></body></html>\n"; 
$dbh->disconnect; 
exit(); 

More examples on how to work with Oracle from Perl you can find in documentation for DBD::Oracle. For example, here is how to execute pieces of PL/SQL code:

(from http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#PL/SQL_Examples )

# Calling a PLSQL procedure:

########## with no parameters
$csr = $db->prepare(q{ begin mypackage.proc1; end; });
$csr->execute;

########## with one IN and one OUT parameters
my $pin = 5;
my $pout;
$csr = $db->prepare(q{ begin mypackage.proc2(:pin, :pout); end; });
$csr->bind_param(":pin", $pin);
$csr->bind_param_inout(":pout", \$pout, 1);
$csr->execute;
print "$pin, $pout\n";

########## return value
my $retval = "";
$csr = $db->prepare(q{ begin :retval := mypackage.func1; end; });
$csr->bind_param_inout(":retval", \$retval, 20);
$csr->execute;
print "$retval\n";

########## processing error
     # procedure mypackage.proc3() has the following code:
     # RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
$db->{RaiseError} = 1;
my $err_code = -20001;
$csr = $db->prepare(q{ begin mypackage.proc3(:err_code); end; });
$csr->bind_param(":err_code", $err_code);
eval { $csr->execute; };
print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";

PL/SQL does not always give you details about errors in procedures. To get more info do this:

show errors; -- shows the last errors. You can also abbriviate it as "SHO ERR".

or something like this:

show errors procedure myproc;

------------------------------------------------------------------------------

"decode" function - if/else, if/else, ..., default - functionality

decode( expression , search , result [, search , result]... [, default] )

SELECT
    decode(company_id,
       1, 'Company 1',
       2, 'Company 2',
       'Other') 'Company Name'
FROM companies;

"NVL" function - replace null value with some other value

NVL( string1, string2 )

string1 is the string to test for a null value.
string2 is the value returned if string1 is null.

Example:

select '2'||to_char(sysdate, 'yyyymmdd')||rownum Sequence_Number,
     to_char(sysdate, 'yyyymmdd') Extraction_Date,
     substr(trim(sm.issue_name), 1, 50) Security_Full_Name,
     trim(sm.currency_code) Price_Ccy_Cde,
     trim(nra_tax_country) Market_Country_Cde,
     trim(sm.currency_code) Cash_Holding_Ccy_Cde,
     trim(decode(nvl(sm.security_type, 'N'), 'N', substr(sm.investment_type, 1,2),sm.security_type)) Investment_Type_Description,
     trim(sm.nra_tax_country) Domicile_Country_Cde,
     trim(smd.country_of_risk) Risk_Country_Cde,
     decode(nvl(to_char(fi.issue_date), 'N'), 'N', '00000000',  to_char(fi.issue_date, 'yyyymmdd')) Issue_Dte,
     decode(nvl(to_char(sm.mat_date), 'N'), 'N', '00000000',  to_char(sm.mat_date, 'yyyymmdd')) Final_Maturity_Dte,
     null Market_Vendor_Rating_S_P,
     null Market_Vendor_Rating_Moody,
       decode(trim(sm.primary_asset_id_type), 'CUSIP',trim(sm.primary_asset_id),
                                      'ISIN' ,trim(sm.primary_asset_id),
                     NULL) Pref_Ext_Security_Number,
     decode(trim(sm.primary_asset_id_type), 'CUSIP',trim(sm.primary_asset_id_type),
                                      'ISIN' ,trim(sm.primary_asset_id_Type),
                     NULL) External_market_Id,
     sm.security_alias Internal_Security_Number,
     null EagleFund,
     d.underlying_sec_alias Underlying_Security_No,
     d.contract_size Contract_Size,
     substr(upper(sme.industry_sector), 1,3) Sector_Cde
from securitydbo.security_master sm,
     securitydbo.secmaster_detail_ext sme,
     securitydbo.security_master_detail smd,
     securitydbo.fixed_income fi,
     securitydbo.derivatives d
 where sm.security_alias = sme.security_alias(+)
   and sm.security_alias = smd.security_alias(+)
   and sm.security_alias = d.security_alias(+)
   and sm.security_alias = fi.security_alias(+)
   and trim(sm.update_date) >= trim(sysdate - 1)

Detecting and removing duplicates

select distinct ...

select unique_id
from mytab t1
where rowid > (select min(rowid) from mytab t2 where t1.key1 = t2.key1);

delete from mytab t1
where t1.rowid > any (select t2.rowid from mytab t2 where t1.key1=t2.key1);

delete from mytab where rowid NOT IN (select max(rowid) from mytab group by key1);

delete from mytab a
where a.rowid > ANY
  (SELECT b.rowid FROM mytab b
    WHERE (a.col1 = b.col1 OR (a.col1 is null AND b.col1 is null))
        AND (a.col2 = b.col2 OR (a.col2 is null AND b.col2 is null))
  );

delete from customer
where rowid in
  (select rowid from
    (select rowid, row_number()
     over (partition by custnbr order by custnbr) dup
     from customer)
  where dup > 1);

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

outer join - old and new syntax

select a.name, nvl(b.title,'--') title
from authors a, books b
where a.a_id = b.a_id(+);

select a.name, nvl(b.title,'--') title
from authors a
left outer join books b on a.a_id = b.a_id;

select a.name, nvl(b.title,'--') title
from authors a
left outer join books b using (a_id);

VARCHAR2 - 1..4000 bytes
VARCHAR - deprecated
CHAR(size) - fixed length, 1..2000 bytes
NCHAR - fixed length, national char. set, 1..2000 bytes
NUMBER - floating-point number with decimal precision 38
NUMBER(p,s) (p in 1..38, s in -84..127)
NUMBER(p,0) = INTEGER
LONG - depricated, use BLOB instead of it.
DATE - 1 ssec presicion
TIMESTAMP(fractional_seconds_precision) - precision may be from 0 to 9, default = 6.


CLOB, NCLOB,BLOB, BFILE, XMLType - large objectw, up to 4 GB
ROWID - phys.address of each row
UROWID(n) - logical address (n - the size of the UROWID column: 1..4000)

INTEGER - ANSI, same as NUMBER(38)
FLOAT(p) - ANSI - similar to NUMBER, but p is binary precision

Example: the value 7,456,123.89 will display as follows
NUMBER(9) 7456124
NUMBER(9,1) 7456123.9
NUMBER(*,1) 7456123.9
NUMBER(9,2) 7456123.89
NUMBER(6) [not accepted exceeds precision]
NUMBER(7,-2) 7456100
NUMBER 7456123.89
FLOAT 7456123.89
FLOAT(12) 7456000.0
Oracle stores all numeric data in variable length format.

INTERVAL YEAR[(p)] TO MONTH - stores a period of time represented as years and months. p is the number of digits in the YEAR datetime field (can be 0..9, default = 2).

INTERVAL DAY[(p1)] TO SECOND[(p2)] - stores a period of time represented as days, hours, minutes and seconds with a fractional part.

 

Output from the script

First method using dbms_output.put_line('...'). For example:

set serveroutput on;
dbms_output.put_line('some text');

The problem - it is buffered - and will make output only at the very end.
Another way - output into a file using utl_file.put_line(). Here is how you can call sqlplus from your shell script:

sqlplus -s / <<-EOF
set echo off
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
DECLARE
fichero utl_file.file_type;
BEGIN
fichero:=utl_file.fopen('/tmp','hot_backup.cmd','W');
utl_file.put_line(fichero,'LINE 1');
utl_file.put_line(fichero,'LINE 2');
utl_file.fclose(fichero);
END;
/
EOF

The best way - using autonomous transaction. An autonomous transaction can be committed even if the parent transaction that triggered it is rolled back, hence the term "autonomous". The autonomous transaction has no link to the calling transaction, so only commited data can be shared by both transactions. These are useful for auditing purposes, where even if an attempted parent transaction fails, a child autonomous transaction will still record the parent transaction, and mark it as failed, aborted, cancelled and so on. Example:

procedure myprint (mes mylog.message%type)
is
pragma autonomous_transaction;
begin
insert into mylog (dt,message) values (to_char(sysdate, 'yyyymmdd'), mes);
commit;
end myprint;

Note: the a.t. method is better than using dbms_output.put_line('...'), because put_line() is buffered until the enclosing transaction is ended.

The following types of PL/SQL blocks can be defined as autonomous transactions:
- Stored procedures and functions.
- Local procedures and functions defined in a PL/SQL declaration block.
- Packaged procedures and functions.
- Type methods.
- Top-level anonymous blocks.

xxxx