SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
#### This is the Gateway piece
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = eetest)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)
#### End of Gateway
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
HS_FDS_CONNECT_INFO = eetest
HS_FDS_TRACE_LEVEL = off
HS_FDS_SUPPORT_STATISTICS=FALSE
CREATE DATABASE LINK eetest_link CONNECT TO "user" IDENTIFIED BY "password" USING 'eetest';
eetest=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))
(CONNECT_DATA= (SID=eetest))
(HS=OK)
)
SQL> SELECT "CustomerID", "CompanyName", "ContactName"
2 FROM customers@eetest_link
3 WHERE ROWNUM < 10;
CustomerID CompanyName
ContactName
------------------------------ ---------------------------------- ---------------------------------
ALFKI Alfreds Futterkiste Maria Anders
ANATR Ana Trujillo Emparedados y helados Ana Trujillo
ANTON Antonio Moreno Taquería Antonio Moreno
AROUT Around the Horn Thomas Hardy
BERGS Berglunds snabbköp Christina Berglund
BLAUS Blauer See Delikatessen Hanna Moos
BLONP Blondel père et fils Frédérique Citeaux
BOLID Bólido Comidas preparadas Martín Sommer
BONAP Bon app' Laurence Lebihan
9 rows selected.
SQL>
[Error] Execution (4: 1): ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []
DECLARE
v_cursor BINARY_INTEGER;
v_id NUMBER;
v_firstname VARCHAR2(20);
v_lastname VARCHAR2(20);
v_temp VARCHAR2(250); -- the Access Text functions support up to 255 characters if needed
v_notes VARCHAR2(1000);
BEGIN
v_cursor := DBMS_HS_PASSTHROUGH.open_cursor@eetest_link;
DBMS_HS_PASSTHROUGH.parse@eetest_link(
v_cursor,
'select employeeid, firstname, lastname , left(notes,250) , mid(notes,251,250),
mid(notes,501,250),mid(notes,751,250) from employees order by employeeid'
);
WHILE DBMS_HS_PASSTHROUGH.fetch_row@eetest_link(v_cursor) > 0
LOOP
DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 1, v_id);
DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 2, v_firstname);
DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 3, v_lastname);
v_notes := NULL;
FOR i IN 4 .. 7
LOOP
DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, i, v_temp);
v_notes := v_notes || v_temp;
END LOOP;
DBMS_OUTPUT.put_line(
v_id || ' ' || RPAD(v_firstname, 10) || RPAD(v_lastname, 10) || ' : ' || v_notes
);
END LOOP;
DBMS_HS_PASSTHROUGH.close_cursor@eetest_link(v_cursor);
END;
/
1 Nancy Davolio : Education includes a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.
2 Andrew Fuller : Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.
3 Janet Leverling : Janet has a BS degree in chemistry from Boston College (1984). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.
4 Margaret Peacock : Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966). She was assigned to the London office temporarily from July through November 1992.
5 Steven Buchanan : Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976. Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London. He was promoted to sales manager in March 1993. Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management." He is fluent in French.
6 Michael Suyama : Michael is a graduate of Sussex University (MA, economics, 1983) and the University of California at Los Angeles (MBA, marketing, 1986). He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional." He is fluent in Japanese and can read and write French, Portuguese, and Spanish.
7 Robert King : Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company. After completing a course entitled "Selling in Europe," he was transferred to the London office in March 1993.
8 Laura Callahan : Laura received a BA in psychology from the University of Washington. She has also completed a course in business French. She reads and writes French.
9 Anne Dodsworth : Anne has a BA degree in English from St. Lawrence College. She is fluent in French and German.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
as oracle docs say that procedures are not supported, in fact pl/sql is not supported.. but does all the SQL functions are supported..?
for example can i use pipe lined functions/ranks/partition statements in the select query in oracle to get it retreived from the gateway..
will this query work?
where in level (pseudo column of oracle), sysdate being oracle function, and sqlserverfunction in oracle statement...
select level,sysdate,col1,SQLServ
Author
Commented:First, you can't use LEVEL without a CONNECT BY clause.
ORA-01788: CONNECT BY clause required in this query block
Second, you can't use CONNECT BY through the gateway, you would get
ORA-02070: database GATEWAYDBLINK does not support a connect by clause in this context
Third, remote objects invoked across a dblink must include the @link syntax on them, otherwise the function would be resolved as if it were a local object
Fourth, no you can't invoke remote functions through sql via a gateway link, even if you do include the @link syntax.
You might or might not be able to invoke the functions with a pass-through query. That would depend mostly on the remote db and the odbc drivers you're using and not so much anything in Oracle itself.
Commented:
this is a nice article.. Thanks for the article sdstuber..!!!
Author
Commented:If you need features that aren't available through ODBC Generic connectivity then you may need to invest in the separately licensed native gateways.
The ODBC Gateway is great for its versatility and that there is no additional cost for it; but it is the lowest rung of functionality.
I'm glad you enjoyed the article, thank you for reading.