SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Extraction from Oracle

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Extraction from Oracle

[ Edited ]

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


Accepted Solutions
Solution
‎02-06-2017 02:17 AM
SAS Employee
Posts: 203

Re: Extraction from Oracle

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.

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Extraction from Oracle

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...

Data never sleeps
Solution
‎02-06-2017 02:17 AM
SAS Employee
Posts: 203

Re: Extraction from Oracle

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 302 views
  • 2 likes
  • 3 in conversation