On Oct 24, 6:32*am, Nikola Stjelja <nstje... (AT) gmail (DOT) com> wrote:
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
( 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)';
END IF;

TBL_EXISTS := NULL;

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
NUMBER DEFAULT 0 NOT NULL, REPORT_FILE_NAME VARCHAR2(255), DEFLT
CHAR(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;
/


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

On Oct 24, 6:32*am, Nikola Stjelja <nstje... (AT) gmail (DOT) com> wrote:
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
( 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)';
END IF;

TBL_EXISTS := NULL;

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
NUMBER DEFAULT 0 NOT NULL, REPORT_FILE_NAME VARCHAR2(255), DEFLT
CHAR(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;
/


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

On Oct 24, 6:32*am, Nikola Stjelja <nstje... (AT) gmail (DOT) com> wrote:
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
( 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)';
END IF;

TBL_EXISTS := NULL;

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
NUMBER DEFAULT 0 NOT NULL, REPORT_FILE_NAME VARCHAR2(255), DEFLT
CHAR(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;
/


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