Re: pl/sql - help!!

From: John Hough <q6y_at_ornl.gov>
Date: 1997/02/04
Message-ID: <32F7B7FF.1AF5_at_ornl.gov>#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

Sylvie:

Below is the appropriate portions of a pl/sql procedure I wrote to truncate
a table, it uses the dbms_sql routines to perform dynamic sql. As far as I
know there is no reason that you could not perform a alter table. I have done
routines like this one for "truncate", and "drop public synonym" and have seen
generic procedures that allow you to pass in "ANY" ddl command and it will be
parsed and executed. I prefer to develop specific routines so I have more
control over who may perform which ddl commands.

Remember: The owner of this procedure MUST have the privileges needed to perform
whatever command will be parsed granted directly to their account and not inherited
via a role.

Good Luck,

John Hough

/* Create the truncate table procedure */

create or replace procedure truncate_table

        (p_table_owner in sys.dba_tables.owner%type,
         p_table_name  in sys.dba_tables.table_name%type)
is
        /* v$session variables   */
          v_current_user        v$session.username%type;

	/* truncate_table_privs table */
           v_status_code 	truncate_table_privs.status_code%type; 

        /* general use variables */
           v_counts             number;
           v_cursor             number;
           v_ddl_command        varchar2(80);

Begin

   /* Author:           John E. Hough, Jr.
                        NCI Infosystems, Inc.
      Program:          Truncate Table
      Date:             01/15/97
      Purpose:
                                                                */

        DBMS_OUTPUT.PUT_LINE('Beginning Procedure truncate table');

        v_ddl_command := 'truncate table
'||p_table_owner||'.'||p_table_name;
        /* DBMS_OUTPUT.PUT_LINE('Opening Cursor'); */ 
        v_cursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_OUTPUT.PUT_LINE('Executing ddl_command: '||v_ddl_command);
        /* DBMS_OUTPUT.PUT_LINE('Calling dbms_sql.parse'); */
        DBMS_SQL.PARSE(v_cursor, v_ddl_command,DBMS_SQL.V7);
        /* DBMS_OUTPUT.PUT_LINE('Calling dbms_sql.execute'); */
        v_counts := DBMS_SQL.EXECUTE(v_cursor);
        /* DBMS_OUTPUT.PUT_LINE('Calling dbms_sql.close_cursor');*/
        DBMS_SQL.CLOSE_CURSOR(v_cursor);
        commit work;
        DBMS_OUTPUT.PUT_LINE(p_table_owner||'.'||p_table_name||' table

has been truncated!! ');

    else

 	DBMS_OUTPUT.PUT_LINE('ACCESS TO TRUNCATE '||p_table_name);
        DBMS_OUTPUT.PUT_LINE('HAS BEEN REVOKED FROM USER
'||v_current_user); 
    	DBMS_OUTPUT.PUT_LINE('CONTACT SCHEMA OWNER '||p_table_owner);	

    end if;

    EXCEPTION

        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error occurred during processing');
            DBMS_OUTPUT.PUT_LINE('Processing terminated, work
rollbacked');
            DBMS_OUTPUT.PUT_LINE('Error occurred:
'||substr(SQLERRM,1,80));
            rollback work;

end truncate_table;
/
show errors Received on Tue Feb 04 1997 - 00:00:00 CET