Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Fine point of DBMS_SQL, and PLSQL.

Fine point of DBMS_SQL, and PLSQL.

From: Roger Redford <dba_222_at_yahoo.com>
Date: 18 Aug 2004 15:29:45 -0700
Message-ID: <a8c29269.0408181429.2f8a77f9@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US