CREATE TABLE using DBMS

From: Bradley Safer <bradley.safer_at_hbo.com>
Date: 1998/02/11
Message-ID: <34E1E14B.F49E6642@hbo.com>#1/1

I am fairly new to PL/SQL programming and have come across the following problem:

According to all of the Oracle documentation that I have read, you can use the DBMS_SQL package to perform DDL for which some parameters are known only at runtime.

From page 10-2 of the Oracle7 Server Application Developer's Guide:

"Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package."

When I make calls to the following procedure I get: "ORA-01031 Insufficient Privileges" however, when issuing the DDL contained in "createString" at the SQL*Plus command line under the same id it works fine:

PROCEDURE createBudgetYearTable(year IN VARCHAR2) IS

    cid INTEGER;
    tableName VARCHAR2(20);
    createString VARCHAR2(255);
BEGIN
    tableName := 'system_budget_'||year;     createString := 'CREATE TABLE '||tableName||' as select * from testTable';

    cid :=DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cid, createString, DBMS_SQL.v7);     DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('ERROR:'||SQLERRM);
        IF DBMS_SQL.IS_OPEN(cid) THEN
            DBMS_SQL.CLOSE_CURSOR(cid);
        END IF;

END createBudgetYearTable;

This code assumes that testTable already exists.

My company is running Oracle 7.3.3 with PL/SQL 2.3.3 on AIX 4.2.

I have tried to be as precise as possible with this problem description. If anyone has any advice, please post a detailed reply. Received on Wed Feb 11 1998 - 00:00:00 CST

CREATE TABLE using DBMS_SQL