PL/SQL Error

PL/SQL Error

Post by Tim Mas » Fri, 19 Oct 2001 21:17:32



I was trying to implement a trigger
 CREATE OR REPLACE TRIGGER answer_view_Trig
 AFTER UPDATE ON MON_Answer
 begin
      CREATE OR REPLACE VIEW Answer_View
        AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,  
                 B.MA_ID, B.MA_Answer, B.ME_ID
            FROM MON_Question A,
                   MON_Answer B
            WHERE  A.MQ_ID = B.MQ_ID)
end

and received the following error.

PLS-00103: Encountered the symbol "(" when expecting one of the
following:
   begin declare exit for goto if loop mod null pragma raise
   return select update while <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql commit <a single-quoted SQL string>

Can someone point me in a direction as to how to fix this error.

                Thanks,
                    Tim Mason

 
 
 

PL/SQL Error

Post by Alexei VORONO » Fri, 19 Oct 2001 21:57:25


I have tried to make that and had the same error

PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>

it seems to be impossible create something inside the trigger (when I
replace
create statement by something else, this works).



Quote:> I was trying to implement a trigger
>  CREATE OR REPLACE TRIGGER answer_view_Trig
>  AFTER UPDATE ON MON_Answer
>  begin
>       CREATE OR REPLACE VIEW Answer_View
> AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,
>                  B.MA_ID, B.MA_Answer, B.ME_ID
>     FROM MON_Question A,
>    MON_Answer B
>     WHERE  A.MQ_ID = B.MQ_ID)
> end

> and received the following error.

> PLS-00103: Encountered the symbol "(" when expecting one of the
> following:
>    begin declare exit for goto if loop mod null pragma raise
>    return select update while <an identifier>
>    <a double-quoted delimited-identifier> <a bind variable> <<
>    close current delete fetch lock insert open rollback
>    savepoint set sql commit <a single-quoted SQL string>

> Can someone point me in a direction as to how to fix this error.

>                 Thanks,
>                     Tim Mason


 
 
 

PL/SQL Error

Post by Marc Bl » Fri, 19 Oct 2001 22:32:50



Quote:>I was trying to implement a trigger
> CREATE OR REPLACE TRIGGER answer_view_Trig
> AFTER UPDATE ON MON_Answer
> begin
>      CREATE OR REPLACE VIEW Answer_View
>    AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,  
>                 B.MA_ID, B.MA_Answer, B.ME_ID
>        FROM MON_Question A,
>               MON_Answer B
>        WHERE  A.MQ_ID = B.MQ_ID)
>end

>and received the following error.

>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
>   begin declare exit for goto if loop mod null pragma raise
>   return select update while <an identifier>
>   <a double-quoted delimited-identifier> <a bind variable> <<
>   close current delete fetch lock insert open rollback
>   savepoint set sql commit <a single-quoted SQL string>

>Can someone point me in a direction as to how to fix this error.

>                Thanks,
>                    Tim Mason

Inside a PL/SQL-block you cannot execute DDL !

To do so, you must use dynamic SQL.

regards
Marc Blum

http://www.marcblum.de

 
 
 

PL/SQL Error

Post by Thomas Kyt » Fri, 19 Oct 2001 22:06:57



says...

Quote:

>I was trying to implement a trigger
> CREATE OR REPLACE TRIGGER answer_view_Trig
> AFTER UPDATE ON MON_Answer
> begin
>      CREATE OR REPLACE VIEW Answer_View
>    AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,  
>                 B.MA_ID, B.MA_Answer, B.ME_ID
>        FROM MON_Question A,
>               MON_Answer B
>        WHERE  A.MQ_ID = B.MQ_ID)
>end

>and received the following error.

well, couple of issues.

o in order to do DDL in plsql, one must use dynamic sql.  Since there ain't a
version to be seen I can only say "see EXECUTE IMMEDIATE or DBMS_SQL depending
on your version"

o DDL commits, Triggers cannot commit.  An autonomous transaction in a trigger
can commit however, there are serious side effects, especially in your case.
What happens when the INSERT fires the trigger -- the view is created (and
committed) and THEN the insert fails and gets rolled back?  You have a view and
no row!

o This trigger would create what appears to be a static view -- its the same
view.  The ONLY thing you seem to be accomplishing with this is a total slow
down of your system, lots of cascading invalidations, lots of extra parsing (not
to mention that ddl in itself is very expensive)

I recommend you rethink your processing.  This looks like an extremely bad idea
-- creating a view in a trigger.  Why not just "create the view" instead of
creating the trigger?

Quote:>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
>   begin declare exit for goto if loop mod null pragma raise
>   return select update while <an identifier>
>   <a double-quoted delimited-identifier> <a bind variable> <<
>   close current delete fetch lock insert open rollback
>   savepoint set sql commit <a single-quoted SQL string>

>Can someone point me in a direction as to how to fix this error.

>                Thanks,
>                    Tim Mason

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp
 
 
 

PL/SQL Error

Post by Sybrand Bakke » Sat, 20 Oct 2001 03:23:52



Quote:>I was trying to implement a trigger
> CREATE OR REPLACE TRIGGER answer_view_Trig
> AFTER UPDATE ON MON_Answer
> begin
>      CREATE OR REPLACE VIEW Answer_View
>    AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,  
>                 B.MA_ID, B.MA_Answer, B.ME_ID
>        FROM MON_Question A,
>               MON_Answer B
>        WHERE  A.MQ_ID = B.MQ_ID)
>end

>and received the following error.

>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
>   begin declare exit for goto if loop mod null pragma raise
>   return select update while <an identifier>
>   <a double-quoted delimited-identifier> <a bind variable> <<
>   close current delete fetch lock insert open rollback
>   savepoint set sql commit <a single-quoted SQL string>

Remove the ( )
and please check up the create view syntax in the sql reference manual

Hth

Quote:>Can someone point me in a direction as to how to fix this error.

>                Thanks,
>                    Tim Mason

Sybrand Bakker, Senior Oracle DBA
 
 
 

1. pl/sql error utlizing dynamic sql

am getting error on the below code.

here are the errors:

pls-00201 identifier 'dbms_sql.open_cursor' must be declared
pls-00201 identifier 'dbms_sql.execute' must be declared

code:

procedure itable_proc
AS
    begin
    declare
    vname varchar2(8);
   RESULT  suser_t.mailerplanetcode%type;
   RESULT2 suser_t.mailerplanetcode%type;
   cursor c1 is
    select mailerplanetcode as plcde, mailerplanetcode   from
     suser_t;
   procedure execute_immediate( sql_stmt in varchar2 )
   as
     exec_cursor     integer default dbms_sql.open_cursor;
     rows_processed  number  default 0;
   begin
     dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
     rows_processed := dbms_sql.execute(exec_cursor);
     dbms_sql.close_cursor( exec_cursor );
      end;
    begin
   for i in c1 loop
     RESULT := i.plcde;
     vname := 'T'||RESULT||'_T';
     RESULT2 := i.mailerplanetcode;
     execute_immediate( 'insert into ' || vname || '

 scf,postal_oper_num,read_date,planetcode,postnet_zipcode,table_date)
                         select
scf,postal_oper_num,read_date,planetcode,postnet_zipcode,sysdate
                           from ext_report_t
                          where substr(planetcode,3,5) = ''' || RESULT2  ||
''''
                      );
   end loop;
    end;
 end itable_proc;

--

Stephen E. Poff

2. Repeating function

3. pl/sql error utilizing dynamic sql

4. Cursor Problem

5. PL/SQL error running catproc.sql

6. Need patch for unattened installation of SQL 2000 Enterprise CPU

7. Pl/Sql Error (may be..... )

8. Sorting GUIDs

9. PL/SQL Error with stored procedure

10. PL/SQL error

11. REP-1401: "beforetrigger", Fatal PL/SQL error occured

12. PL/SQL Error

13. pl/sql error handling