Re: Dynamic SQL

"Sean" <s_at_hotmail.com> wrote in message news:399D3F8C.DB6978F8_at_hotmail.com...
> Hi can anyone help me with executing a query using dynamic sql. the
> program has
> multiple forms and get's the information for the querying from a .ini
> file at start up...but
> of course during the change over from static to dynamic it created an
> obscene amount of errors...
> the most troublesome so far has been with this stored procedure that I
> wrote. Using forms_ddl issues
> a commit that just buggers it up and it does not work...anyone have any
> ideas
>
> --PROCEDURE TO INCREMENT THE PROCESS ORDER--
> PROCEDURE INCREMENT_PROC IS
> SQL_STAT VARCHAR2(2000);
> --CONNECTED TEAM
> CONT_TEAM VARCHAR2(20);
> BEGIN
> CONT_TEAM := :GLOBAL.ENVCHG;
>
 SET_BLOCK_PROPERTY('MOD_REPL_CONS_TABS',UPDATE_CHANGED_COLUMNS,PROPERTY_TRUE);
>
> --CHECKS TO SEE IF THE RECORD STATUS IS CHANGED OR INSERT--
> IF :SYSTEM.RECORD_STATUS IN ('CHANGED','INSERT') THEN
> IF :GLOBAL.INCRE_CHCK != :PROCESS_ORDER OR :GLOBAL.INCRE_CHCK = 0 THEN
>
>
>
> SQL_STAT := 'UPDATE SVW__'||CONT_TEAM||'_NTL_REPL_CONS_TABS '
> ||'SET PROCESS_ORDER = PROCESS_ORDER + 1 '
> ||'WHERE PROCESS_ORDER >= :PROCESS_ORDER '
> ||'AND PROCESS_ORDER < 999 '
> ||'AND TABLE_NAME <> :TABLE_NAME '
> ||'AND PROCESS_TYPE = :PROCESS_TYPE;';
> FORMS_DDL(SQL_STAT);
> END IF;
> END IF;
> END;
>
>
> Thanks Sean
> -------Using Forms 6.0

Don't you have to parse it first with DBMS_SQL.PARSE? Look into that and see if it helps you out.

-Matt Received on Sat Aug 19 2000 - 02:01:30 CEST