Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Fine point of DBMS_SQL, and PLSQL.
Dear experts,
I don't think this can be done, but I will ask anyway.
We have two tables with a denormalized structure:
Question_01 Answer_01 Question_02 Answer_02 Question_03 Answer_03 Question_04 Answer_04 Question_05 Answer_05
The procedure I'm writing is supposed to take a string with all the responses, and update the table with the answers. It will use a rowtype as a local variable before updating the table.
You could do this with lots of code.
rowtypevar.Answer_01 := substr(p_answers, 1, 1); rowtypevar.Answer_02 := substr(p_answers, 2, 1); ...
However, there are actually some error checking statements that need to be done in PLSQL first. Nulls? Correct values?
So, I'm wondering, is there a way to use a loop, and dbms_sql?
I have queries to hit the data dictionary, and retrieve the columns in the right order.
Is there a way to pass in the name of the_column, and do the same thing over and over with DBMS_SQL?
for ... loop
/* do error checking
*/
dbms_sql.parse := (cursor_name, /* error checking string with the_column */, dbms_sql.native); rows_processed := dbms_sql.execute(cursor_name); /* prepare dbms_sql string using rowtypevar.the_column:
if (ok) then
dbms_sql.parse := (cursor_name, 'rowtypevar.the_column := substr(p_answers, counter, 1)', dbms_sql.native); rows_processed := dbms_sql.execute(cursor_name); END IF;
end loop;
UPDATE table with rowtype.
I've been trying different scenarios, without success.
My feeling is that dbms_sql does DML, and DDL statements on tables.
But does not do PLSQL statements on variables in memory, which is what I'm trying to do here.
Am I correct, and this is not possible?
Any other possibilities?
Thanks a lot! Received on Wed Aug 18 2004 - 17:29:45 CDT