soup_or_power_at_yahoo.com wrote:
> I think I found the problem. The C42 column in subscriber table is
> VARCHAR2(500). For your elucidation I am attaching the schema. Sorry I
> don't have the elaboration of the error message other than what is
> already posted in the first message (that's what Perl gave me:). Also
> the SQL is dynamically created, so unless perl is your cup of tea, it
> won't be much help.
>
> Many thanks!
>
> DA Morgan wrote:
> > 1. Oracle edition and version
> Version 8
>
>
>>2. DDL that creates the table sendq_tbl
>
> CREATE TABLE SENDQ_TBL
> (
> PROFILE_ID NUMBER(10) NOT NULL,
> SLIST_ID NUMBER(10) NOT NULL,
> CREATED_TM DATE,
> MODIFIED_TM DATE DEFAULT SYSDATE,
> DELETED_FLG NUMBER(1) DEFAULT 0,
> SBLOCK VARCHAR2(4000),
> BLOCK_ID NUMBER(10),
> SLIST_CELL_ID NUMBER(10) DEFAULT NULL,
> FILE_LOC BLOB,
> CLOB_COL CLOB,
> BLOB_FILENAME VARCHAR2(30),
> BLOB_MIME_TYPE VARCHAR2(100),
> BLOB_COL BLOB
> )
>
>>3. DDL that creates the table subscriber_tbl
>
> CREATE TABLE SUBSCRIBER_TBL
> (
> PROFILE_ID NUMBER(10) NOT NULL,
> PLIST_ID NUMBER(10),
> PROFILE_KEY VARCHAR2(100),
> EMAIL_ADDR VARCHAR2(100),
> ALT_EMAIL_ADDR VARCHAR2(1000),
> L_INDEX VARCHAR2(20),
> BLOCK_ID NUMBER(10) DEFAULT 0,
> G_INDEX VARCHAR2(20),
> CREATED_TM DATE DEFAULT SYSDATE,
> MODIFIED_TM DATE DEFAULT SYSDATE,
> MAIL_PREF_MASK VARCHAR2(500),
> MAIL_PREF CHAR(1),
> C1 VARCHAR2(500),
> C2 VARCHAR2(500),
> C3 VARCHAR2(500),
> C4 VARCHAR2(500),
> C5 VARCHAR2(500),
> C6 VARCHAR2(500),
> C7 VARCHAR2(500),
> C8 VARCHAR2(500),
> C9 VARCHAR2(500),
> C10 VARCHAR2(500),
> C11 VARCHAR2(500),
> C12 VARCHAR2(500),
> C13 VARCHAR2(500),
> C14 VARCHAR2(500),
> C15 VARCHAR2(500),
> C16 VARCHAR2(500),
> C17 VARCHAR2(500),
> C18 VARCHAR2(500),
> C19 VARCHAR2(500),
> C20 VARCHAR2(500),
> C21 VARCHAR2(500),
> C22 VARCHAR2(500),
> C23 VARCHAR2(500),
> C24 VARCHAR2(500),
> C25 VARCHAR2(500),
> C26 VARCHAR2(500),
> C27 VARCHAR2(500),
> C28 VARCHAR2(500),
> C29 VARCHAR2(500),
> C30 VARCHAR2(500),
> C31 VARCHAR2(500),
> C32 VARCHAR2(500),
> C33 VARCHAR2(500),
> C34 VARCHAR2(500),
> C35 VARCHAR2(500),
> C36 VARCHAR2(500),
> C37 VARCHAR2(500),
> C38 VARCHAR2(500),
> C39 VARCHAR2(500),
> C40 VARCHAR2(500),
> C41 VARCHAR2(500),
> C42 VARCHAR2(500),
> C43 VARCHAR2(500),
> C44 VARCHAR2(500),
> C45 VARCHAR2(500),
> C46 VARCHAR2(500),
> C47 VARCHAR2(500),
> C48 VARCHAR2(500),
> C49 VARCHAR2(500),
> C50 VARCHAR2(500),
> C51 VARCHAR2(500),
> C52 VARCHAR2(500),
> C53 VARCHAR2(500),
> C54 VARCHAR2(500),
> C55 VARCHAR2(500),
> C56 VARCHAR2(500),
> C57 VARCHAR2(500),
> C58 VARCHAR2(500),
> C59 VARCHAR2(500),
> C60 VARCHAR2(500),
> C61 VARCHAR2(500),
> C62 VARCHAR2(500),
> C63 VARCHAR2(500),
> C64 VARCHAR2(500),
> C65 VARCHAR2(500),
> C66 VARCHAR2(500),
> C67 VARCHAR2(500),
> C68 VARCHAR2(500),
> C69 VARCHAR2(500),
> C70 VARCHAR2(500),
> C71 VARCHAR2(500),
> C72 VARCHAR2(500),
> C73 VARCHAR2(500),
> C74 VARCHAR2(500),
> C75 VARCHAR2(500),
> C76 VARCHAR2(500),
> C77 VARCHAR2(500),
> C78 VARCHAR2(500),
> C79 VARCHAR2(500),
> C80 VARCHAR2(500),
> C81 VARCHAR2(500),
> C82 VARCHAR2(500),
> C83 VARCHAR2(500),
> C84 VARCHAR2(500),
> C85 VARCHAR2(500),
> C86 VARCHAR2(500),
> C87 VARCHAR2(500),
> C88 VARCHAR2(500),
> C89 VARCHAR2(500),
> C90 VARCHAR2(500),
> C91 VARCHAR2(500),
> C92 VARCHAR2(500),
> C93 VARCHAR2(500),
> C94 VARCHAR2(500),
> C95 VARCHAR2(500),
> C96 VARCHAR2(500),
> C97 VARCHAR2(500),
> C98 VARCHAR2(500),
> C99 VARCHAR2(500),
> C100 VARCHAR2(500),
> C101 VARCHAR2(500),
> C102 VARCHAR2(500),
> C103 VARCHAR2(500),
> C104 VARCHAR2(500),
> C105 VARCHAR2(500),
> C106 VARCHAR2(500),
> C107 VARCHAR2(500),
> C108 VARCHAR2(500),
> C109 VARCHAR2(500),
> C110 VARCHAR2(500),
> C111 VARCHAR2(500),
> C112 VARCHAR2(500),
> C113 VARCHAR2(500),
> C114 VARCHAR2(500),
> C115 VARCHAR2(500),
> C116 VARCHAR2(500),
> C117 VARCHAR2(500),
> C118 VARCHAR2(500),
> C119 VARCHAR2(500),
> C120 VARCHAR2(500),
> C121 VARCHAR2(500),
> C122 VARCHAR2(500),
> C123 VARCHAR2(500),
> C124 VARCHAR2(500),
> C125 VARCHAR2(500),
> C126 VARCHAR2(500),
> C127 VARCHAR2(500),
> C128 VARCHAR2(500),
> C129 VARCHAR2(500),
> C130 VARCHAR2(500),
> C131 VARCHAR2(500),
> C132 VARCHAR2(500),
> C133 VARCHAR2(500),
> C134 VARCHAR2(500),
> C135 VARCHAR2(500),
> C136 VARCHAR2(500),
> C137 VARCHAR2(500),
> C138 VARCHAR2(500),
> C139 VARCHAR2(500),
> C140 VARCHAR2(500),
> C141 VARCHAR2(500),
> C142 VARCHAR2(500),
> C143 VARCHAR2(500),
> C144 VARCHAR2(500),
> C145 VARCHAR2(500),
> C146 VARCHAR2(500),
> C147 VARCHAR2(500),
> C148 VARCHAR2(500),
> C149 VARCHAR2(500),
> C150 VARCHAR2(500),
> C151 VARCHAR2(500),
> C152 VARCHAR2(500),
> C153 VARCHAR2(500),
> C154 VARCHAR2(500),
> C155 VARCHAR2(500),
> C156 VARCHAR2(500),
> C157 VARCHAR2(500),
> C158 VARCHAR2(500),
> C159 VARCHAR2(500),
> C160 VARCHAR2(500),
> C161 VARCHAR2(500),
> C162 VARCHAR2(500),
> C163 VARCHAR2(500),
> C164 VARCHAR2(500),
> C165 VARCHAR2(500),
> C166 VARCHAR2(500),
> C167 VARCHAR2(500),
> C168 VARCHAR2(500),
> C169 VARCHAR2(500),
> C170 VARCHAR2(500),
> C171 VARCHAR2(500),
> C172 VARCHAR2(500),
> C173 VARCHAR2(500),
> C174 VARCHAR2(500),
> C175 VARCHAR2(500),
> C176 VARCHAR2(500),
> C177 VARCHAR2(500),
> C178 VARCHAR2(500),
> C179 VARCHAR2(500),
> C180 VARCHAR2(500),
> C181 VARCHAR2(500),
> C182 VARCHAR2(500),
> C183 VARCHAR2(500),
> C184 VARCHAR2(500),
> C185 VARCHAR2(500),
> C186 VARCHAR2(500),
> C187 VARCHAR2(500),
> C188 VARCHAR2(500),
> C189 VARCHAR2(500),
> C190 VARCHAR2(500),
> C191 VARCHAR2(500),
> C192 VARCHAR2(500),
> C193 VARCHAR2(500),
> C194 VARCHAR2(500),
> C195 VARCHAR2(500),
> C196 VARCHAR2(500),
> C197 VARCHAR2(500),
> C198 VARCHAR2(500),
> C199 VARCHAR2(500),
> C200 VARCHAR2(500),
> DELETED_FLG NUMBER(1) DEFAULT 0,
> NUM_WEB NUMBER(10)
> )
>
>>4. DDL that creates the table_report_tbl
>
>
> CREATE TABLE SUB_REPORT_TBL
> (
> PROFILE_ID NUMBER(10),
> PLIST_ID NUMBER(10),
> NUM_SENT NUMBER(10) DEFAULT 0,
> SENT_TM DATE,
> NUM_FTD NUMBER(10) DEFAULT 0,
> FTD_TM DATE,
> NUM_CLICK NUMBER(10) DEFAULT 0,
> CLICK_TM DATE,
> NUM_REPLY NUMBER(10) DEFAULT 0,
> REPLY_TM DATE,
> NUM_GIF NUMBER(10) DEFAULT 0,
> GIF_TM DATE,
> NUM_WEB NUMBER(10) DEFAULT 0,
> WEB_TM DATE,
> NUM_OPTOUT NUMBER(10) DEFAULT 0,
> OPTOUT_TM DATE,
> OPTIN_MASK VARCHAR2(500),
> BLOCK_ID NUMBER(10)
> )
>
>>5. The shorter version of the SQL statement (above)
>> that creates the exception.
>
>
>>6. The full and complete error message you are getting: Not just the number.
>>
>>Then all I can promise is that I'll see if I can reproduce it here in
>>the lab.
>>--
>>Daniel A. Morgan
>>http://www.psoug.org
>>damorgan_at_x.washington.edu
>>(replace x with u to respond)
Sure looks like that is the issue. But there is another huge issue in
the table structure you posted. Columns C1 through C200. There is just
no excuse for this in any relational database and a redesign should be
your first priority.
One simple solution would be a table with a structure like this:
CREATE TABLE profile_col_values (
PROFILE_ID NUMBER(10) NOT NULL,
COLUMN_ID NUMBER(4) NOT NULL,
COLUMN_VALUE VARCHAR2(500));
and I find the VARCHAR2(500) horrifying at face value too.
Then
ALTER TABLE profile_columns
ADD CONSTRAINT pk_profile_col_values
PRIMARY KEY (profile_id, column_id)
USING INDEX
PCTFREE 0;
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Jul 18 2005 - 14:23:52 CDT
