Re: Dynamic SQL
From: Sean <s_at_hotmail.com>
Date: Mon, 21 Aug 2000 11:49:55 -0400
Message-ID: <39A14FA3.EBD3DC1A_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
Date: Mon, 21 Aug 2000 11:49:55 -0400
Message-ID: <39A14FA3.EBD3DC1A_at_hotmail.com>
I'll check it out, thanks for the reply
[Quoted] "Matt B." wrote:
> "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. > > -MattReceived on Mon Aug 21 2000 - 17:49:55 CEST