Re: Fine point of DBMS_SQL, and PLSQL.

Roger Redford wrote:
> 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!

Correct.

But what you are missing is that all of your kludging is an attempt to cover for an incredibly bad design.

Fix the design and the problem goes away. In other words ... redesign vertically rather than horizontally.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)