Mephansteras
MIS
I need to do something that seems like it'd be easy, but I can't for the life of me figure out how to do this in oracle.
We have a table that contains information about letters that contains (among others) the fields: Letter Id, Column_Nm and Column_Val. A given letter might have anywhere from 2-12 records in this table.
I need to be able to turn those values into a temp table and return that information to an external program (JReports).
For example:
In the Letter table
1 'Addresss' 'Address-info'
1 'Customer id' 101
I need to create a temp table that would look like this
Address Customer id
Address-info 101
I then need to do a select from this temp table into a ref cursor and return that to the external program.
Problem is, I can't figure out how to make Oracle do all three. I can use Execute Immediately to dynamically create the temp table and populate it, but then the procedure won't compile because the temp table doesn't exist yet!
I've tried everything I can think of. Any ideas on how to make this work? Or is this something that would be easy in SQL Server but just can't be done in Oracle?
We have a table that contains information about letters that contains (among others) the fields: Letter Id, Column_Nm and Column_Val. A given letter might have anywhere from 2-12 records in this table.
I need to be able to turn those values into a temp table and return that information to an external program (JReports).
For example:
In the Letter table
1 'Addresss' 'Address-info'
1 'Customer id' 101
I need to create a temp table that would look like this
Address Customer id
Address-info 101
I then need to do a select from this temp table into a ref cursor and return that to the external program.
Problem is, I can't figure out how to make Oracle do all three. I can use Execute Immediately to dynamically create the temp table and populate it, but then the procedure won't compile because the temp table doesn't exist yet!
I've tried everything I can think of. Any ideas on how to make this work? Or is this something that would be easy in SQL Server but just can't be done in Oracle?