I am really wondering about data loading to the cloud, I've just read the documentation, but regarding the packages, I have more than 100 package and I have to "add to cart" one by one the problem is that the package body is not added by default, so you have to expand the packages one by one then select the package body right click and add to cart. This task is very tedious, is there any other options.”
The DBMS_METADATA package is for DDL so not suitable for creating scripts to move data. If you want to generate a script containing insert statements for your table data then you need to use the SQL Developer export as insert statements or alternatively write you own SQL which will dynamically generate the required SQL inserts.
What about the sample data, building such scripts need from you to track the PKs and FKs, that what would expdp utility do, is there any means that I can expdp the local database and import it on the cloud?.
You can use SQL Developer to create scripts to export sample data by adding a WHERE clause to your export - it's called the Global Where in the SQL Dev export wizard. You can also specify DDL as part of the script. Do this for each of the tables you want to move and then load the resulting scripts in the SQL Workshop area of Application Express in the Cloud.
When you export the DDL, the script will include the constraints, such as the foreign keys. You will simply have to run the scripts in the appropriate order to avoid constraint violations.
Hope this helps.
- Rick Greenwald
Thanks all, it is very helpful, one last question, regarding the log of the deployment process, how can I find that the error causing records to be rejected. Sometimes I can see that the table is empty after data deployment, but I can't tell what the error was.
Thanks in advance
There are 2 ways to see data loading errors: 1) using SQL Developer, or 2) using SFTP.
1) In SQL Developer, find your deployment under Cloud Connections / Deployments. Open the LOGS tab and find the [table name]_DATA_TABLE.ctl record for the table in question. Double-click the DETAILS cell. You should see a popup with SQL*Loader output for this table, which will show ORA errors for rejected records.
2) Connect using SFTP account associated with your database service. The "download" directory should contain a zip file for your deployment (for example: LOG_deploy.zip). This zip file will contain SQL*Loader log file(s) that will also list any data loading errors.