Re: ORA-00900: invalid SQL statement

On Oct 24, 6:32 am, Nikola Stjelja <nstje..._at_gmail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

There are a number of errors in your code. The following statement is incorrect for several reasons:

        EXECUTE IMMEDIATE 'ALTER TABLE SYS_USER ADD WEB_USER VARCHAR(1) DEFAULT
CHAR(32) NOT NULL;'; There cannot be a semicolon IN the text string; it needs to go. Also DEFAULT is a reserved word and should NOT be used as a column name, and since you're trying to add TWO columns to this table the statement needs a comma after the VARCHAR(1) type declaration and should have () around the column definitions. A corrected statement is shown below:

        EXECUTE IMMEDIATE 'ALTER TABLE SYS_USER ADD (WEB_USER VARCHAR(1), DEFLT CHAR(32) NOT NULL)'; The second statement suffers from the same syntax and naming issues; the corrected statement is shown below:

        EXECUTE IMMEDIATE 'ALTER TABLE SYS_WEB_REPORT ADD (DIRECTORY_PATH VARCHAR2(256), DEFLT CHAR(32) NULL)'; Of course once you get this to actually execute the first time you'll never get it to execute again as you'll face the following error:

SQL> CALL METReportsCreateDBState();
CALL METReportsCreateDBState()

     *
ERROR at line 1:
ORA-01430: column being added already exists in table ORA-06512: at "SOMEUSR.METREPORTSCREATEDBSTATE", line 3

SQL> as you have absolutely no code to check for the existence of these columns you wish to add. This entire procedure is a mess; a corrected version of it, which does run repeatably, is found below:

CREATE OR REPLACE PROCEDURE METReportsCreateDBState AS

	CURSOR GET_SYS_USER_COLS IS
	SELECT 'X'
	FROM USER_TAB_COLUMNS
	WHERE TABLE_NAME = 'SYS_USER'
	AND COLUMN_NAME = 'WEB_USER';

	CURSOR GET_WEB_REPORT_COLS IS
	SELECT 'x'
	FROM USER_TAB_COLUMNS
	WHERE TABLE_NAME = 'SYS_WEB_REPORT'
	AND COLUMN_NAME = 'DIRECTORY_PATH';

	CURSOR FIND_DATABASE_REPORT_CFG IS
	SELECT 'X'
	FROM USER_TABLES
	WHERE TABLE_NAME = 'SYS_WEB_REPORT_DATABASE_CFG';

	CURSOR FIND_WEB_REPORT_CFG IS
	SELECT 'X'
	FROM USER_TABLES
	WHERE TABLE_NAME = 'SYS_WEB_REPORT_CFG';

	COL_EXISTS VARCHAR2(1) := NULL;
	TBL_EXISTS VARCHAR2(1) := NULL;
BEGIN
	OPEN GET_SYS_USER_COLS;
	FETCH GET_SYS_USER_COLS INTO COL_EXISTS;
	CLOSE GET_SYS_USER_COLS;

	IF COL_EXISTS IS NULL THEN
        	EXECUTE IMMEDIATE 'ALTER TABLE SYS_USER ADD (WEB_USER
VARCHAR(1), DEFLT CHAR(32) NOT NULL)';
	END IF;

	OPEN GET_WEB_REPORT_COLS;
	FETCH GET_WEB_REPORT_COLS INTO COL_EXISTS;
	CLOSE GET_WEB_REPORT_COLS;

	IF COL_EXISTS IS NULL THEN
	        EXECUTE IMMEDIATE 'ALTER TABLE SYS_WEB_REPORT ADD
(DIRECTORY_PATH VARCHAR2(256), DEFLT CHAR(32) NULL)';
	END IF;

	OPEN FIND_DATABASE_REPORT_CFG;
	FETCH FIND_DATABASE_REPORT_CFG INTO TBL_EXISTS;
	CLOSE FIND_DATABASE_REPORT_CFG;

	IF TBL_EXISTS IS NULL THEN
	        EXECUTE IMMEDIATE 'CREATE TABLE SYS_WEB_REPORT_DATABASE_CFG

	OPEN FIND_WEB_REPORT_CFG;
	FETCH FIND_WEB_REPORT_CFG INTO TBL_EXISTS;
	CLOSE FIND_WEB_REPORT_CFG;

	IF TBL_EXISTS = 'X' THEN
	        EXECUTE IMMEDIATE 'DROP TABLE SYS_WEB_REPORT_CFG';
	END IF;

        EXECUTE IMMEDIATE 'CREATE TABLE SYS_WEB_REPORT_CFG(CFG_ID

And a call to this procedure returns successfully:

SQL> CALL METReportsCreateDBState();

Call completed.

SQL> This is truly a disaster in the making, modifying, creating and dropping objects inside a stored procedure. Why is this 'necessary'? I see no valid reason for any of this to be written.

David Fitzjarrell Received on Fri Oct 24 2008 - 07:46:03 CDT