Oracle FAQ

Topics:
SQL*Loader/sqlldr/SQL Loader
SQL*Plus/sqlplus/SQL Plus
PL/SQL Coding Specific
Administrative/Operations
Performance/Tuning
Replication


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQL*Loader/sqlldr/SQL Loader

Q: Can you automatically generate skeletal Sqlldr control files??
A: I wish.

Easiest way to create them is to take output of a desc file of target table:
- put "LOAD DATA INTO table tgt_table (" at top of file
- :.,$ s/(..*)// this gets rid of the (10) in varchar2(10)
- :.,$ s/VARCHAR2/char terminated by '\~'/
- :.,$ s/NUMBER/integer external terminated by '\~'/
- :.,$ s/DATE/date "DD-MON-YY" terminated by '\~'/
- :.,$ s/$/,/  (put a comma at the end of all lines if not there already)
- clean the "terminated by" clause from the last column, close w/ ")"

and then run the command
sqlldr user@sid/password data=datafile control=ctlfile log=logfile
(datafile defaults to the ctlfile name)

---

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQL*Plus/sqlplus/SQL Plus

---

Q: How do you log into Oracle from the command line? 
A: sqlplus user@sid/password

- If TNS cannot resolve the sid, you'll have to do the login manually/
interactively.  Either way, the password is seen in cleartext in the process
table (ps -fe | grep sqlplus)

---
Q: How do you secure the database password when logging in?
A: ??

---

Q: I want to get some data out of Oracle in a text, delimited format.
A: In a vi window:

- In your sql, you'll have to encapsulate each column with || 'sep' ||
where sep is your seperator (a tilde ~ or comma perhaps).  Easiest way
is to list all your columns (get output of a desc table and run:
:1,$ s/^ //
:1,$ s/  *..*$//
this leaves one column name per line.  Now hit a couple of sed commands in vi:
:1,$ s/^/|| /g
:1,$ s/$/ || '\~'/g

Clean up the first and last lines of your list, then insert the select and from 
clauses to be what you wish (make sure there's no blank line between the select
and the first column name!).  You should have sql that resembles:

select 
COL_A || '~'
|| COL_B || '~'
...
|| COL_N
from table;

- Add these lines to the top:
set linesize 1000
set pagesize 0
set feedback off
set trimspool on
set termout off

- Add these lines to the bottom:
-- Restore default settings
set linesize 80
set pagesize 24
set feedback on
set termout on

- cut-n-paste your target sql to the sql*plus window
- file -> spool -> spool file and specify an output file 
OR
in sql*plus: 
SQL> spool c:\dir\filename
- execute your query
- file -> spool -> spool off 
OR
SQL> spool off

and then get your output file.  More frequently, Oracle admins will write
small extraction scripts that you can call from sql*plus.  They automate
this entire sequence rather nicely.

---
Q: How do i prevent output to the screen when spooling?
A: ?? thought it was set termout off, but not working

---
Q: how do you recall the previous command?
A: Edit, which brings up an editor (notepad on PCs)

---
Q: How do you specify what editor "edit" brings up?
A: ???

---
Q: how do you clear the buffer?
A: del.  del # for a line number, del x y to delete from x to y.

----
Q: How do you pre-set configuration variables for your Oracle session?
A: login.sql

---
Q: How do you insert an "ampersand" into a string in oracle?
A: several methods; easiest is:
- in sqlplus, set def off before attempting


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
PL/SQL Coding Specific

----
Q: How do you run scripts from command line?
A: ?

---
Q: how can you limit the number of rows being returned?  Ala set rowcount x?
A: you can't.  Best thing to do is set pagesize 24 and set pause on
before selecting data.  This pages through result set and allows you to
hit ctrl-C when you have seen enough.  OR find a where clause that limits
the rows coming back.  No equivalent to "set rowcount xx" in sybase.

Update: SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11;

---
Q: how do get a list of objects?  Tables, procs, triggers?
A: 
select table_name from user_tables;
select view_name from user_views;
?? for procs
select sequence_name from user_sequences;

---
Q: How do you get a list of columns per table (ala sp_help or
select * from table where 1=2 in Sybase/MS-Sql server)?
A:  select column_name from user_tab_columns where table_name='TABLE';

---
Q: How do you work with Sequences?
A: Create them as such
create seq_name start with X increment by 1;
insert into table values (seq_name.nextval, ...);

To get current value of a sequence: select seq_name.currval from dual;

However; a sequence must be "current" in the session in order to be
able to select from it.  That means, you must have "initialized" the
sequence in the session in question; which means, you must have used
the seq_name.nextval value.

---
Q: How do you assign a sequence number to a variable in oracle?
A: select seq_name.nextval into varname from dual;
(where of course varname is previously declared)

---
Q: How do you populate a sequence with a particular number?
A: You can't: you must drop it and recreate it:
drop sequence seq_name;
create sequence seq_name start with N;

---

Q: How can I get a list of all my sequences and their current values?
A: select sequence_name, last_number from user_sequences;



---
Q: how do I debug SQL that i've written that isn't compiling correctly?
A: after running, do 
SQL> show errors; 
and you'll get line numbers (approximately) of problem lines.  The line count
given will not include any part of the "create procedure/trigger" statement
if you're calling the SQL from such.

---
Q: You apparently cannot do a join in an update statement clause.  How
do you then recode an update statement?
A: an in clause.  Thus
update tableA set col=x 
from tableA, tableB
where tableA.id = TableB.id and TableB.id = "123"

becomes

update tableA set col=x where id in 
(select tableA.id from tableA, tableB where 
tableA.id = TableB.id and TableB.id = "123")

Another example

update tableA set a.col1 = b.col1, a.col2 = b.col2
from tableA a, tableB b
where tableA.id = tableB.id and TableB.id = '123'

becomes

update tableA 
set (col1, col2) = (select col1, col2 from TableB where id = '123')
where id = '123'

However, you cannot reference :new or :old in this subquery.  You'll have
to assign the targeted column to a variable instead (if you're using triggers)


---
Q: How do you concatenate two fields or two strings in Oracle?
A: ||.  eg. select 'aaa' || 'bbb' from dual; returns "aaabbb"

---
Q: How do you assign a variable in a procedure/trigger?
A: := sign.  However, you cannot do:
new_var := select var from table where col1 = x;

instead:
select var into new_var from table where col1 = x;

---
Q: How do you work with functions?  
A: ???

---
Q: How do you create and use temp tables?
A: No such concept in Oracle as a tempdb or a #table.

---
Q: How do you work with long variables doing insert into select from?
A: you can't manipulate long variables this way.  You have to use a 
third party app and bind the long variable to a local var to do the insertion

---



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Administrative/Operations

---
Q: What is the syntax for adding a primary key to an already-created table?
A: (question asked b/c syntax apparently has changed recently)

old: alter table test modify (col_1 constraint test_cst primary key);
new: alter table test add constraint test_cst primary key (col_1);

and then, alter table test enable constraint test_cst;
(though constraints seem to default to "enabled")

---
Q: what is a key preserved table?  I couldn't update through a 
view b/c one of the tables wasn't 'key preserved.'
A: ?

---
Q: why are tables that i've dropped still existant in the Toad Schema
browser?
A: ?  commit issue?  clear connection?  don't know

---
Q: How do you add a new server to your client list?
A: Net Assistant 8, which updates the tnsnames.ora file buried in
the $ORACLE_HOME/Network/Admin directory.  This is aking to modifying
the interfaces file (or sql.ini) for Sybase (perhaps using sybinit)

---
Q: How do you view the text of a stored procedure?
A: ?

---
Q: Where is the server's error log?  Where are System messages written to?
A: ??

--- 
Q: How can you tell on an Oracle box when the server is up and running?
A: ??? (the existance of the spid file?)

---
Q: Do private Synonyms override Public Synonyms?
A: Yes.  When an operation is done on a table, Oracle tries to find the table
in this order:  (from Chapter 21 of Oracle 8i Concepts reference manual)
1. searching in the current schema
2. searching for a private synonym in the same schema. 
3. searching for a public synonym. 
4. searches for a schema name that matches the first portion of the object name. 
If a matching schema name is found, Oracle attempts to find the object in that
schema. 
5. If no schema is found, an error is returned. 

---
Q: Do table constraints create indexes (as in Sybase and Microsoft SQL Server)?
A: No.  they merely create "rules" of behavior for the table.  An additional
create index statement must be issued.

---


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Performance/Tuning

---
Q: How do you tell which query plan, which index is getting used?
A: ?

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Replication
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-