Creating View with SP

[WP]

I'd like to be able to replace some views using a single SP call.

I thoght the syntax was just :

PROCEDURE CreateViews AS

BEGIN

CREATE OR REPLACE VIEW DB2_FDBCLI ( CLRACI, CLPRNM, CLNOM, CLTLPH, CLTLFX,
CLTYPE, CLDTOU, CLDTAN, CLETAT, CLGERA, CLGER2, CLGRPE ) AS
SELECT LRACI,CLPRNM,CLNOM,CLTLPH,CLTLFX,CLTYPE,CLDTOU,CLDTAN,
CLETAT,CLGERA,CLGER2,CLGRPE
from olyftch.FDBCLI@esdev01.db2;

CREATE OR REPLACE VIEW DB2_FDBTAB ( TBID, TBCODE)
AS SELECT TBID, TBCODE from olyftch.FDBCLI@esdev01.db2;

END CreateViews;

but this give the compilation 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

Can someone please help.

Thanks Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[MikeJones]

I'm thinking that this is the PL/SQL engine throwing up an error because you are trying to execute an SQL Plus command.

Trying wrapping the statements in an execute immidiate statement which should solve your problem...

execute_immidiate
'CREATE OR REPLACE VIEW DB2_FDBTAB ( BID, TBCODE) '||
|| ' AS SELECT TBID, TBCODE from olyftch.FDBCLI@esdev01.db2';

Or you could use...

dbms_utility.exec_ddl_statement('CREATE OR REPLACE VIEW DB2_FDBTAB ( BID, TBCODE) '||
|| ' AS SELECT TBID, TBCODE from olyftch.FDBCLI@esdev01.db2');

same thing different way...

HTH,

Mike.

Mike.

 

[sem]

You can not run ddl commands directly from pl/sql. You have to use &quot;execute imediate&quot; if you are on 8i or dbms_sql on earlier versions.

 

[MikeJones]

On a rather pedantic note, I like using dbms_utility.exec_ddl_statement kinda self documents what you are doing...