Is it possible to create any procedure to extract tables from Oracle database? Something like i could select which table and which fields that i want to extract, so finally create a CSV file of table. In future i will need for oracle ERP too, but at this time just for database. I'm working in a audit project, so field consultants needs constantly extract tables from clients, and all this is by hand-made (table per table, script per script, there is so many waste time). So this way i'm trying to automated these extractions.
Thank you!
I can just give you the idea.
Create a procedure which accepts the Table name and comma-separated column names as two input parameters with a third out parameter which will be used to pass the status of the procedure at the end to the calling user.
CREATE PROCEDURE FETCH_TABLE_DATA( P_IN_TABLE_NAME IN VARCHAR2,
P_IN_CS_COLUMN_NAMES IN VARCHAR2,
P_OUT_MESSAGE OUT VARCHAR2)
You can now create the cursor using dynamic query and use something like:
'SELECT ' || COLUMN_NAMES_COMMA_SEPRATED || ' FROM ' || TABLE_NAME
and using UTL_FILE, you can put this all data to the text file.
In case of success, you can assign the name of the file to the P_OUT_MESSAGE or in case of failure, You can show the error message by assigning error message to P_OUT_MESSAGE
Search all the keywords used in the answer to prepare the procedure by yourself.
Hope, It will give you proper direction.
Cheers!!