• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

PL/SQL Procedure Alter Table

Hi,
  I am trying to write a PL/SQL function to disable a table constraint. I continue to get syntax errors, and do not understand what I am doing wrong. Any help would be greatly appreciated.

Procedure -
CREATE PROCEDURE TEST()
 AS
BEGIN
   ALTER TABLE CM
   DISABLE CONSTRAINT FK_CM_REFERENCE_USERS;
END TEST;

Error -
Line # = 3 Column # = 1 Error Text = PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:     begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge    <a single-quoted SQL string> pipe


Thanks
0
RockyFullen
Asked:
RockyFullen
1 Solution
 
seazodiacCommented:
the procedure has incorrect syntax, for DDL you have to use dynamic SQL:

CREATE PROCEDURE TEST           --you don't need parathesis here.
AS
BEGIN
  execute immediate 'ALTER TABLE CM DISABLE CONSTRAINT FK_CM_REFERENCE_USERS';
END TEST;
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now