Process Variable data to SQL database table

Now you have a CDT you could use that to generate a table in your DB (I presume you mean 'MySQL' as opposed to MS SQL Server?). Or you can hand-build a table that matches the CDT format. Either way, adding the CDT to a datastore that connects to your DB and publishing it will allow you to use the 'Write to Datastore' Smart Service in you process to write the data.

Would it be possible to create 2 CDTs, one to receive from Oracle, the other to transmit to MySQL, then use a script task to cast from one CDT type to the other CDT?

There are some good functional answers on this thread already, but I am curious about the volume of data you are dealing with and the frequency at which you want to perform this transfer. When you say "all the data from Oracle", how much data are you talking about? And how often will your application be doing this operation? At large scale, alternative methods would be preferred over doing this transfer in memory (via process variables).

the data is around 10k and the scheduler will run everyday

I would have to agree with Steven above that I wouldn't necessarily want to use Appian for that job.  It's a BPM and custom business application platform, built for those functionalities and not strictly as a database transfer tool.

Another question I have now is how long are you going to be running this on a daily basis.  Is there a legitimate business case for using 2 different DBMSs at the same time?  If you were decommissioning one, that would make sense, but I would expect the data to be much larger than 10k and done once.