Bill:
If I recall correctly, at one time Oracle used an distinct index to
implement the DISTINCT option. I wonder if the error message is Oracle's way
of telling you that it won't accept a text field in a distinct index (even
though unnecessary for correct execution of your query)?
Sig
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Bill
Droogendyk
Sent: Wednesday, September 08, 2004 12:57 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: ORA-00997: illegal use of LONG datatype
Folks:
I kept looking through help and tech support and found a thing or two to
try, but neither worked. I then removed the DISTINCT keyword (which was
only there because of cut and paste code re-use techniques) and then it
worked. The DBMAX_TEXT option otherwise works as reported.
The help suggestions were as follows:
*************ONE*************
In the SAS System Version 8, if you access a character value that is
1024 in length or greater, the resulting SAS Dataset variable defaults
to 1024 in length. To create a SAS Dataset variable longer than 1024
characters you must specify the DBMAX_TEXT= option either on the LIBNAME
statement or in the proc sql pass-through connection parameters
depending on which method you're using to access the data. Valid values
for DBMAX_TEXT are 1 to 32767.
For example:
proc sql;
connect to oracle (user=scott password=tiger path=V2o8040
dbmax_text=32767);
create table work.test as select * from connection to oracle
(select * from DEPT);
quit;
libname mylib oracle user=scott password=tiger path=V2o8040
dbmax_text=32767;
Note that although the example uses ORACLE as the database, this is not
a database specific issue. This applies to ODBC, SYBASE, DB2, etc as
well as ORACLE.
****************TWO***********
When using SQL Pass Through to create a dataset or SQL View to a DBMS
table with a long character datatype (LONG for Oracle, TEXT for
Sybase), the resulting SAS variable will have a default size of 32K.
Version 6 Sql Pass Through would have truncated such fields to 200.
If your DBMS table has a significant number of rows, this increase
from 200 to 32000 can have a huge impact on the amount of space needed
for the SAS dataset you create.
As a workaround, you can use the LENGTH= parameter on your SQL Pass
Through code to restrict the size of the variable. For example, the
following code assumes that you only need the first 200 characters
of the VAR1 field. It changes the length of the resulting VAR1 field
from the default of 32k to 200:
CREATE TABLE FOO AS
SELECT VAR1 LENGTH=200 FROM CONNECTION TO ORACLE
(SELECT VAR1 FROM MYTABLE);
Version 8 will have an option (DBMAX_TEXT) that will allow you to set
the maximum size of such text fields.
wd
Today's gift is ... Today!
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sarah
Whittier
Sent: 08 September, 2004 12:32
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: ORA-00997: illegal use of LONG datatype
Bill,
You may want to try the dbsastype option, although I'm not sure if that will
solve your problem. IIRC, it would be something like:
FROM rpt_comnt(dbsastype=(rpt_claim="char(1024)"))as a.
Sarah Whittier
--------------------------------------------------------------
Date: Wed, 8 Sep 2004 12:08:07 -0400
From: Bill Droogendyk <***@DOFASCO.CA>
Subject: Re: ORA-00997: illegal use of LONG datatype
Folks:
I get the error msg above (see log below) when trying to access an Oracle
table where the filed of interest is 1024 long. Does anyone have a
workaround? SAS 8.2, Oracle 9 something. Thanks, wd
187 create table rptcomnt as
188 select * from connection to oracle
189 (
190 SELECT DISTINCT
191 a.rpt_claim_no ,
192 a.rpt_comnt
193
194 FROM rpt_comnt a,
195 temptable b
196 WHERE a.rpt_claim_no=b.rpt_claim_no
197
198 order BY rpt_claim_no
199 );
ERROR: ORACLE prepare error: ORA-00997: illegal use of LONG datatype. SQL
statement: SELECT DISTINCT a.rpt_claim_no , a.rpt_comnt FROM rpt_comnt a,
temptable b WHERE a.rpt_claim_no=b.rpt_claim_no order BY rpt_claim_no.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements. 200 run;