Is the database (oracle) already existing or you create a new one? Do you leave web2py create and update the schema for you?
If so, you should be aware that certain changes are not apply if they need a change in column type and other possible scenario. If you are the creator of the database and you don't try to implement in web2py the schema of a legacy database, you can try to drop everything at db level and let web2py recreate everything in one step (all your final models definitions). And see if it improves. I mean if you have iterate many time when developping your web2py model, it may happen that the oracle schema is not totally in sync with the web2py schema and that trigger error. Now I am not sure what you are talking about when you talk about trigger... Do you mean database PL/SQL trigger? If so, do you use web2py create those trigger... If so, I think it a new fancy feature, and it may be not fully tested, so it may be part of the problem. I would not use trigger at database level in your first implementation, if you can, to make thing simpler. Since the last error you report is about duplicated field, it may be possible that your trigger has error or behave not as you expect in some situation and generate duplicates. Finally if you are on deadline you should stick with what works (MySQL), make your proof of concept and then you will have approval to port your project to oracle and time to make it works properly. Good luck. Richard On Tue, Apr 24, 2012 at 6:37 AM, Rakesh Singh <[email protected]>wrote: > 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 >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>
