Re: Variable fieldname in PL/SQL

Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in message > >
>
> The best solution would be using the copy and paste functionality of
> your editor, or setting up a table with a VARRAY instead of this
> denormalized design. Dynamic sql is support in sql statements only
> there is no dynamic pl/sql
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

You can in a greater sense have dynamic plsql - dynamic plsql blocks, that is...we do something like the following...

	IF (task_rec.tpid IS NULL) THEN
            proc_stmt := 'declare s varchar2(240):=null; begin ' ||
            get_rec.proc_name || '(' || to_char(x_project_id) ||
            ','''',''' || x_calling_process ||
            ''',''' || x_calling_place || ''',' ||
to_char(get_rec.amt) ||
            ',' || to_char(get_rec.percent) || ', to_date(''' ||
            x_rev_or_bill_date || '''),' || to_char(get_rec.bea_id) ||
',' ||
            to_char(get_rec.be_id) || ',' || to_char(x_request_id) ||
	    '); end;';
	ELSE
            proc_stmt := 'declare s varchar2(240):=null; begin ' ||
            get_rec.proc_name || '(' || to_char(x_project_id) ||
            ',' || task_rec.tpid || ',''' || x_calling_process ||
            ''',''' || x_calling_place || ''',' ||
to_char(get_rec.amt) ||
            ',' || to_char(get_rec.percent) || ', to_date(''' ||
            x_rev_or_bill_date || '''),' || to_char(get_rec.bea_id) ||
',' ||
            to_char(get_rec.be_id) || ',' || to_char(x_request_id) ||
            '); end;';
	END IF;

        x_error_message := 'Error during parsing the dynamic PL/SQL.';
        dbms_sql.parse(c, proc_stmt, dbms_sql.native);

        x_error_message := 'Error during executing the dynamic
PL/SQL.';
        row_processed := dbms_sql.execute(c);

Kind of like that. We have taken to calling it dynamic plsql around here, after the subject on page 9-7 of the 815 plsql fund manual.

Good luck,
Greg Received on Thu Jun 12 2003 - 18:45:01 CDT