Re: pl/sql - help!!

From: John Ennis <john_at_pjpsoft.com>
Date: 1997/02/05
Message-ID: <32F7E355.5F76@pjpsoft.com>#1/1

Sylvie Bérubé wrote:
>
> Goldrich wrote:
> >

 <snip>
> > How do I issue a create table stmt w/in a PL/SQL block in a stored proc
> > implementation.
> <snip>
>
> I have almost the same problem. I'm trying to do an ALTER TABLE
> statement to disable an R.I. constraint in a stored procedure and this
> will not work.
> 1) DBMS_SQL doesn't give the possibility of doing anything else that
> analyzing or creating tables.
> 2) I tried creating my own package with the C pragma definition
> "execute_ddl" found in the DBMS_SQL package. It compiled but wouldn't
> work: sent back an error message stating it couldn't find the C proc.
> 3) Dynamic SQL is only for DML, not for DDL - am I wrong?
>
> So... if any of you know how to create a stored proc with an ALTER TABLE
> statement disable the R.I., I'd be happy to hear from you.
> Thanks in advance
>
> Sylvie Bérubé
> sberube_at_cam.org

The following procedure uses the dbms_sql package to disable the primary key constraint T_PK of a table T :

SQL> create or replace procedure t_off as

    dml_stmnt INTEGER;
BEGIN
    dml_stmnt := dbms_sql.open_cursor;
    dbms_sql.parse(dml_stmnt , 'ALTER TABLE t disable constraint t_pk',

                   dbms_sql.v7);

    dbms_sql.close_cursor(dml_stmnt );
EXCEPTION
WHEN OTHERS THEN
    dbms_sql.close_cursor(dml_stmnt );
END;
/

Hope this helps.

John Ennis
john_at_pjpsoft.com Received on Wed Feb 05 1997 - 00:00:00 CST