Andrew Fraser DBA

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 variable (right click my computer, manage, system properties, advanced tab, environment variables)
• Create a new environment variable called TNS_ADMIN and set it to that folder name.
• Create a file tnsnames.ora in that folder with your target database details.
• Run odb_install.exe

You can then add and test oracle odbc connections in Control Panel Administrative Tools.

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

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

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 plsql gives the full list of ASCII codes:

set serveroutput on size 10240
declare
   i number;
   j number;
   k number;
begin
   for i in 2..15 loop
       for j in 1..16 loop
           k:=i*16+j;
           dbms_output.put((to_char(k,'000'))||':'||chr(k)||'  ');
           if k mod 8 = 0 then
              dbms_output.put_line(' ');
           end if;
       end loop;
   end loop;
end;
/

(From http://www.orafaq.com/wiki/ASCII )

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;

Output of sqltrpt is more readable with these settings:

set pages 9999 lines 192
col plan_plus_exp form a132
@?/rdbms/admin/sqltrpt

variable b1 varchar2(6) ;
exec :b1 := '2009'

Used mainly for sql tuning rather than in real code.

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')

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:

SQL> select password from dba_users where username = 'MYUSER';

PASSWORD
------------------------------
086FAA387C794B46

SQL> alter user MYUSER identified by mypassword ;

User altered.

SQL> conn MYUSER/mypassword
Connected.
SQL> alter user MYUSER identified by values '086FAA387C794B46';

User altered.

SQL> sho user
USER is "MYUSER"

The above works for versions up to 10g. For 11g, instead use:

SQL> set long 9999
SQL> select dbms_metadata.get_ddl ('USER', 'MYUSER') from dual ;

Source: Laurent Schneider.