BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

What are the steps that I need to take to write to an Oracle schema in Data Integration Studio directly?  When to creating a new table or when updating the table.  I can view Oracle schema in Data Integration Studio.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@DavidPhillips2 

The DIS way is: "You define a metadata libname definition against the Oracle schema, then register metadata tables against this libname and then use these table objects in your DIS job."

 

What's not o.k. with this? Why do you look for another option? What problem do you want to solve that above approach doesn't address?

 

If you could be a bit more specific then may be we can think of other solution approaches which better address your specific problem.

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

@DavidPhillips2 

You define a metadata libname definition against the Oracle schema, then register metadata tables against this libname and then use these tables objects in your DIS job.

If both source and target table are in Oracle then some of the SQL transformations (especially MERGE) allow to set options to use pass-through SQL (they show the Oracle O on the transform is setup this way). You then need to use Oracle SQL functions in the expressions as the generated code is explicit pass-through.

DavidPhillips2
Rhodochrosite | Level 12

Is there another option?

Patrick
Opal | Level 21

@DavidPhillips2 

That's the DIS way of doing things.

You can of course also hand-code everything from libname statement to append/insert/update code.

 

What's the problem you have to solve? Why do you ask for "another option"?

DavidPhillips2
Rhodochrosite | Level 12

I'm looking for a solution that does not involve hand coding.  That hand coding defeats the purpose of a graphical interface.

Patrick
Opal | Level 21

@DavidPhillips2 

The DIS way is: "You define a metadata libname definition against the Oracle schema, then register metadata tables against this libname and then use these table objects in your DIS job."

 

What's not o.k. with this? Why do you look for another option? What problem do you want to solve that above approach doesn't address?

 

If you could be a bit more specific then may be we can think of other solution approaches which better address your specific problem.

DavidPhillips2
Rhodochrosite | Level 12

I got confused by your paragraph about pass through SQL it looked like you were saying i had to manually write TSQL.

Patrick
Opal | Level 21

@DavidPhillips2 wrote:

I got confused by your paragraph about pass through SQL it looked like you were saying i had to manually write TSQL.


Yes, sorry, I know that my writing doesn't always express what I have in my mind.

I just wanted to say that some of the SQL transformations - especially Merge - allow you to set-up things in a way so that SAS DIS generates explicit pass-through SQL. And if it does then you can use database specific syntax in the expressions column of the mapping tab.

DIS shows you if it's using explicit Oracle pass-through by adding an "O" to the transformation. 

DavidPhillips2
Rhodochrosite | Level 12

It is also that almost all the documentation on Data Integration Studio involves TSQL to write to Oracle tables and this solution is both the proper streamline enterprise ETL method and the lesser known method.  The tool would not be an enterprise solution without it.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1852 views
  • 1 like
  • 2 in conversation