Re: Executing Distributed Queries
From: Umachandar Jayachandran (umachandar@yahoo.com)
Date: 01/02/03
- Next message: Stockton P.: "stored proc exec and insert permissions"
- Previous message: Leon Parker: "Executing Distributed Queries"
- In reply to: Leon Parker: "Executing Distributed Queries"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Umachandar Jayachandran" <umachandar@yahoo.com> Date: Thu, 2 Jan 2003 13:31:40 -0800
Remote SP execution is supported only with SQL Server provider. You have
to use OPENQUERY to send the pass-through command but your SP on the remote
server should produce a result set. I have posted several examples in the
programming, odbc & server newsgroups that show how to do this with Oracle
packages & SPs. You can search for more samples in these newsgroups. I am
posting one such message here.
>>
You have to wrap the SP call in a package that returns a table variable
as output. SO all you need is a dummy row to be returned from the package &
then you can call it using OPENQUERY using a pass-through query. I posted a
sample while back about executing DDLs in Oracle via T-SQL. Your call will
look like:
SELECT r.DummyOut
FROM OPENQUERY( Oracle8i,
'{Call SomePkg.SP( {resultset dummyout, 1} )}' ) AS r
>>
>>
You can't execute DDLs directly on remote data sources. These are not
supported by all data sources - remember that a linked server can be ADSI or
Excel.
It is slightly tricky but you can do it. With OPENQUERY, you can execute
any pass-through statement in Oracle that returns a resultset. The key is
that the pass-through statement should return some results. This is however
difficult to accomplish in Oracle because you can't mix DDLs & statements
that return results directly in SPs (unlike in T-SQL). You can however call
a package that can return a result via table variables. So you can create a
package like below that can truncate a table:
CREATE OR REPLACE PACKAGE trunctbl
AS
TYPE dummyout is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
PROCEDURE trunctbl( dummy OUT dummy );
END;
/
CREATE OR REPLACE PACKAGE BODY trunctbl
AS
PROCEDURE trunctbl ( dummy OUT dummy )
IS
curhandle NUMBER;
BEGIN
curhandle := dbms_sql.open_cursor;
dbms_sql.parse( curhandle, 'TRUNCATE TABLE owner.testtab',
dbms_sql.native );
dbms_sql.close_cursor(curhandle);
dummy( 1 ) := 0;
EXCEPTION
WHEN OTHERS THEN
dummy( 1 ) := 1;
dbms_sql.close_cursor(curhandle);
END;
END trunctbl;
/
The package above uses the "DBMS_SQL" package to execute dynamic SQL in
Oracle. The dummy table is how you return data back to OLEDB or ODBC driver.
You can process the result using the {resultset} escape syntax. So you can
now do:
select *
from openquery( Oracle8i,
'{call trunctbl.trunctbl( {resultset 1, dummy} )}' )
This will return 1 or 0.
>>
-- Umachandar Jayachandran SQL Resources at http://www.umachandar.com/resources.htm ( Please reply only to newsgroup. )
- Next message: Stockton P.: "stored proc exec and insert permissions"
- Previous message: Leon Parker: "Executing Distributed Queries"
- In reply to: Leon Parker: "Executing Distributed Queries"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
