Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |