I've reduced the number of columns on the second table and I did not get an
error starting up.
But then I started getting errors on the first table after I changed a
column.
It seems to occur whenever I modify the database layout in any way.
I also see that I am generating a bunch of weird triggers each time (before
web2py deadlocks)
I am a bit worried about using Oracle at this stage - but it is the
'standard' database in the organisation.
Maybe I should create the tables, triggers and sequences manually as I have
to complete dev by Thursday and I'm already a day behind :-)
Other option may be to finish development on MySQL or sqlite and only move
to Oracle when 100% complete.
The trigger names are all resemble the name BIN$vlh/Lcs+QAbgQwqTAUZABg==$0
create or replace
TRIGGER "BIN$vlh/Lcs+QAbgQwqTAUZABg==$0" BEFORE INSERT ON
BIN$vlh/Lcs/QAbgQwqTAUZABg==$0 FOR EACH ROW BEGIN SELECT
auth_permission_sequence.nextval INTO :NEW.id FROM DUAL; END;
My current error is as follows.
<type 'exceptions.KeyError'> 'unique_num'
I changed UNIQUE_NUM by removing required=True and got the error.
Traceback (most recent call last):
File "/data/source/dtos/web2py/gluon/restricted.py", line 205, in restricted
exec ccode in environment
File "/data/source/dtos/web2py/applications/dtos/models/db.py"
<http://localhost:8000/admin/default/edit/dtos/models/db.py>, line 251, in
<module>
comment='The amount of tax paid to SARS in respect of In Specie dividends.')
File "/data/source/dtos/web2py/gluon/dal.py", line 6320, in define_table
polymodel=polymodel)
File "/data/source/dtos/web2py/gluon/dal.py", line 742, in create_table
fake_migrate=fake_migrate)
File "/data/source/dtos/web2py/gluon/dal.py", line 797, in migrate_table
and not isinstance(table[key].type, SQLCustomType) \
File "/data/source/dtos/web2py/gluon/dal.py", line 6714, in __getitem__
return dict.__getitem__(self, str(key))
KeyError: 'unique_num'
On Tuesday, 24 April 2012 09:24:10 UTC+2, Rakesh Singh wrote:
>
> Thank you Richard.
> I shall try your suggestions this morning.
>
>
> On Monday, 23 April 2012 17:12:36 UTC+2, Richard wrote:
>>
>> Also, since you have a lot of column, you may start with fewer column and
>> identify the exact column that trigger the error.
>>
>> I will be easier to help you with simpler model, you then can generalize
>> after we had found the problem.
>>
>> Richard
>>
>> On Mon, Apr 23, 2012 at 11:10 AM, Richard Vézina <
>> [email protected]> wrote:
>>
>>> It should not be the problem, but to make sure you set not null at the
>>> backend level you need : notnull=True if I remember.
>>>
>>> Richar
>>>
>>>
>>> On Mon, Apr 23, 2012 at 10:40 AM, Rakesh Singh <[email protected]
>>> > wrote:
>>>
>>>> Hi,
>>>>
>>>> I am having a lot of trouble implementing on Oracle.
>>>> When I run my app on SQLite or MySQL, but I am having lots of different
>>>> errors on Oracle.
>>>> One problem I have is that when an error is returned, the application
>>>> hangs.
>>>> I can view the admin app and browse the error ticket, but the thread
>>>> that is doing the database operation seems to hang. I have to kill -9 and
>>>> restart.
>>>>
>>>> My 1st table created correctly, but my 2nd gave me errors with floats,
>>>> so I changed to decimal and finally integer, but I still get errors.
>>>> Again, on sqlite and MySQL, it works perfectly, so I'm not sure where
>>>> the problem could be.
>>>>
>>>> What I haven't done on the 2nd table is used the "requires" validators
>>>> as the user may not have all the required information during the capturing
>>>> process.
>>>>
>>>>
>>>> Error : DatabaseError: ORA-00910: specified length too long for its
>>>> datatype
>>>>
>>>> Traceback
>>>>
>>>>
>>>> Traceback (most recent call last):
>>>> File "/data/source/dtos/web2py/gluon/restricted.py", line 205, in
>>>> restricted
>>>> exec ccode in environment
>>>> File "/data/source/dtos/web2py/applications/dtos/models/db.py", line
>>>> 470, in <module>
>>>> label='Start Date'),
>>>> File "/data/source/dtos/web2py/gluon/dal.py", line 6320, in
>>>> define_table
>>>> polymodel=polymodel)
>>>> File "/data/source/dtos/web2py/gluon/dal.py", line 633, in
>>>> create_table
>>>> precision, scale = map(int,field.type[8:-1].split(','))
>>>> ValueError: invalid literal for int() with base 10: ''
>>>>
>>>>
>>>>
>>>>
>>>> Here is my model :
>>>>
>>>> db.define_table('DT_SARS_DECLARANT',
>>>> Field('SECTION_ID',
>>>> 'string',
>>>> length=1,
>>>> default='B',
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Section Identifier'),
>>>> Field('RECORD_TYPE',
>>>> 'string',
>>>> length=6,
>>>> default='DD',
>>>> required=True,
>>>> #requires=[IS_UPPER(), IS_ALPHANUMERIC(), IS_NOT_EMPTY()],
>>>> requires=[IS_UPPER(), IS_ALPHANUMERIC()],
>>>> label='Record Type'),
>>>> Field('RECORD_STATUS',
>>>> 'string',
>>>> length=2,
>>>> default='N',
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Record Status',
>>>> comment='''Indicates the status of the record, whether it is
>>>> a new record, an adjusted record or a record that must be
>>>> deleted
>>>> from the declaration.'''),
>>>> Field('UNIQUE_NUM',
>>>> 'string',
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_ALPHANUMERIC(), IS_NOT_EMPTY()],
>>>> label='Unique Number',
>>>> comment='''The submitting entity’s system will have a unique
>>>> identifier for each record. This is the number that must be
>>>> entered
>>>> into this field.'''),
>>>> Field('ROW_NUMBER',
>>>> 'integer',
>>>> required=True,
>>>> requires=IS_INT_IN_RANGE(0, 9999999999),
>>>> label='Row Number',
>>>> comment='''A sequential number to indicate the row number
>>>> in the file.'''),
>>>> Field('TRANS_DUE_DATE',
>>>> 'date',
>>>> required=True,
>>>> requires=IS_DATE(),
>>>> label='Transaction Due Date',
>>>> comment='This is the due date of the transactionthat pertains
>>>> to this record.'),
>>>> Field('DIV_DECLARANT',
>>>> 'string',
>>>> length=1,
>>>> required=True,
>>>> requires=[IS_NOT_EMPTY(), IS_UPPER()],
>>>> label='Divident Declarant',
>>>> comment='Indicates if the entity submitting the file also
>>>> declared the dividend for this record.'),
>>>> # @TODO, There is a field that is missing in the Excel sheet.
>>>> # Confirmation if this is not required, else, it will go here...
>>>> Field('NATURE',
>>>> 'string',
>>>> length=33,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Dividend Declarant: Nature Of Person',
>>>> comment='The type/category of the divident declarant.'),
>>>> Field('REG_NAME',
>>>> 'string',
>>>> length=150,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Dividend Declarant: Registered Name',
>>>> comment='The name of the divident declarant.'),
>>>> Field('TRADE_NAME',
>>>> 'string',
>>>> length=150,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Trading Name',
>>>> comment='The name the devidend declaring entity is trading.'),
>>>> Field('REG_NUM',
>>>> 'string',
>>>> length=15,
>>>> required=False,
>>>> requires=[IS_UPPER(), IS_ALPHANUMERIC()],
>>>> label='Registration Number',
>>>> comment='The registration number of the divedent declaring
>>>> entity.'),
>>>> Field('ISIN_NUM',
>>>> 'string',
>>>> length=12,
>>>> required=False,
>>>> requires=[IS_UPPER(), IS_ALPHANUMERIC()],
>>>> label='ISIN Number',
>>>> comment='The international security identification number.'),
>>>> # @TODO The following Income Tax number field is marked 'On
>>>> Announcemnt.
>>>> # Confirmation required is this field is to be included. Is is for
>>>> now...
>>>> Field('INC_TAX_NUM',
>>>> 'integer',
>>>> required=False,
>>>> requires=IS_INT_IN_RANGE(),
>>>> label='Income Tax Reference Number',
>>>> comment='The entity\'s income tax number as registered at
>>>> SARS.'),
>>>> Field('RSA_RES',
>>>> 'string',
>>>> length=1,
>>>> required=True,
>>>> default='Y',
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Resident Of RSA',
>>>> comment='Indicates if the dividend declaring entity is resident
>>>> in South Africa.'),
>>>> Field('SHARES_LISTED',
>>>> 'string',
>>>> length=1,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Shares Listed On JSE',
>>>> comment='Indicates if the shares for which the dividend is
>>>> declared are listed on the JSE.'),
>>>> Field('MICRO_BUS',
>>>> 'string',
>>>> length=1,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Micro Business',
>>>> comment='Indicates if the dividend declaring entity is
>>>> registered as a micro busines.'),
>>>> Field('STC_CREDIT',
>>>> 'double',
>>>> required=False,
>>>> requires=IS_FLOAT_IN_RANGE(0, 999999999999999.99),
>>>> label='Available STC Credit',
>>>> comment='''This is the rolling balance of the STC credit that
>>>> the dividend declaring entity
>>>> has available to utilise when dividend is declared.'''),
>>>> Field('STC_CREDIT_UTIL',
>>>> 'double',
>>>> required=False,
>>>> requires=IS_FLOAT_IN_RANGE(0, 999999999999999.99),
>>>> label='STC Credit Utilised',
>>>> comment='The amount of STC credit that is utilised with this
>>>> declaration of dividend.'),
>>>> Field('ENTITY_TYPE',
>>>> 'string',
>>>> length=35,
>>>> required=False,
>>>> requires=[IS_NOT_EMPTY(), IS_ALPHANUMERIC()],
>>>> label='Entity Type',
>>>> comment='The type/ category of the entity the dividend was
>>>> received from.'),
>>>> Field('ENTITY_NAME',
>>>> 'string',
>>>> length=150,
>>>> required=False,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Entity Name',
>>>> comment='The name of the entity the dividend was received
>>>> from.'),
>>>> Field('ENTITY_TRADE_NAME',
>>>> 'string',
>>>> length=150,
>>>> required=False,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Entity Registered Name',
>>>> comment='The name the entity that the dividend was received
>>>> from is trading under.'),
>>>> Field('ENTITY_REG_NUM',
>>>> 'string',
>>>> length=15,
>>>> required=False,
>>>> requires=[IS_UPPER(), IS_ALPHANUMERIC(), IS_NOT_EMPTY()],
>>>> label='Entity Registration Number',
>>>> comment='The number of the entity that the dividend was
>>>> received from.'),
>>>> # @TODO Field is highlighted. We will include it for now.
>>>> Field('ENTITY_REF_NUM',
>>>> 'integer',
>>>> required=False,
>>>> requires=[IS_NOT_EMPTY(), IS_INT_IN_RANGE(0, 999999999999)],
>>>> label='Entity Tax Reference Number',
>>>> comment='The entity income tax number as registered at SARS.'),
>>>> Field('ENTITY_REC_DATE',
>>>> 'date',
>>>> required=False,
>>>> requires=IS_DATE(),
>>>> label='Entity Date Received',
>>>> comment='The date on which the dividend was received by the
>>>> submitting entity.'),
>>>> Field('DIV_TYPE',
>>>> 'string',
>>>> length=2,
>>>> required=True,
>>>> requires=[IS_UPPER(), IS_NOT_EMPTY()],
>>>> label='Divident Type',
>>>> comment='This field indicates the type of dividend that is
>>>> declared/ received.'),
>>>> Field('NUM_SHARES',
>>>> 'double',
>>>> required=True,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0,
>>>> 999999999999999.999999)],
>>>> label='Number Of Shares',
>>>> comment='Number of shares or units to which the dividend
>>>> relates.'),
>>>> Field('DIV_PER_SHARE',
>>>> 'double',
>>>> required=True,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0,
>>>> 999999999999999.999999)],
>>>> label='Dividend Per Share',
>>>> comment='The dividend that is declared per share or unit.'),
>>>> Field('DIV_VALUE',
>>>> 'double',
>>>> required=True,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0,
>>>> 999999999999999.999999)],
>>>> label='Divident Value',
>>>> comment='The gross value of the dividend declared.'),
>>>> Field('DATE_DECLARED',
>>>> 'date',
>>>> required=True,
>>>> requires=IS_DATE(),
>>>> label='Date Declared',
>>>> comment='The date the dividend was announced.'),
>>>> Field('DATE_PAY',
>>>> 'date',
>>>> required=True,
>>>> requires=IS_DATE(),
>>>> label='Date Paid/Payable',
>>>> comment='The date on which the dividend was paid by the
>>>> dividend declaring entity.'),
>>>> Field('STC_CREDIT_SHARE',
>>>> 'double',
>>>> required=True,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0.000000,
>>>> 999999999999999.999999)],
>>>> label='STC Credit Per Share',
>>>> comment='''The portion of the dividend per share that should be
>>>> subtracted for STC credit.
>>>> This value must be carried through to the recipient detail.'''),
>>>> Field('MICRO_EXEMPT',
>>>> 'double',
>>>> required=False,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0.00,
>>>> 999999999999999.99)],
>>>> label='Micro Business Exemption',
>>>> comment='The amount that can be claimed as allowable micro
>>>> business exemption.'),
>>>> Field('IS_TAX_PAY',
>>>> 'double',
>>>> required=False,
>>>> requires=[IS_NOT_EMPTY(), IS_FLOAT_IN_RANGE(0.00,
>>>> 999999999999999.99)],
>>>> label='IS Tax Payable',
>>>> comment='The amount of tax paid to SARS in respect of In Specie
>>>> dividends.')
>>>> )
>>>>
>>>>
>>>>
>>>> db.define_table('DT_SARS_RECIPIENT',
>>>> Field('SECTION_ID',
>>>> 'string',
>>>> length=1,
>>>> required=True,
>>>> label='Section Identifier'),
>>>> Field('RECORD_TYPE',
>>>> 'string',
>>>> length=6,
>>>> required=True,
>>>> label='Record Type'),
>>>> Field('RECORD_STATUS',
>>>> 'string',
>>>> length=2,
>>>> required=True,
>>>> label='Record Status'),
>>>> Field('UNIQUE_NUM',
>>>> 'string',
>>>> length=100,
>>>> required=True,
>>>> label='Unique Number'),
>>>> Field('ROW_NUM',
>>>> 'integer',
>>>> required=True,
>>>> label='Row Number'),
>>>> Field('TRANS_DUE_DATE',
>>>> 'date',
>>>> required=True,
>>>> requires=IS_DATE(),
>>>> label='Transaction Due Date'),
>>>> Field('DIV_UNIQUE_NUM',
>>>> 'string',
>>>> length=100,
>>>> required=True,
>>>> label='Dividend Unique Number'),
>>>> Field('DIV_TYPE',
>>>> 'string',
>>>> length=2,
>>>> required=True,
>>>> label='Dividend Type'),
>>>> Field('PERSON_LIABLE',
>>>> 'string',
>>>> length=2,
>>>> required=True,
>>>> label='Person Liable For Tax'),
>>>> Field('DIST_TYPE',
>>>> 'string',
>>>> length='2',
>>>> required=True,
>>>> label='Distribution Type'),
>>>> Field('NATURE_OF_PERSON',
>>>> 'string',
>>>> length=33,
>>>> required=True,
>>>> label='Nature Of Person'),
>>>> Field('FIRST_NAMES',
>>>> 'string',
>>>> length=100,
>>>> label='First Names'),
>>>> Field('SURNAME_REGNAME',
>>>> 'string',
>>>> length=125,
>>>> label='Surname / Registered Name'),
>>>> Field('TRADE_NAME',
>>>> 'string',
>>>> length=150,
>>>> label='Trading Name'),
>>>> Field('TAX_RES',
>>>> 'string',
>>>> length=3,
>>>> label='Tax Residency'),
>>>> Field('ID_TYPE',
>>>> 'string',
>>>> length=3,
>>>> label='Identification Type'),
>>>> Field('ID_NUM',
>>>> 'string',
>>>> length=30,
>>>> label='Identification Number'),
>>>> Field('CNTY_ISSUE',
>>>> 'string',
>>>> length=3,
>>>> label='Country of Issue'),
>>>> Field('INC_TAX_REF',
>>>> 'integer',
>>>> label='Income Tax Reference Number'),
>>>> Field('DOB_INC',
>>>> 'date',
>>>> label='Date Of Birth\/Inception'),
>>>> Field('EXEMPT_CLAIM_PAR',
>>>> 'string',
>>>> length=1,
>>>> label='Exemption Claimed Under Par'),
>>>> Field('DTA_RATE_REQ',
>>>> 'string',
>>>> label='DTA Reduced Rate Requirements Met'),
>>>> Field('DATE_PAY',
>>>> 'date',
>>>> label='Date Paid\/Payable'),
>>>> Field('NUM_SHARES',
>>>> 'integer',
>>>> label='Number Of Shares'),
>>>> Field('DIV_SHARE',
>>>> 'integer',
>>>> label='Number Of Dividends Per Share'),
>>>> Field('DIV_VALUE',
>>>> 'integer',
>>>> label='Dividend Value'),
>>>> Field('STC_SHARE',
>>>> 'string',
>>>> length=22,
>>>> label='STC Credit Per Share'),
>>>> Field('MICRO_EXEMPT',
>>>> 'string',
>>>> length=18,
>>>> label='Micro Business Exemption'),
>>>> Field('TAX_RATE_APPL',
>>>> 'string',
>>>> length=5,
>>>> label='Tax Rate Applicable'),
>>>> Field('REBATE_FOREIGN',
>>>> 'integer',
>>>> label='Rebate For Foreign Tax Paid'),
>>>> Field('TAX_WITHHELD',
>>>> 'integer',
>>>> label='Tax Withheld'),
>>>> Field('NET_AMNT_BO',
>>>> 'integer',
>>>> label='Net Amount Distributed To BO'),
>>>> Field('CORP_ACT_ID',
>>>> 'integer',
>>>> label='Corporate Action ID'),
>>>> Field('SIN',
>>>> 'integer',
>>>> length=25,
>>>> label='SIN'),
>>>> Field('SCA_NUM',
>>>> 'integer',
>>>> label='SCA ID'),
>>>> Field('BENEF_ACC_NUM',
>>>> 'integer',
>>>> label='Beneficial Owner Account Number'),
>>>> Field('DOC_REF',
>>>> 'integer',
>>>> label='Document Reference Number'),
>>>> Field('STATUS',
>>>> 'string',
>>>> length=3,
>>>> label='Status'),
>>>> Field('START_DATE',
>>>> 'date',
>>>> label='Start Date'),
>>>>
>>>> )
>>>>
>>>>
>>>> Any idea what I could be missing to satisfy Oracle?
>>>>
>>>> Many thanks
>>>>
>>>> Rakesh
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
