1. Generate sequences for each record
2. Update with a join
3. Grant privileges
4. Select record into a new table
5. Special chars in Oracle
6. Lost SYS Password
7. Pipelined table function - select from a function
8. Useful SqlPlus settings, they are useful to combine from sqlplus with shell scripts, for convenient input/outputs.
1. Generate sequences for each record
SELECT 'CNT' || Trim(To_Char(dense_rank() over (ORDER BY T.BLOCKTRDREF), '00000')) CNT
2. Update with a join
update (select bonus
from employee_bonus b
inner join employees e
on b.employee_id = e.employee_id
where e.bonus_eligible = 'N'
) t
set t.bonus = 0UPDATE SYNAPP.TRADEREP_TAB T
SET (T.SETTMARKET, T.SETTBATCHZON) = (
SELECT E.SETTMKTCONV.UNIQUEID, E.settMktConv.WorkZone.batchzone.UNIQUEID
FROM SYNAPP.EQUTI_TAB E
WHERE T.PRIMINSTRREF = E.UNIQUEID
AND T.SETTMARKET IS NULL
AND T.TRADEDATE >= '1 Nov 2012'
--AND T.TRADEREF = '0003131012'
)
;
3. Grant privileges
grant create view to syndev;grant create materialized view to syndev;4. Select record into a new table
CREATE TABLE SYNDEV.ADDWRAP_TAB_V_TMP ASSELECT OOID, OWNEROOID, ORGOOID, OWNERTYPE, ORGNAME, TRADINGACC, ISLETCONF, LETOFFICE, LETORDERTYPE, LETPRODTYPE, LETPCCC FROM SYNDEVGMA1.ADDWRAP_TAB_V;5. Special chars in Oracle
& -- char(38) ' -- ''sa_id1 = (sa_id == null)? '' : replace(replace(trim(sa_id), "'", "''"), "&", "'||chr(38)||'");6. Lost SYS Password
$ sqlplus "/ as sysdbaSQL> show userUSER is "SYS"SQL> passw systemSQL> quitNext, we need to change the password of SYS:$ sqlplus systemSQL> passw sysSQL> quit7. Pipelined table function
Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data -- instead of a table -- you would use a pipelined function.
http://www.akadia.com/services/ora_pipe_functions.html
create function gen_numbers(n in number default null) return array PIPELINED as begin for i in 1 .. nvl(n,999999999) loop pipe row(i); end loop; return; end;/select * from TABLE(gen_numbers(3));
COLUMN_VALUE ------------ 1 2 38. Useful SqlPlus settings, they are useful to combine from sqlplus with shell scripts, for convenient input/outputs.
SET HEADING OFF --- do not return headersSET DEFINE ON --- enable variable replacement (see &1 blow), shell can pass a variable to your query.SET VERIFY OFF --- disable the variable replacement outputWHENEVER SQLERROR EXIT SQL.SQLCODE --- always stop if SQL error occurs, so shell can catch the error. SELECT TO_CHAR(T.ASATDATE , 'YYYYMMDD')FROM SYNAPP.WORKZONE_TAB TWHERE T.UNIQUEID = '&1';EXIT;