Re: BEGIN-SQL ALTER TABLE (oracle)

[Brian McGraw <Brian_McGraw@SONAT.COM>]

Oracle will not allow statements that alter its data dictionary to compile
in a PL/SQL script (i.e. scripts with a Begin and End statement).  You must
use the dbms_sql
package to create the SQL script dynamically, and execute it.

Below is an example of declaring a statement and executing it via the
dbms_sql package:

DECLARE
  dsql_str           VARCHAR2(250);
  source_cursor       INTEGER;
  source_rows         INTEGER;
--
BEGIN
--
  dsql_str     := 'ALTER TABLE <table_name> DISABLE CONSTRAINT
<constraint_name>';
  source_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (source_cursor, dsql_str, dbms_sql.NATIVE);
  source_rows := dbms_sql.execute(source_cursor);
  dbms_sql.close_cursor (source_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(source_cursor) THEN
      dbms_sql.close_cursor(source_cursor);
    END IF;
    RAISE;
END;

Hope that helps -

Brian





Joe Story <joe.story@FOXINTERNET.NET> on 04/30/98 04:25:18 PM

Please respond to SQR-USERS@USA.NET

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Brian McGraw/Sonat)
Subject:  BEGIN-SQL  ALTER TABLE (oracle)




We run Oracle 7.3.2.3 on Netware 4.11 with SQR 3.5. We want to disable a
constraint using
BEGIN-SQL
ALTER TABLE table.name DISABLE CONSTRAINT constraint.name
END-SQL
but we receive this error message:
Error at:  ALTER
Error on line 15:
   (SQR 3734) Couldn't compile SQL.
Any ideas?
Joe Story