Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010

    Unanswered: Database Link to MS Access Memo

    Dear All,

    I am new to database link. I am now having a problem that I could not convert the Memo field in a MS Access database to a clob in a function so that I could create type using the field.

    My code is as follows:

    1. create or replace type my_type as object (key number, o clob);

    2. create or replace type my_table_type as table of my_type;

    3. create or replace function return_table
    return my_table_type as my_table my_table_type;
    select cast(multiset(
    select key, memo_field from my_access_table@ms_access
    where key < 10
    ) as my_table_type) into my_table from dual;
    return my_table;
    end return_table;

    I have tried to use the following:
    select key, to_lob(memo_field) from my_access_table@ms_access
    where key < 10

    But error message shown: "ORA-22992: cannot use LOB locators from remote tables"

    It seems that the default datatype from the Memo field is long. However, long cannot be used as a type in creating the type. Is there anything I misssed or was wrong? Any suggestions are appreciate. Thanks a lot.


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Microsoft Access Questions

    "How do I migrate the Microsoft Access Memo columns in tables?

    The Oracle ODBC driver automatically handles both BLOB and CLOB columns. By default, all Microsoft Access Memo fields are mapped to CLOB datatypes in an Oracle database. You can use the Microsoft Access front end with the destination Oracle database. Unlike LONG columns, from Oracle 8.x onwards you can have any number of LOB columns in a table. You can have either internal LOBs, meaning they are inside the database, or external LOBs, meaning they are in a file on the file system but under the database's transactional control. The advantages of LOBs are that you can replicate them or index them using the context option."
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Dear anacedent,

    Thanks for your reply. However, it seems that I am now having the MS-Access Memo field's datatype in the Oracle as long but not as the link quoted as LOB. Because I have tested that as follows:

    select dbms_lob.substr(memo_field, dbms_lob.getlength(memo_field)) from my_access_table@ms_access where key < 10

    But error mesage shown: ORA-00997: illegal use of LONG datatype

    How could I have the MS-Access Memo field's datatype as CLOB or BLOB?

    FYI. My Oracle database is 10g. Thanks in advance.


Posting Permissions

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