topic Re: Extraction from Oracle in SAS Data Management

Extraction from Oracle

Saranya_Sub — Wed, 11 Jan 2017 14:10:28 GMT

Hi,

Am trying to extract data from oracle into a dataset and append the data into Teradata .

since some of the native funtionality of teradata is not working when connecting to orcale.so ended up in pulling data to SAS and then upload to terdata.

proc sql;
connect to oracle (PATH=XXXX USER=XXXX PASSWORD="XXXX" PRESERVE_COMMENTS );
create table work.OracleTabExtract
as select * from
connection to oracle(
select /*+ parallel(16)*/ * from oracle.tablename
) ;

i want to convert all columns present in the oracle table to character if its numeric.since the date columns are not getting loaded properly into teradata as its warning out.

is there any idea which i can include in the above step? the problem is i have more than 300 columns in the table

Thanks in advance

Re: Extraction from Oracle

LinusH — Wed, 11 Jan 2017 15:07:59 GMT

First, 300 columns sounds like a bad data model, which will give trouble. And you are experiencing that right now. It gets very syntax intense.

Perhaps you don't need to convert all numerical columns. If you hit a SAS date limit on ly data/datetime columns need transformation.

If you already going through the trouble of writing explicit pass-through, just add cast() operations on the columns you wish to convert.

Reducing no lines code is done via macro in SAS. But since this is one time job, I think you'll won't save much time on creating such logic, so copy/paste I guess...

Re: Extraction from Oracle

JBailey — Tue, 17 Jan 2017 16:33:12 GMT

Hi @Saranya_Sub

It is possible to do this without landing the data in SAS. Adding a Teradata Parallel Transporter load to the create table could help with performance. DBTYPE= can help with the conversion.

That being said, @LinusH is correct. 300 columns is a lot to deal with. The program to do this could be long.