Month: June 2010

Install latest version of Oracle ODBC

To install latest version of Oracle ODBC: Go to http://www.oracle.com/technology/tech/oci/instantclient/ to download: Instant Client Package – Basic Instant Client Package – ODBC Unzip both of them into the same folder (e.g. c:\oracle). Add that folder name into the PATH environment

Posted in Installs, Windows

O’Reilly’s Steve Feuerstein Oracle PL/SQL Programming

O’Reilly’s Steve Feuerstein Oracle PL/SQL Programming, 2nd Edition online at http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/

Posted in Book review, PL/SQL

Plugins for WordPress Code Display

This plugin is pretty much essential to get wordpress to display code: Syntax Highlighter and Code Prettifier Plugin for WordPress The other plugins I use are: wp-table Google XML Sitemaps Akismet

Posted in Wordpress

chr(163) for £ pound character

Use chr(163) for the ‘£’ character, especially in stored SQL such as create procedure scripts, where you can’t be sure what client character settings might be used to re-create the procedure. SELECT m.description, chr(163)||TO_CHAR(m.weekly_rate, ‘999.99’) amount from my_table m; This

Posted in Character sets

chr(13) chr(10) carriage return line feed CR LF

chr(13) and chr(10) are the carriage return line feed CR LF characters, so to strip them out: select replace(replace(m.long_notes,chr(10),’ ‘),chr(13),’ ‘) from mytable m;

Posted in Character sets

sqltrpt readable formatting

Output of sqltrpt is more readable with these settings: set pages 9999 lines 192 col plan_plus_exp form a132 @?/rdbms/admin/sqltrpt

Posted in Performance tuning

Setting bind variables in sql*plus

variable b1 varchar2(6) ; exec :b1 := ‘2009’ Used mainly for sql tuning rather than in real code.

Posted in Performance tuning

SQL profile drop

If you have accepted a sql profile from a tuning advisor recommandation like: execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_239607’, replace => TRUE); Then the regression commands to back it out are like: select name from dba_sql_profiles; exec dbms_sqltune.drop_sql_profile(‘MYNAME’)

Posted in Performance tuning

Change oracle password temporarily

Want to connect as a particular user but don’t know their password? You can temporarily change it and then reset it back, if you have DBA/alter any user privilege: set pages 9999 lines 132 long 9999 column col1 form a132

Posted in Uncategorized