Use of PL/SQL %TYPE and HS - Heterogeneous Connectivity

I have been unable to get a PL/SQL package specification to compile that uses %TYPE against a table that is referenced via a HS link and physically resides in MSSQL 7.
The documentation I looked at (Oracle Transparent Gateway A82868-01 for 8.1.6 and A88789-01 for 9.0.1) does not specifically state if this functionality is supported or not. My database level is 8.1.7.1.5.
I assume that if this is supported then Oracle does some translation of data types at compile time. If it is not suppported then I can remove the use of %TYPE and type the variables using the Raw data type.
My line of code in the package spec is:
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN "rfq"."rfqnum"%TYPE);
I have also tried:
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN rfq.rfqnum%TYPE);
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN "dbo"."rfq"."rfqnum"%TYPE);
PROCEDURE RFQ_HDR_OUT
( i_rfqnum IN rfq."rfqnum"%TYPE);
The error I get is:
(1): PLS-00201: identifier 'rfq.rfqnum' must be declared
The rfq reference is actually a Oracle VIEW built on a SELECT * FROM table#HSODBC. I can successfully select data from this table using SQL*Plus.
I am also able to reference this table in my package BODY without a problem.
Any insight much appreciated.
Regards, Charles.

Charles,
Are you using Generic Connectivity or Transparent gateway for SQL Server? 

I am using Generic Connectivity. 

Charles,
We tried it here with Generic Connectivity using Oracle v9.0.1 and it works. It also works with the SQL Server gateway v9.0.1.
Although we have not tested with 8.1.7, I think it will not work.
Will update you once we have tested it. 

Charles,
We tried it here with Generic Connectivity using Oracle v9.0.1 and it works. It also works with the SQL Server gateway v9.0.1.
Although we have not tested with 8.1.7, I think it will not work.
Will update you once we have tested it. 

Charles,
We have tried this with Oracle 8.1.7 and it works. Take a look at the trace file for more information on why it is failing in your environment.

The compilation of the package does not create any trace output.
My trace settings in hs\admin\inithsodbc.ora are:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=mssql
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = hsodbc.trc
I can however successfully run a SELECT against the same VIEW that I am trying to use for %TYPE in my package. This SELECT renders the following trace:
TUESDAY MAY 14 2002 15:33:12.031
(0) hoagprd(2); Entered.
(0) [Generic Connectivity Using ODBC] version: 2.0.4.0.0010
(0) connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=4;PARSER_-
(0) DEPTH=2000;EXEC_FLAGS = 131080;defTdpName=hsodbc;binding=(hsodbc,ODBC,"mssql");
(0) ORACLE GENERIC GATEWAY Log File Started at 14-May-02 15:33:12
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = maximo.
(0) Created new ODBC connection (27792120)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid =BATT.d2ea68f8.3.1.11,
(0) hoagtbid = , tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "RFQ"
(0) odbc_rec: select * from "RFQ"
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."buyercompany" AS c00039, "T0000"."printdate" AS c00038, "T0000"."ldkey" AS c00037, "T0000"."rfq10" AS c00036, "T0000"."rfq9" AS c00035, "T0000"."rfq8" AS c00034, "T0000"."rfq7" AS c00033, "T0000"."rfq6" AS c00032, "T0000"."rfq5" AS c00031, "T0000"."rfq4" AS c00030, "T0000"."rfq3" AS c00029, "T0000"."rfq2" AS c00028, "T0000"."rfq1" AS c00027, "T0000"."historyflag" AS c00026, "T0000"."priority" AS c00025, "T0000"."changedate" AS c00024, "T0000"."changeby" AS c00023, "T0000"."paymentterms" AS c00022, "T0000"."shipvia" AS c00021, "T0000"."freightterms" AS c00020, "T0000"."fob" AS c00019, "T0000"."replytoattn" AS c00018, "T0000"."replyto" AS c00017, "T0000"."billtoattn" AS c00016, "T0000"."billto" AS c00015, "T0000"."shiptoattn" AS c00014, "T0000"."shipto" AS c00013, "T0000"."requestedby" AS c00012, "T0000"."requireddate" AS c00011, "T0000"."rfqtype" AS c00010, "T0000"."purchaseagent" AS c0009, "T0000"."closeondate" AS c0008, "T0000"."replydate" AS c0007, "T0000"."enterby" AS c0006, "T0000"."enterdate" AS c0005, "T0000"."statusdate" AS c0004, "T0000"."status" AS c0003, "T0000"."description" AS c0002, "T0000"."rfqnum" AS c0001, "T0000"."rowstamp" AS c0000 FROM "RFQ" T0000
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 932):
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "RFQ"
(0)
(0)
(0) Accessing Database "hsodbc" with SQL:
(0) SELECT "T0000"."buyercompany" AS c00039, "T0000"."printdate" AS c00038, "T0000"."ldkey" AS c00037, "T0000"."rfq10" AS c00036, "T0000"."rfq9" AS c00035, "T0000"."rfq8" AS c00034, "T0000"."rfq7" AS c00033, "T0000"."rfq6" AS c00032, "T0000"."rfq5" AS c00031, "T0000"."rfq4" AS c00030, "T0000"."rfq3" AS c00029, "T0000"."rfq2" AS c00028, "T0000"."rfq1" AS c00027, "T0000"."historyflag" AS c00026, "T0000"."priority" AS c00025, "T0000"."changedate" AS c00024, "T0000"."changeby" AS c00023, "T0000"."paymentterms" AS c00022, "T0000"."shipvia" AS c00021, "T0000"."freightterms" AS c00020, "T0000"."fob" AS c00019, "T0000"."replytoattn" AS c00018, "T0000"."replyto" AS c00017, "T0000"."billtoattn" AS c00016, "T0000"."billto" AS c00015, "T0000"."shiptoattn" AS c00014, "T0000"."shipto" AS c00013, "T0000"."requestedby" AS c00012, "T0000"."requireddate" AS c00011, "T0000"."rfqtype" AS c00010, "T0000"."purchaseagent" AS c0009, "T0000"."closeondate" AS c0008, "T0000"."replydate" AS c0007, "T0000"."enterby" AS c0006, "T0000"."enterdate" AS c0005, "T0000"."statusdate" AS c0004, "T0000"."status" AS c0003, "T0000"."description" AS c0002, "T0000"."rfqnum" AS c0001, "T0000"."rowstamp" AS c0000 FROM "RFQ" T0000
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt(195); Array fetch size is: 1.
(0) ------ hoadscr() -------:
(0) hoadamsz: 40, hoadasiz: 40, hoadambr: 1, hoadabrc: 1
(0) row 0 - hoadambl: 0, hoadadty: 0, hoadaprc: 0, hoadacst: 0
(0) row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 8, hoadanam: rowstamp, hoadabfl:
(0) 0, hoadamod: 0
(0) row 1 - hoadambl: 8, hoadadty: 108, hoadaprc: 8, hoadacst: 0
(0) row 1 - hoadascl: 0, hoadanul: 0, hoadanml: 6, hoadanam: rfqnum, hoadabfl: 8,
(0) hoadamod: 0
(0) row 2 - hoadambl: 50, hoadadty: 108, hoadaprc: 50, hoadacst: 0
(0) row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 11, hoadanam: description,
(0) hoadabfl: 50, hoadamod: 0
(0) row 3 - hoadambl: 6, hoadadty: 108, hoadaprc: 6, hoadacst: 0
(0) row 3 - hoadascl: 0, hoadanul: 0, hoadanml: 6, hoadanam: status, hoadabfl: 6,
(0) hoadamod: 0
(0) row 4 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 4 - hoadascl: 0, hoadanul: 0, hoadanml: 10, hoadanam: statusdate,
(0) hoadabfl: 7, hoadamod: 0
(0) row 5 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: enterdate, hoadabfl:
(0) apiutil; row 39 - cbDataOffset: 839, dwBinding: 1, width: 18, scale: 0
(0) hoaftch(21); Exited with retcode = 1403, hoadabrc = 0.
(0) hoaclse(22); Entered. id = 1.
(0) hoaclse(22); Exited with retcode = 0.
(0) hoadafr(23); Entered. id = 1.
(0) hoadafr(23); Exited with retcode = 0.
(0) hoacomm(11); Entered. keepinfo = FALSE, tflag = 1.
(0) hoacomm(11); Exited with retcode = 0.
Regards, Charles.

Related

Heterogenous Service - Connecting to Lotus Notes Database from oracle 10g

I am using Oracle 10g Release 2. I am trying to get the data from Lotus Notes into Oracle for the project.
1. Created ODBC DSN to Lotus Notes File System.
2. Created Listner and TNSNAMES entried. Started the Listner without any issues.
3. Created database link to using the TNSNAMES entry for Lotusnotes.
4. Able to query the system table like ALL_OBJECTS and ALL_TABLES and see the list of objects and tables in Lotus Notes.
5. When trying to query the table(other then system tabels) in lotus notes to get the data , I am getting following error.
ERROR:
ORA-02068: following severe error from LOTUSNOTES
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1524)
)(CONNECT_DATA=(SID=LOTUSNOTES)))
Please help.
Thanks
Seshadri Thope 
For a diagnosis please provide the last lines from HSODBC trece (HS_FDS_TARCE_LEVEL=SUPPORT) and fro the ODBC driver trace (if you use HSODBC on Windows open ODBC Admin and enable machine wide tracing, on Unix check out the ODBC driver docu; commonly it is enabled by setting the parameter TRACE=1 in the[ODBC] section).
Then post the relevant trace file parts. 
Here is the information from the trace logs.
Trace Log from Oracle
Oracle Corporation --- MONDAY OCT 06 2008 11:13:45.199
Heterogeneous Agent Release
10.2.0.4.0
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT A1 . "Format" FROM "AudCust" A1
(0)
(0)
(0) Accessing Database 'LOTUSNOTES' with SQL:
(0) SELECT "A1"."Format" AS c000 FROM "AudCust" "A1"
(0)
(0)
(0) hoapars (15): ; hoapars Exited with retcode = 0.
(0) hoaopen (19): ; hoaopen Entered. id = 1.
(0) hoaopen (19): ; hoaopen Exited with retcode = 0.
(0) hoadscr (16): ; hoadscr Entered. id = 1.
(0) hoastmt.c (304): ; Array fetch size is: 1.
(0) HOAUTIL.C (1249): ;-----
hoadscr() -------:
(0) HOAUTIL.C (1250): ; hoadamsz: 1, hoadasiz: 1, hoadambr: 1, hoadabrc: 1, hoadawht: 5
(0) HOAUTIL.C (1253): ; row 0 - hoadambl: 4000, hoadadty: 108, hoadaprc: 0, hoadacst: 0
(0) HOAUTIL.C (1257): ; row 0 - hoadascl: 0, hoadanul: 1, hoadanml: 9, hoadanam: A1.Format, hoadabfl: 4000, hoadamod: 0
(0) hoadscr (16): ; hoadscr Exited with retcode = 0.
(0) hoaftch (21): ; hoaftch Entered. id = 1.
(0) apiutil.c (0): ; hoaftch()::
(0) apiutil.c (0): ; Column 0 - pszName: A1.Format, size: 10001, id: 108, width: 10000, scale: 0, flags: 12.
(0) SELECT "A1"."Format" AS c000 FROM "AudCust" "A1"
(0)
Last couple of lines of ODBC Log File
HSODBCLOTUSNOTE 1604-f20 ENTER SQLBindCol
HSTMT 01EA39A8
UWORD 1
SWORD 99 <SQL_C_DEFAULT>
PTR 0x029A003C
SQLLEN 10001
SQLLEN * 0x019A6FA4
HSODBCLOTUSNOTE 1604-f20 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01EA39A8
UWORD 1
SWORD 99 <SQL_C_DEFAULT>
PTR 0x029A003C
SQLLEN 10001
SQLLEN * 0x019A6FA4 (0)
HSODBCLOTUSNOTE 1604-f20 ENTER SQLExecute
HSTMT 01EA39A8
HSODBCLOTUSNOTE 1604-f20 EXIT SQLExecute with return code 0 (SQL_SUCCESS)
HSTMT 01EA39A8
HSODBCLOTUSNOTE 1604-f20 ENTER SQLSetStmtOption
HSTMT 01EA39A8
UWORD 9 <SQL_ROWSET_SIZE>
SQLLEN 100
HSODBCLOTUSNOTE 1604-f20 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 01EA39A8
UWORD 9 <SQL_ROWSET_SIZE>
SQLLEN 100
HSODBCLOTUSNOTE 1604-f20 ENTER SQLExtendedFetch
HSTMT 01EA39A8
UWORD 1 <SQL_FETCH_NEXT>
SQLROWOFFSET 0
SQLROWSETSIZE * 0x019A6BE8
UWORD * 0x019A6EDC
Thanks
Seshadri Thope
Edited by: thopevs on Oct 6, 2008 8:24 AM 
It hangs in the middle of the fetch:
You see the gateway shows the select statement to be executed ... and the ODBC driver shows:
HSODBCLOTUSNOTE 1604-f20 ENTER SQLExtendedFetch
Not every ODBC driver supports extended fetching. Please set in your init.ora of hsodbc ($OH/hs/admin/init<hsodbc>.ora):
HS_FDS_CONNECT_PROPERTIES="disableExtendedFetch='true'"
In plain text as there are a lot of single and double quotes:
HS_FDS_CONNECT_PROPERTIES=<double quotes>disableExtendedFetch=<single quote>truesingle quote><double qoutes>
Then retry the select. 
It worked. I am able to query the tables.
Thank you very much for your help.

help reading output of MAXL query database 'xxx'.'xxx'  list existing_views

Hi All,
I need help reading the output of the following MAXL.
MAXL> query database 'XXX'.'XXX' list existing_views;
view_id view_levels view_size query_cost outline_id
----------------------------------------------------------------------------+-------------------
0 0, 0, 0, 0, 0, 0, 0 1 142325353.8242 3773077074
2 2, 0, 0, 0, 0, 0, 0 0.08007867824499 18044420.2041 3773077074
18 3, 0, 0, 1, 0, 0, 0 0.0097144909492 8413859.539121 3773077074
243 3, 0, 0, 0, 0, 0, 0 0.01200976418374 8269770.111514 3773077074
258 3, 0, 0, 1, 0, 0, 0 0.005230928739221 7629557.002285 3773077074
480 0, 0, 0, 0, 0, 0, 0 0.0439447198027 2138706.51715 3773077074
482 2, 0, 0, 0, 0, 0, 0 0.008324278579761 1296612.678469 3773077074
495 0, 0, 0, 1, 0, 0, 0 0.04091628722389 904275.4607642 3773077074
496 1, 0, 0, 1, 0, 0, 0 0.02352851896674 842245.8610811 3773077074
721 1, 0, 0, 0, 0, 0, 0 0.03284575666356 840288.1004678 3773077074
723 3, 0, 0, 0, 0, 0, 0 0.002449977563551 830169.6478759 3773077074
738 3, 0, 0, 1, 0, 0, 0 0.001255050711848 823139.9018001 3773077074
1320 0, 0, 0, 0, 0, 0, 1 0.03880273763287 821668.5881673 3773077074
1446 1, 0, 1, 0, 0, 0, 0 0.4630289820583 814281.9762218 3773077074
1573 3, 0, 2, 0, 0, 0, 1 0.01696582234862 811848.8715395 3773077074
1582 2, 0, 1, 1, 0, 0, 1 0.02174212566811 809304.8265478 3773077074
2067 2, 0, 2, 1, 0, 0, 1 0.002576454052301 196520.6619539 3773077074
4348 3, 0, 2, 1, 0, 0, 0 0.006627421114825 195901.4748527 3773077074
5083 3, 0, 2, 0, 1, 0, 0 0.00169668134083 194823.1813826 3773077074
5178 3, 0, 0, 1, 0, 0, 1 0.0009579944251709 193445.5133131 3773077074
6362 2, 0, 0, 0, 0, 0, 1 0.005180671503596 192976.6636263 3773077074
16497 2, 0, 2, 1, 1, 0, 1 0.001258153421064 129982.5153875 3773077074
16555 0, 0, 2, 1, 1, 1, 1 0.01257323769739 55243.73857212 3773077074
16556 1, 0, 2, 1, 1, 1, 1 0.004624561140274 50206.65721487 3773077074
16558 3, 0, 2, 1, 1, 1, 1 0.0005054380656583 38063.75871495 3773077074
OK/INFO - 1241044 - Records returned: [25].
What does column 2 "view_levels" signify. We have 11 dimensions and I expected column 2 be 11 values long but it has only 7. What is that? I would also like to know the meaning of the other columns too.
Any help/pointers/documentation is appreciated. Thanks for taking a look.

Heterogeneous connectivity issue with postgresql database

Dears,
I am in dire need of the solution to the below problem.
As issue I execute select * from "user_title"#TIMETREX.LOCALHOST, the system displays a message and the trace file logs the following errors.
Here is my HS tace file data:
(0) hoagprd(2); Entered.
(0) [Generic Connectivity Using ODBC] version: 2.0.4.0.0010
(0) connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=4;PARSER_-
(0) DEPTH=2000;EXEC_FLAGS =
(0) 131080;defTdpName=TIMETREX;binding=(TIMETREX,ODBC,"TIMETREX");
(0) ORACLE GENERIC GATEWAY Log File Started at 14-Sep-10 14:51:07
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = timetrex.
(0) Created new ODBC connection (27202376)
(0) Silent DB Function!!
(0) (Last message occurred 4 times)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid
(0) =ORACLE.US.ORACLE.COM.94aafae5.13.84.21, hoagtbid =
, tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "user_title"
(0) odbc_rec: select * from "user_title"
(0) Silent DB Function!!
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."deleted", "T0000"."deleted_by", "T0000"."deleted_date", "T0000"."updated_by", "T0000"."updated_date", "T0000"."created_by", "T0000"."created_date", "T0000"."name", "T0000"."company_id", "T0000"."id" FROM "user_title" T0000
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 932):
(0) SELECT "T0000"."deleted", "T0000"."deleted_by", "T0000"."deleted_date", "T0000"."updated_by", "T0000"."updated_date", "T0000"."created_by", "T0000"."created_date", "T0000"."name", "T0000"."company_id", "T0000"."id" FROM "user_title" T0000
(0)
(0) MESSAGE(10); Unregistered catalog ID in message #63992 (ID=0)
(0) Noname message
(0) DRV_QspecGetColCursor:
(0)
(0) nvRETURN (P:\Src\DRV\DRVIQSPC.C 921): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 1089): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 939): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 599): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 438): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 413): -2213
(0) nvRETURN (P:\Src\QP\QPT2SEXE.C 455): -2213
(0) (Last message occurred 2 times)
(0) nvRETURN (P:\Src\QP\QP_COMPL.C 749): -2213
(0) nvRETURN (P:\Src\QP\QP_COMPL.C 561): -2213
(0) nvRETURN (P:\Src\QP\QP_COMPL.C 231): -2213
(0) nvRETURN (P:\Src\QP\QP_COMPL.C 200): -2213
(0) nvOUT (P:\Src\UTIL\GETERROR.C 327): DRV_QspecGetColCursor:
(0) hoapars(15); Exited with retcode = 28500.
(0) hoaroll(12); Entered. tflag = 1.
(0) hoaroll(12); Exited with retcode = 0.
Would be gratful for any kind of help
Abid

Can't select char type columns using ODBC link to SQL Server 2K

I have set up a db link using hsodbc from 8.1.7 to SQL Server 2000. I can select numeric and date columns from the
SQL Server table with no problem. But any character datatype (nchar or nvchar) column name included in the query
returns an ORA-00904 ("invalid column name"). I know the column names are valid for SQL Server and can access
these columns via EXCEL using the same user id and data source that is used for the link. I don't have a clue as to why ORACLE can't "see" the character type columns. Following is the hs trace file (HS_FDS_TRACE_LEVEL = debug) generated for "select assay_name from bio_assay#sqlsrvl":
FRIDAY SEP 05 2003 10:39:47.674
(0) hoagprd(2); Entered.
(0) [Generic Connectivity Using ODBC] version: 2.0.4.0.0010
(0) connect string is:
(0) YEAR2000_POLICY=-1;CTL_DEBUG=T;CONSUMER_API=1;SESSION_BEHAVIOR_FLAGS=4;PARSER_-
(0) DEPTH=2000;EXEC_FLAGS =
(0) 131080;defTdpName=SQLSRVL;binding=(SQLSRVL,ODBC,"LIBERTY");
(0) ORACLE GENERIC GATEWAY Log File Started at 05-Sep-03 10:39:47
(0) Class version: 65
(0) hoagprd(2); Exited with retcode = 0.
(0) hoainit(3); Entered.
(0) hoainit(3); Exited with retcode = 0.
(0) hoalgon(7); Entered. name = XXXXXXXXXXX.
(0) Created new ODBC connection (28382608)
(0) Silent DB Function!!
(0) (Last message occurred 4 times)
(0) hoalgon(7); Exited with retcode = 0.
(0) hoaulcp(4); Entered.
(0) hoaulcp(4); Exited with retcode = 0.
(0) hoauldt(5); Entered.
(0) hoauldt(5); Exited with retcode = 0.
(0) hoabegn(9); Entered. formatID = 306206, hoagttid
(0) =XXXXXXXXXXXXXXXXXXXXXXXX, hoagtbid = , tflag = 0, initial = 1
(0) hoabegn(9); Exited with retcode = 0.
(0) hoapars(15); Entered. stmtType = 0, id = 1.
(0) nvOUT (P:\Src\QP\QP_SQTXT.C 55): SELECT * FROM "BIO_ASSAY"
(0) odbc_rec: select * from "BIO_ASSAY"
(0) Silent DB Function!!
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 932):
(0) <<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
(0) Original SQL:
(0) SELECT * FROM "BIO_ASSAY"
(0)
(0)
(0) Accessing Database "SQLSRVL" with SQL:
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
(0)
(0)
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>(0) hoapars(15); Exited with retcode = 0.
(0) hoaopen(19); Entered. id = 1.
(0) hoaopen(19); Exited with retcode = 0.
(0) hoadscr(16); Entered. id = 1.
(0) hoastmt(195); Array fetch size is: 1.
(0) ------ hoadscr() -------:
(0) hoadamsz: 11, hoadasiz: 11, hoadambr: 1, hoadabrc: 1
(0) row 0 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 0 - hoadascl: 0, hoadanul: 0, hoadanml: 9, hoadanam: ASSAY_KEY, hoadabfl:
(0) 20, hoadamod: 0
(0) row 1 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 1 - hoadascl: 0, hoadanul: 0, hoadanml: 15, hoadanam: ENVIRONMENT_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 2 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 2 - hoadascl: 0, hoadanul: 1, hoadanml: 10, hoadanam: TARGET_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 3 - hoadambl: 20, hoadadty: 134, hoadaprc: 19, hoadacst: 0
(0) row 3 - hoadascl: 0, hoadanul: 0, hoadanml: 13, hoadanam: TIMEFRAME_KEY,
(0) hoadabfl: 20, hoadamod: 0
(0) row 4 - hoadambl: 2, hoadadty: 7, hoadaprc: 5, hoadacst: 0
(0) row 4 - hoadascl: 0, hoadanul: 1, hoadanml: 15, hoadanam: ASSAY_STATUS_NO,
(0) hoadabfl: 2, hoadamod: 0
(0) row 5 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 5 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: ASSAY_DATE_STARTED,
(0) hoadabfl: 7, hoadamod: 0
(0) row 6 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 6 - hoadascl: 0, hoadanul: 1, hoadanml: 18, hoadanam: ASSAY_DATE_PLANNED,
(0) hoadabfl: 7, hoadamod: 0
(0) row 7 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 7 - hoadascl: 0, hoadanul: 1, hoadanml: 20, hoadanam:
(0) ASSAY_DATE_COMPLETED, hoadabfl: 7, hoadamod: 0
(0) row 8 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 8 - hoadascl: 0, hoadanul: 0, hoadanml: 18, hoadanam: ASSAY_CREATED_TIME,
(0) hoadabfl: 7, hoadamod: 0
(0) row 9 - hoadambl: 7, hoadadty: 167, hoadaprc: 0, hoadacst: 0
(0) row 9 - hoadascl: 0, hoadanul: 1, hoadanml: 19, hoadanam: ASSAY_MODIFIED_TIME,
(0) hoadabfl: 7, hoadamod: 0
(0) row 10 - hoadambl: 0, hoadadty: 0, hoadaprc: 0, hoadacst: 0
(0) row 10 - hoadascl: 0, hoadanul: 0, hoadanml: 13, hoadanam: WH_ADDED_DATE,
(0) hoadabfl: 0, hoadamod: 0
(0) hoadscr(16); Exited with retcode = 0.
Note, the query I entered doesn't appear and I never entered the query "select * from bio_assay" - it appears that this is
automatically generated by the hsodbc process and raises other questions (like wouldn't this return a lot of unneeded
data ?). It also appears that the "select * from bio_assay" gets translated somehow into a select statement listing each column - however, non of the columns listed are character type columns like assay_name.
Does this make sense to anyone? Any ideas ? HELP ! 
Convert columns of NVARCHAR datatype in SQLServer into VARCHAR 
How do I convert NVARCHAR to VARCHAR ? I can't (not allowed) modify the SQL Server database. THANKS for the reply. 
Use remote view instead of remote table when accessing from Oracle. View must contain conversion function in SELECT clause (I don't remember the syntax for such function) 
Remote view gives the same error. I think the problem has to do with nchar and nvchar fields. It seems that the hsodbc agent issues a "select * from bio_assay" at the beginning of the session (see above trace).
0) odbc_rec: select * from "BIO_ASSAY"
(0) Silent DB Function!!
(0) nvOUT (P:\Src\QP\QPT2SEXE.C 929):
(0) SELECT "T0000"."WH_ADDED_DATE" AS c00010, "T0000"."ASSAY_MODIFIED_TIME" AS c0009, "T0000"."ASSAY_CREATED_TIME" AS c0008, "T0000"."ASSAY_DATE_COMPLETED" AS c0007, "T0000"."ASSAY_DATE_PLANNED" AS c0006, "T0000"."ASSAY_DATE_STARTED" AS c0005, "T0000"."ASSAY_STATUS_NO" AS c0004, "T0000"."TIMEFRAME_KEY" AS c0003, "T0000"."TARGET_KEY" AS c0002, "T0000"."ENVIRONMENT_KEY" AS c0001, "T0000"."ASSAY_KEY" AS c0000 FROM "BIO_ASSAY" T0000
Apparently, the list of fields that is returned from the "select *" does not include any of the nchar or nvchar type columns. After this initial "select *" the oracle server only looks at the initial list of columns and gives an "invalid column" error for any fields that are not in this initial list. So the question is: how do I make the hsodbc agent see the nchar/nvchar type fields and include them in the initial list ? I've tried setting the HS_NLS_LANGUAGE and HS_NLS_NCHAR parameters but these seem to have no effect.
Any clue as to what's going on ? 
hsodbc 817 is not capable dealing with nchar/nvarchar columns.
See the manual of supported data types for more info:
Appendix D; page d-2:
ODBC -> Oracle
SQL_BIGINT -> NUMBER(19,0)
SQL_BINARY -> RAW
SQL_CHAR -> CHAR
SQL_DATE -> DATE
SQL_DECIMAL(p,s) -> NUMBER(p,s)
SQL_DOUBLE -> FLOAT(49)
SQL_FLOAT -> FLOAT(49)
SQL_INTEGER -> NUMBER(10)
SQL_LONGVARBINARY -> LONG RAW
SQL_LONGVARCHAR -> LONG
SQL_NUMERIC(p,s) -> NUMBER(p,s)
SQL_REAL -> FLOAT(23)
SQL_SMALLINT -> NUMBER(5)
SQL_TIME -> DATE
SQL_TIMESTAMP -> DATE
SQL_TINYINT -> NUMBER(3)
SQL_VARCHAR -> VARCHAR 
THANKS for pointing this out. Could I install the 9.2 version of hsodbc to get around this limitation ? 
hsodbc V9.2 maps it to characters; if you need NATIONAL CHARACTER set support, you must use TG4MSQL.
Regards,
Klaus 
Sorry, my last posting missed some copy/paste info.
Here now all the details:
A describe of the SQL server table works with hsodbc release 9.2; a select will fail due to the same reason as mentioned for 817. The dataype is not supported.
If you check out an odbc trace initiated by hsodbc, you'll see, that the ODBC datatypes are SQL_WVARCHAR and SQL_WCHAR. Both are not in the supported datatype list of hsodbc V92.
The only way to get national character set columns out of SQL Server is with TG4MSQL.

ORA-28500 Oracle 11g Gateway problem

Hi,
I have installed Oracle database 11g and configured Oracle Gateway 11g on Linux operating system.
I have created a database link through which i access data from SQL Server. By this below command,
select * from tablename#sqlserver; Where sqlserver is the db link.
It fetches the data but after some time connection automatically breaks and displayed below error message.
ERROR:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from SQLSERVER
Below is the log file
SQLFetch: row: 100, column 3, bflsz: 41,  bflar: 23, (bfl: 23, mbl: 40)
SQLFetch: row: 100, column 4, bflsz: 16, bflar: -1
SQLFetch: row: 100, column 4, bflsz: 16,  bflar: SQL_NULL_DATA
SQLFetch: row: 100, column 5, bflsz: 16, bflar: 16
SQLFetch: row: 100, column 5, bflsz: 16,  bflar: 16, (bfl: 16, mbl: 16)
SQLFetch: row: 100, column 6, bflsz: 4, bflar: 2
SQLFetch: row: 100, column 6, bflsz: 4,  bflar: 2, (bfl: 2, mbl: 3)
SQLFetch: row: 100, column 7, bflsz: 2, bflar: 2
SQLFetch: row: 100, column 7, bflsz: 2,  bflar: 2, (bfl: 2, mbl: 2)
SQLFetch: row: 100, column 8, bflsz: 2, bflar: 2
SQLFetch: row: 100, column 8, bflsz: 2,  bflar: 2, (bfl: 2, mbl: 2)
SQLFetch: row: 100, column 9, bflsz: 4, bflar: 4
SQLFetch: row: 100, column 9, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)
+100 rows fetched+
Exiting hgoftch, rc=0 at 2011/10/11-09:09:50
Entered hgoftch, cursor id 1 at 2011/10/11-09:09:50
hgoftch, line 130: Printing hoada # 0x10b55db0
MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)
DTY          NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
+4 INTEGER N 4 4 0/ 0 0 0 0 IsrCode+
+4 INTEGER Y 4 4 0/ 0 0 0 0 ParIsrCode+
+12 VARCHAR Y 16 40 0/ 0 0 0 200 IsrName+
+91 DATE Y 0 16 0/ 0 0 -1 0 StartDate+
+91 DATE N 16 16 0/ 0 0 0 0 EndDate+
+12 VARCHAR Y 2 3 0/ 0 0 0 200 IsoCtry+
+5 SMALLINT Y 2 2 0/ 0 0 0 0 FyeMonth+
+5 SMALLINT Y 2 2 0/ 0 0 0 0 SIC+
+4 INTEGER Y 4 4 0/ 0 0 0 0 NAICS+
Entered hgopoer at 2011/10/11-09:09:56
hgopoer, line 233: got native error 104 and sqlstate 01000; message follows...
+[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionRead (recv()). {01000,NativeErr = 104}[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation. {08S01,NativeErr = 11}+
Exiting hgopoer, rc=0 at 2011/10/11-09:09:56
hgoftch, line 730: calling SQLFetch got sqlstate 01000
+0 rows fetched+
Exiting hgoftch, rc=28500 at 2011/10/11-09:09:56 with error ptr FILE:hgoftch.c LINE:730 FUNCTION:hgoftch() ID:Fetch resultset data
Waiting for urgent response.
Regards,
Rizi 
Could you please provide us more details like the Oracle database release, the ODBC driver vendor and version (DataDirect?) and also the connection aborts always for the same table/select or also when selecting other tables. 
Hi,
More details regarding above issue.
Oracle version 11.2.0.1.0
Oracle Gateway version 11.2.0 for MS-SQL Server
This problem exist for all tables not for specific tables.
Regards,
Rizi 
please post the complete listener.ora file of the gateway (remove host name), the sqlnet.ora file located in your database home/network7admin and the gateway init file. Here also remove the host name if you don't want to publish it. 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4msql)
)
)
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
GATEWAY Parameters
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[]:1433//qqq
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
# custom init parameters
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
HS_NLS_LENGTH_SEMANTICS=CHAR
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
HS_NLS_NCHAR=UCS2 
configuration looks good - no timeout in the listener (inbound_connect_timeout) nor in sqlnet.ora or within the gateway.
Could you please post a gateway trace level 255 (HS_FDS_TRACE_LEVEL=255) - for example upload it to: http://dl.dropbox.com
Edited by: kgronau on Oct 12, 2011 2:00 PM
Issue is similar tp ORA-28500: connection Error
Does it belong to you as well?
If yes, please close one thread.

Categories

Resources