Re: ORA-00900: invalid SQL statement

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 Received on Fri Oct 24 2008 - 06:25:48 CDT