Menu
Trying to make a generic PL/SQL procedure to export data in specific XML format, e.g. Excel XML. Let's say the procedure accepts a string with the SELECT query to EXECUTE IMMEDIATE.
This requires access to data types of each column of the resulting rowset, which -- seeing as the procedure is to be generic -- is only known after the query is run.
I have tried an approach with a temporary table, but for the procedure to compile the table must exist and have its structure known at compile time.
How can I next process the rows and columns of an EXECUTE IMMEDIATE result in a double loop that analyzes the type of each value and emits an appropriate piece of XML?
920
Another way to get a column's data type is to query the ALL_TAB_COLUMNS view: SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE OWNER = 'HR' AND TABLE_NAME = 'COUNTRIES';
ALL_TAB_COLUMNS is a view in Oracle that contains the information about all columns in all table. We can just query with the table name in this view to get the column names and data types of a table in Oracle.
In Oracle SQL, you have three options for replacing special characters: Using the REPLACE function. Using the REGEXP_REPLACE function. Using the TRANSLATE function.
You can't do that with EXECUTE IMMEDIATE. You'll have to use the more powerful (and more complex) DBMS_SQL package - I've linked you to the DESCRIBE_COLUMNS procedure, which is especially relevant.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
