Results 1 to 2 of 2

Thread: Migration to Oracle 11g

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Jun 2009
    Location
    Lima, Perú
    Posts
    663

    Default Migration to Oracle 11g

    Hi Martin,

    I think this theme deserves its own thread. (original thread here)

    Quote Originally Posted by Ivan
    I need to convert an application from embeded to oracle 11g ...
    Quote Originally Posted by Ivan
    The worst trouble was that it can't work with reread (error 25) and findbyrowid ... until I changed the primary key in the int file .
    Seems to be that PRIMARY_INDEX always must be 1
    Quote Originally Posted by Martin Moleman
    Are you using recnum tables or standard tables? With standard tables the Primary_index should be set to 1. Or actually a non zero value.
    Quote Originally Posted by Ivan
    I am using standard tables. My (little) experience says that Primary_index always has to be 1. If I setup as 2 or other number, reread and findbyrowid don't work.
    I have a table with about 10 thousands records that had the Primary_Index as 2 and was in trouble, so I made a test.
    I read all data and saved in an array the rowid and the key fields.
    Then did a random reading of 100 records. No one was found by rowid, only by the key fields, and reread also didn´t work.
    Then I found that changing the primary_index to 1 all records were found by rowid, key fields and reread.
    Quote Originally Posted by Martin Moleman
    Primary index:
    Can you show your table definition? For example a DEF file.
    How are index.1 and index.2 defined? Do they uniquely identify a record? That should be the case to be able to use them as a primary index.
    Here you have the actual DEF and SQL description file in parallel (see attached). All indexes are uniques. After change the primary key to 1, I swapped Index.1 with Index.2 .
    To test if the problem persist I tried changing again the primary_index to 2, it works... until I added a new record. The record was saved (I can see it in Oracle Sql Developer) but when I tried to access it in dbexplorer or in a grid, I got these errors:
    Code:
       c:\program files\visual dataflex 17.0\bin\dbexplor.exe
           Registro no encontrado Find Equal.  Table = KRECURSO (13), Index = KRECURSO002 (2), Source = ODBC_DRV.Find
           Error: 25
           MSG_ERROR_REPORT (433) - OERRORDIALOG (81) - at address 46493
           MSG_READBYROWID (1500) - oKRECURSO_DD (1171) - at address 56675
           MSG_READBYROWID (1500) - ODATATABLE (1180) - at address 20401
           GET_FILL_NEXT_ROW (5940) - ODATATABLE (1180) - at address 19741
           GET_ROW_CHANGING (278) - ODATATABLE (1180) - at address 21205
           MSG_ITEM_CHANGE (144) - ODATATABLE (1180) - in native code
           MSG_ITEM_CHANGE (144) - ODATATABLE (1180) - at address 20104
           MSG_ITEM_CHANGE (144) - ODATATABLE (1180) - at address 22189
           SET_CURRENT_ITEM (14) - ODATATABLE (1180) - in native code
           SET_CURRENT_ITEM (14) - ODATATABLE (1180) - at address 4383
           SET_CURRENT_ITEM (14) - ODATATABLE (1180) - at address 22071
           SET_CURRENT_ITEM (14) - ODATATABLE (1180) - at address 22269
           MSG_DOWN_ROW (5879) - ODATATABLE (1180) - at address 19761
           MSG_KEY (55) - ODATATABLE (1180) - at address 21592
           MSG_ONMOUSEWHEEL (4551) - ODATATABLE (1180) - at address 3806
           MSG_ONWMMOUSEWHEEL (1357) - ODATATABLE (1180) - at address 3794
           [start] - at address 77238
           
           Grid error in Datalist.pkg
             procedure ReadByRowId RowId riRec
               integer srvr# file#
               get Server to srvr#
               get Main_File to file#
               if srvr# ne 0 send ReadByRowId to srvr# file# riRec   <=== error here 
               else send vReadRowId file# riRec
             end_procedure
    Really I am ok with setup the primary_index as 1, but I think there is a problem and I don´t know if it is about the vdf odbc driver or oracle odbc driver.
    Swapping the indexes lead me to another problem... what sentences do we need to search for in the source when this happens? of course index.1, index.2, ordering, some other one?)

    Quote Originally Posted by Ivan
    I had to write a program to transfer data because of lot of errors with "Convert to OBDC" wizard,
    but this was related with language settings. Oracle XE works with unicode and national characters are translated to 2 or 3 bytes.
    Quote Originally Posted by Martin Moleman
    What Oracle ODBC driver are you using? What exact version? In my experience ODBC drivers can sometimes be buggy.
    Quote Originally Posted by Ivan
    I am using Oracle Database 11g Express Edition 11.2.0.2.0 to make testing in my machine and Oracle Database 11g Enterprise Edition 11.2.0.2.0 in Linux as oficial server.
    First I was using odbc driver SQORA32.DLL 11.01.00.06 (03/10/2007) installed by TI in my desktop and 11.02.00.02 (27/08/2011) (comes with Oracle express) and had a lot of troubles.
    The wizard refused 50% of my data when transfered from embeded to oracle database.
    Then I found the 11.02.00.03 driver (30/10/2011) and only refused about 10% (the data with national characters like á ó é í ú ñ)
    This is because of the wizard converts the ASCII fields to CHAR, but with units of BYTE and each special character is converted to 2, 3 or 4 bytes, so the length of the field is exceeded and the transfer is refused.
    Oracle has fields CHAR with units also CHAR and the national characters are counted as 1 despite of the number of bytes they use.
    For Index fields CHAR is a good option (it fills the field with spaces as VDF), but for large text or description fields is better use VARCHAR2 (also with units of BYTE or CHAR).
    Also to see the characters in our language is important set the variable NLS_LANG in the registry.
    When I installed the latest driver, this variable was not setup and the special characters were replaced with a question mark.
    Quote Originally Posted by Martin Moleman
    Maybe you can configure the Oracle database to use a non-unicode codepage?
    Quote Originally Posted by Ivan
    In Oracle XE I can´t. It comes with AL32UTF8, this is setup when the database is created and I can not create databases. I can create only schemas and tablespaces.
    In Oracle Enterprise yes. It was setup to WE8MSWIN1252.
    Quote Originally Posted by Martin Moleman
    Code page:
    From my experience (a while back I must admit) it is a matter of setting up the correct codepage / NLS settings.
    Even when the data is stored as Unicode in Oracle, it should be possible to store and retrieve the data through ODBC CK.
    Yes, it works. The problem is with the wizard. It creates ASCII fields as CHAR with units BYTES and data with national characters exceeds the length of the field.
    I made this BP (see attached) to transfer the data, using the file definition created by the wizard, but changing the most of CHAR fields to VARCHAR2 with units of type CHAR. I continue testing, probably some fields need to returned to CHAR.
    (to be clearer: Oracle has fields CHAR and VARCHAR2 and each one can be of units of type BYTE or CHAR.)

    Thanks again for your answers. I continue testing and will keep you informed.

    Best regards,

    Ivan Schoof
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •