topic Re: Write to Oracle Schema from Data Integration Studio in SAS Data Management

Write to Oracle Schema from Data Integration Studio

DavidPhillips2 — Wed, 22 May 2019 16:18:49 GMT

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.

Re: Write to Oracle Schema from Data Integration Studio

Patrick — Thu, 23 May 2019 04:55:14 GMT

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

Re: Write to Oracle Schema from Data Integration Studio

DavidPhillips2 — Thu, 23 May 2019 12:38:12 GMT

Is there another option?

Re: Write to Oracle Schema from Data Integration Studio

Patrick — Thu, 23 May 2019 22:31:12 GMT

@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"?

Re: Write to Oracle Schema from Data Integration Studio

DavidPhillips2 — Wed, 29 May 2019 12:51:30 GMT

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

Re: Write to Oracle Schema from Data Integration Studio

Patrick — Thu, 30 May 2019 10:36:42 GMT

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

Re: Write to Oracle Schema from Data Integration Studio

DavidPhillips2 — Thu, 30 May 2019 12:42:25 GMT

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

Re: Write to Oracle Schema from Data Integration Studio

Patrick — Thu, 30 May 2019 13:10:56 GMT

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

Re: Write to Oracle Schema from Data Integration Studio

DavidPhillips2 — Thu, 30 May 2019 13:50:30 GMT

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.