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.

Popup LOV boxes that retrieve their list from a database table are relatively easy to set up in oracle forms, the equivalent for a dropdown list item takes a bit more work.

1) First, create a new item in the canvas with item type as ‘List Item’ – or, in my case I changed the item type of an existing ‘text item’ called CONTROL.CAS_UPLOAD to be a ‘list item’.

2) Second, create a procedure like the below. This is a procedure within the form, listed under program units, not a database stored procedure.

The second column selected here is the one that is actually passed into the form control, even although slightly confusingly the box will display the first column value after selection is made. Only the first column is shown in the drop down.

CONTROL.CAS_UPLOAD here is the name of my list item (cas_upload), qualified by block name (control).

PROCEDURE CREATE_Filenames_RG  IS
-- Andrew Fraser v2.2 27th May 2010
-- Populate dynamic lookup

    it_id1   Item := Find_Item('CONTROL.CAS_UPLOAD');  

    group1_id    RecordGroup;

    GRP_status       NUMBER; 

    V_Space varchar2(10) := ''''||'0'||'''';

BEGIN

  group1_id := Find_Group('FILENAMES_RG');  

  IF NOT Id_Null(group1_id) THEN
  	delete_group(group1_id);
  END IF;
  group1_id := Create_Group_From_Query('FILENAMES_RG',
        'SELECT TO_CHAR(f.date_created, ''DD-Mon-YYYY HH24:MI'')||'||''''||' -- '||''''||'||f.file_name , f.file_name '
     ||' FROM filename_table f'
     ||' WHERE f.flag_processed = ''N'''
     ||' ORDER BY f.date_created, f.file_name') ; 

    Grp_status := Populate_Group('FILENAMES_RG');
  	IF Grp_status = 0 THEN
			If Not Id_Null(it_id1) THEN
		  	If Get_Item_Property(it_id1,Item_Type) = 'LIST' Then
	  	 	  Clear_List(it_id1);
	   		  Populate_List(it_id1,'FILENAMES_RG');
  		  END IF;
			END IF;
			END IF;

End;

3) Create/edit triggers to call this:

On form startup:

When-New-Form-Instance on the form as a whole:

-- Andrew Fraser v2.2 27-May-2010
CREATE_Filenames_RG;

And/or on mouse click if you want the database table requeried every single time the user clicks on the dropdown:

When-Mouse-Click on the new item itself:

-- Andrew Fraser v2.2 27-May-2010
Clear_List('CAS_UPLOAD');
Clear_Item;
CREATE_Filenames_RG;

4) Note that hard-coded lists get created with a record group and an associated LOV. Counter-intuitively, these are not used at all in the above method for database lookups.