Re: ORA-00900: invalid SQL statement

From: Nikola Stjelja <nstjelja_at_gmail.com>
Date: Fri, 24 Oct 2008 13:32:09 +0200
Message-ID: <gdsbnq$n3$1@ss408.t-com.hr>

gazzag wrote:

> On 24 Oct, 11:49, Nikola Stjelja <nstje..._at_gmail.com> wrote:

>> Hi ,
>> I've created the following stored procedure:
>>
>> CREATE OR REPLACE PROCEDURE METReportsCreateDBState AS
>> BEGIN
>> EXECUTE IMMEDIATE 'ALTER TABLE SYS_USER ADD WEB_USER VARCHAR(1) DEFAULT
>> CHAR(32) NOT NULL;';
>>
>> EXECUTE IMMEDIATE 'ALTER TABLE SYS_WEB_REPORT ADD DIRECTORY_PATH
>> VARCHAR2(256) DEFAULT CHAR(32) NULL';
>>
>> EXECUTE IMMEDIATE 'CREATE TABLE SYS_WEB_REPORT_DATABASE_CFG (
>> CONFIGURATION_ID NUMBER DEFAULT 0 NOT NULL, DATABASE_NAME VARCHAR2(50)
>> NOT NULL, SERVER VARCHAR2(256) NOT NULL, DB_USERNAME VARCHAR2(256) NOT
>> NULL, DB_PASSWORD VARCHAR2(256) NOT NULL, CONSTRAINT
>> PK_SYS_WEB_REPORT_DATABASE_CFG PRIMARY KEY (CONFIGURATION_ID))';
>> EXECUTE IMMEDIATE 'ALTER TABLE SYS_WEB_REPORT_DATABASE_CFG ADD
>> CONSTRAINT UQ_C_REPORT_DATABASE_CFG UNIQUE (DATABASE_NAME,SERVER)';
>>
>> EXECUTE IMMEDIATE 'DROP TABLE SYS_WEB_REPORT_CFG';
>> EXECUTE IMMEDIATE 'CREATE TABLE SYS_WEB_REPORT_CFG(CFG_ID NUMBER
>> DEFAULT 0 NOT NULL,REPORT_FILE_NAME VARCHAR2(255) DEFAULT CHR(32) NOT
>> NULL,CONFIGURATION_ID NUMBER DEFAULT 0 NOT NULL,CONSTRAINT
>> PK_SYS_WEB_REPORT_CFG PRIMARY KEY(CFG_ID),CONSTRAINT
>> FK_RPT_SYS_WEB_REPORT_CFG FOREIGN KEY (REPORT_FILE_NAME) REFERENCES
>> SYS_WEB_REPORT (REPORT_FILE_NAME),CONSTRAINT FK_CFG_SYS_WEB_REPORT_CFG
>> FOREIGN KEY (CONFIGURATION_ID) REFERENCES SYS_WEB_REPORT_DATABASE_CFG
>> (CONFIGURATION_ID))';
>>
>> END;
>>
>> When i execute it with the following code:
>> CALL METReportsCreateDBState();
>>
>> I get the following error: ORA-00900: invalid SQL statement;
>> I'm fairly new to oracle and I can't find what is wrong with the procedure.
>>
>> TIA

> 
> Why are you creating, altering and dropping database objects within a
> procedure?  It's really not a good idea and I doubt that it's
> required.
> 
> HTH
> 
> -g

Actually it is required for me to do that. :(

Do you know the source of the error? Received on Fri Oct 24 2008 - 06:32:09 CDT