Hi,
I was looking for some help for Dynamic SQL in Oracle 9i.
My requirement is, I have a table, where a user's preferences are set. This table contains column_name, column_type & column_value. Now this information is organised in rows. I want to output this information using a REF cursor.
column_name would become the name of the column in the row. column_type would define the datatype of that column i.e. whether it is varchar2 or number etc. & column_value would be value of the column.
I am sure that I would be returning only one row.
My idea was of declaring a cursor, go through all the rows, As I loop thru the cursor, create local variables of specified types, assign then the values from the column_value and simultaneoult prepare a dynamic SQL statements which would select all these local variables and output them as a single row using REF cursor.
But I could not get any help / example on Dynamic SQL to achieve this.
-------------------
Any help/suggestions are welcome
-Mukund.
I was looking for some help for Dynamic SQL in Oracle 9i.
My requirement is, I have a table, where a user's preferences are set. This table contains column_name, column_type & column_value. Now this information is organised in rows. I want to output this information using a REF cursor.
column_name would become the name of the column in the row. column_type would define the datatype of that column i.e. whether it is varchar2 or number etc. & column_value would be value of the column.
I am sure that I would be returning only one row.
My idea was of declaring a cursor, go through all the rows, As I loop thru the cursor, create local variables of specified types, assign then the values from the column_value and simultaneoult prepare a dynamic SQL statements which would select all these local variables and output them as a single row using REF cursor.
But I could not get any help / example on Dynamic SQL to achieve this.
-------------------
Any help/suggestions are welcome
-Mukund.