Any way to copy PROD Oracle tables to DEV using SAS ETL tools (only) for testing purposes?

[tsndrct1234]

Hello!

Disclaimer: This is going to be a long and confusing post but I will try to make it simpler since I badly need help right now.

Summary:

One of the ETL sources we had, recently migrated from AS400 to DB2 thus, I have to test how this will impact our ETL run. Now, I setup the baseline run flow on our DEV environment in SAS EG (cause this is where I have to do the testing, I know I could use Flow Manager but I don't and can't have access), I encountered a lot of error because of data duplicates. Tracing, I found out that most Oracle tables on my run are outdated. And these tables hugely impacted the whole chain and caused multiple errors and delay on my test. My superiors won't allow the idea of involving Oracle team here and wants me to find a way to update those Oracle source tables using my SAS tools only: SAS EG, SAS DIS and SAS MC but I don't know how or if it is feasible. I Googled that we can use SQL Developer but I'll have to understand if I'm missing something? Is there a way to setup my SAS tools to copy the PROD version of these Oracle tables to my DEV env?

My superiors uses Flow Manager to run the ETL, but I don't have access so I manually replicated the whole ETL chain on my SAS EG and it's super messy once I triggered the run. I sense they want me to find a way around to test all of this before going live but I running out of options. Can you help me? Thank you so much in advance!

[pink_poodle]

You would need two transfers 1) DEV to SAS, and 2) SAS to PROD. The principle how to load Oracle tables into SAS and vice versa can be found here: https://communities.sas.com/t5/New-SAS-User/Copy-datasets-from-Oracle-to-SAS/td-p/539311. It involves pointing a SAS library to an Oracle database so they can “talk” (i.e., transfer tables) back and forth. You can even modify table in SAS then transfer to Oracle, but you are saying here just to copy.

[SASKiwi]

How many tables and how many columns and rows are involved? Do you even have update access to Dev Oracle tables to do what you have been asked to do? Only an Oracle DBA can set up the correct permissions for you.

[Sajid01]

Hello @tsndrct1234 

" encountered a lot of error because of data duplicates"
Can you please share the error messages?
Make sure the messages are readable .

[Patrick]

Honestly... just based on your description this feels like "a mess". And if it's "a mess" then normally the best way of action is to take a step back, re-assess and plan the right course of action.

Are you a tester? If so why is it your responsibility to update source data? And even if you could technically is it the right thing to do?

I suggest you first figure out the right path for environment set-up that will return valid test results, identify the task that need to be performed for this set-up, identify the resources that need to execute,....

Basically: Decompose the messy problem into manageable steps, identify the sequence of actions and the responsibilities and then talk to your manager and get confirmation about the how and who.

...and once you're there we can help you with solving the problems per step - like how to load data from SAS to Oracle (if that's something you still need to do after having done the analysis).

[Sajid01]

Your question appears to be "is there a way to setup my SAS tools to copy the PROD version of these Oracle tables to my DEV env?"
Yes it is indeed possible. Programmers do it all the time in some form or the other.How to do it depends upon your local environment.
While you have access to SMC, SAS DIS and SAS EG, SAS EG alone should be sufficient to perform your task. (Most programmers do not have access to anything beyond EG. In that sense you are privileged).
At many places one cannot connect to production databases from the Dev environment or vice versa.
Under such scenario it is technically possible to create the tables in production environment and move it to dev. May be SAS or may be scp. Again exact steps depend upon your local environment. Your SAS Admin must be able to assist.
Make sure you can connect to and have access to databases of these environments.