ODBC Pass-Through Queries

[BlockNinja]

Is there a way to pass parameters to a server via ODBC Pass-Through queries
in Access?

Example:
Doing a "select * from ThisTable" on a certain table in Oracle 10g at work
(or linking to it via Access) produces errors when viewing the table. Namely
the error in question is "Insufficient parallel query slaves available".
This can be easily fixed in a native Oracle session by issuing the following
commands:

ALTER SESSION SET PARALLEL_MIN_PERCENT=0;
SELECT * FROM ThisTable;

This changes the session so that if there aren't enough parallel query
slaves available to Oracle, the query will still run, which is the
recommended default by Oracle.

By default though, the PARALLEL_MIN_PERCENT session parameter is not set to
zero on ODBC connections to Oracle (at least on my installation), so Access
will fail miserably when trying to view the table through the Oracle ODBC
Connection. I would like to pass this session parameter to the server, but
cannot run two queries inside of the same Pass-Through query. Any ideas?

 

[Wolfgang Kais]

Hello "BlockNinja".

Is there a way to pass parameters to a server via ODBC Pass-Through
queries in Access?

Example:
Doing a "select * from ThisTable" on a certain table in Oracle 10g
at work (or linking to it via Access) produces errors when viewing
the table. Namely the error in question is "Insufficient parallel
query slaves available".
This can be easily fixed in a native Oracle session by issuing the
following commands:

ALTER SESSION SET PARALLEL_MIN_PERCENT=0;
SELECT * FROM ThisTable;

This changes the session so that if there aren't enough parallel
query slaves available to Oracle, the query will still run, which
is the recommended default by Oracle.

By default though, the PARALLEL_MIN_PERCENT session parameter is
not set to zero on ODBC connections to Oracle (at least on my
installation), so Access will fail miserably when trying to view
the table through the Oracle ODBC Connection. I would like to pass
this session parameter to the server, but cannot run two queries
inside of the same Pass-Through query. Any ideas?

I haven't an Orcle server to play with, but:
Does the ALTER SESSION command produce a result? If not, there should
be no problem executing both commands in one pass through query. That
is because the odbc driver does not check the query text but passes
it directly to the server.
Also worth a try: check the settings in the dsn in the odbc32 applet
in the administrative tools folder on your pc. Maybe you can define
this session setting there. If it's there but you don't want to set
the value for all connections, create an additional dsn using this
setting.

 

[BlockNinja]

Unfortunately when I try to do both commands in the same query Oracle will
spit back and Invalid character error. When doing of them individually each
will execute. I looked into odbcad32 for the DSN I had for the connection
and it didn't give me a place to set any session-specific settings.