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.
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.
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.
Is there another option?
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"?
I'm looking for a solution that does not involve hand coding. That hand coding defeats the purpose of a graphical interface.
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.
I got confused by your paragraph about pass through SQL it looked like you were saying i had to manually write TSQL.
@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.
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.
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.
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.