Selectorweb.com New York
home > Oracle Email

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

* Oracle Bulk Operations
*
*
*



 
Intro home - top of the page - email

* technet.oracle.com/ - Oracle Technology Network
* http://oradoc.photo.net/ - Oracle Documentation on photo.net
* education.oracle.com/profdevpaths/ - The Oracle Career Development Paths
* 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/certification/sampleq.html -Oracle Certification Sample Tests
??* merchant.superlibrary.com:8000/products/15716/1571690484/1571690484s.html - Oracle How-To
* www.orafaq.org/faqmain.htm - FAQ
* http://www.orafaq.net/ - FAQ, Scripts, whole world
* http://www.orafaq.net/faqscrpt.htm - Oracle Scripts
* www.geocities.com/SiliconValley/Park/5476/oun01fi.htm - Oracle Unleashed * The online book
* www.geocities.com/SiliconValley/Park/5476/oun17fi.htm - Oracle Unleashed* Designing a Database
* www.adelante.u-net.com/frpage4.htm - Oracle DBA Scripts and tips
* www.oriolecorp.com/index1.html -
* www.oriolecorp.com/index1.html - Free Oracle SQL scripts and DBA tools
* www.cs.ndsu.nodak.edu/~perrizo/classes/index.html - William Perrizo NDSU Faculty
??* www.atm.cs.ndsu.nodak.edu/atmgroup/cs765f98papers.html- DB intro

* dacnet.rice.edu/DAC-Net/References/DB/ - SQL links
??* www.geocities.com/ResearchTriangle/Node/9672/sqltut.html - SQL tutorial
??* www.geocities.com/ResearchTriangle/Node/9672/sqltut.html - SQL Intro + links

* info-it.umsystem.edu/oracle/svslr/function.html - Oracle DBMS Functions
* www.irm.vt.edu/oracle_803_docs/DOC/server803/A54647_01/toc.htm - Oracle8 Server SQL Reference
??* databases.miningco.com/msub8.htm?pid=2827&cob=home - Oracle links

* www.revealnet.com/ - RevealNet, Oracle information portal.
* www.dbasupport.com/cert/ - Oracle Certification training
* www.dbasupport.com/dbres/Technical_Docs/Tutorials/ - tutorials
* www.oraclenotes.com/ - Study Oracle Notes and Forums
* directory.google.com/Top/Computers/Software/Databases/Oracle/FAQs/ - Oracle FAQs on Google

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

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

* www.ozemail.com.au/~gpiper/ - Oracle FAQ.
* www.dbasupport.com/faq/ - Oracle FAQ.
* www.osborne.com/oracle/dbafaq/dbafaq1.htm - A good FAQ for administrators.
* www.oracle-dba.com/ora_scr.htm - Scripts for Oracle administration, monitoring, diagnostics and reporting.
* www.theoracleresourcestop.com/ - discussions, scripts, info, links, white papers

* www.cs.uwindsor.ca/meta-index/courses/95F/60-315/temp/sqlforms.html - SQLForms Version 3.0 Tutorial
* nova.bsuvc.bsu.edu/ucspub/ho/vax/sqlforms.html -  SQL*Forms in Oracle

* 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
* www.caribdata.co.uk/db_service/plsql/qa.home - Interactive FAQs.
* www.ioug.org/tips/index.cfm - technical tips

 
Tools home - top of the page - email

Tools For Oracle:

For your GUI clients to work, you need to install "Oracle Client" software first (to have proper DLLs).
* www.globecom.net/tora/ - TOra - Toolkit For Oracle - very good, easy, light - and free. Unix and Windows. Should be your first choice.
* www-db.stanford.edu/~ullman/fcdb/oracle/or-intro.html - sqlplus, getting starting with Oracle
* www.quest.com/sql_navigator/ - SQL Navigator from Quest Software (acquired from www.technosolutions.com)
   (probably the best client for Windows, even better than RapidSQL)
* www.oracle.com/ip/develop/ids/index.html?content.html - tools from Oracle itself
* www.toadsoft.com/ - TOAD - the Tool for Oracle Application Developers
* www.quest.com/spotlight_oracle/ - Spotlight on Oracle (formerly Instance Monitor) - graphically displays, in real time, the actual server processes and flow of data in your Oracle database so you can quickly identify congested areas and take appropriate corrective action.
* use  DBArtisan and RapidSQL from embarcadero .

 
Books home - top of the page - email

Books:

* Oracle8: The Complete Reference * George Koch, Kevin Loney
* Oracle Advanced PL/SQL Programming with CD-ROM by Scott Urman. Paperback (May 15, 2000)
* Oracle8i DBA Handbook * Kevin Loney, Marlene Theriault; Paperback
* Oracle Certified Professional Application Developer Exam Guide * Christopher Allen, et al;
* Oracle Certified Professional Financial Applications Consultant Exam Guide * Christopher Allen, Vivian Chow
* Oracle8 How-To : The Definitive Oracle8 Problem-Solver * Edward Honour, Paul Dalberth, Ari Kaplan, Atul Mehta
* Building Intelligent Databases With Oracle Pl/Sql, Triggers, and Stored Procedures (2nd Edition) * by Kevin T. Owens (Preface)

 
Hints home - top of the page - email

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(); 

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