MS Access to Oracle (Pass-Through Query) tip

Hi all, just a tip for those who are stuck using MS Access as a front-end to an Oracle db.
If you need to execute procedures and functions in Oracle from Access, a good way to do this is with a pass through query (an MS thing). Howard Rogers (HJR) actually developed the code to do this several months ago, so all the credit goes to him. I simply noticed that you can create procedures, functions, or packages (or modify parts) from within Access. If you create the proc/func/pkg on one continuous unbroken line, you'll have no problems. The benefit of creating these objects dynamically is that they can be customized depending on the circumstances (eg. in control structures). If you simply need to run a procedure then replace the following query string with the procedure name (followed by ;). Note that the whole query string is enclosed with "begin" and "end;". These are mandatory to execute it (even if your procedure is preceded by DECLARE).

Anyways, here's the code (again, thanks to HJR):

(this can be attached to the on_click event of a command button, or a form/report opening, etc.)

Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
Dim qdfLocal As QueryDef
Dim rstTopFive As Recordset
Dim strMessage As String
Dim connection_string As Long

connection_string = "ODBC;DSN=orcl.global.domain;UID=user;PWD=pass;DBQ=PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=T;MDI=F;CSR=F;FWC=F;PFC=100;TLO=0;"

Set dbsCurrent = CurrentDb
Set qdfPassThrough =
dbsCurrent.CreateQueryDef("unique_query_name_here") qdfPassThrough.Connect = connection_string qdfPassThrough.SQL = "begin declare v_id number(6); v_source_ip varchar2(20); v_ip_lookup varchar2(1000);v_whois_lookup varchar2(4000);begin execute immediate 'call dbms_output.enable(1000000)';execute immediate 'call dbms_java.set_output(1000000)';execute immediate 'truncate table Whois_Table';for x in (select id,source_ip from Websites_Visited order by total desc) loop v_source_ip := x.source_ip;v_id := x.id;select oscmd('/usr/bin/host '||v_source_ip) into v_ip_lookup from dual;select oscmd('/usr/bin/whois -h whois.arin.net '||v_source_ip) into v_whois_lookup from dual; if (instr(v_whois_lookup,'Allocated to APNIC') > 0 or instr(v_whois_lookup,'This IP address range is not registered in the ARIN database') > 0) then select oscmd('/usr/bin/whois -h whois.apnic.net '||v_source_ip)into v_whois_lookupfrom dual;end if;if instr(v_whois_lookup,'ALLOCATED UNSPECIFIED') > 0 then select oscmd('/usr/bin/whois -h whois.ripe.net '||v_source_ip)into v_whois_lookup from dual;end if;if (instr(v_whois_lookup,'This network range is not allocated to APNIC')
> 0) OR (v_whois_lookup is null) then v_whois_lookup := 'Information
Not Available';end if;insert into Whois_Table values (v_id,v_source_ip||' : '||NVL(v_ip_lookup,'No Match'),NVL(v_whois_lookup,'No Data Available'),sysdate);end loop;commit;end; end;"

qdfPassThrough.ReturnsRecords = False
DoCmd.OpenQuery "unique_query_name_here", acViewNormal, acEdit dbsCurrent.QueryDefs.Delete "unique_query_name_here" dbsCurrent.Close

note: the query string is all one long, unbroken string (it wraps automatically here).

Cheers,

Bricklen Received on Thu Nov 14 2002 - 11:18:05 CST