Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: unable to remap_data varray type in expdp

    Hi,

    (new here!)

    11g Enterprise Edition Release 11.2.0.3.0

    I've been trying to expdp a table with a varray type column replacing its value using remap_data with a null or empty array in order to reduce data size when imported into another instance. I've had success exporting and importing including remapping several lob columns in the same way but not with the varray column for which I consistently get the error:

    ORA-00907: missing right parenthesis

    Here's some tests:

    Create test user/schema 'test'

    create or replace type test.TestArray as varray(5) of varchar2(10);

    create table test.test (id number, ta test.TestArray);

    insert into test.test values (1, test.TestArray('A', 'B'));

    Remap functions in my dba account:

    CREATE OR REPLACE PACKAGE "SLIDEL"."REMAP"
    as
    FUNCTION nullarray(qa in TestArray) return TestArray;
    end remap;

    CREATE OR REPLACE PACKAGE BODY "SLIDEL"."REMAP"
    as
    FUNCTION nullarray(qa TestArray) return TestArray
    is
    begin
    return(null);
    end nullarray;
    end remap;

    (also tried empty array instead of null)

    expdp param file (test.par):

    USERID=slidel
    DIRECTORY=dump
    DUMPFILE=test.dmp
    VERSION=latest
    SCHEMAS=test
    EXCLUDE=STATISTICS
    COMPRESSION=ALL
    remap_data=test.test.ta:remap.nullarray

    (other exports on same directory are fine)

    Now export:

    expdp PARFILE=test.par

    Results:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SLIDEL"."SYS_EXPORT_SCHEMA_01": slidel/******** PARFILE=test.par
    Estimate in progress using BLOCKS method...
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    ORA-31693: Table data object "TEST"."TEST" failed to load/unload and is being skipped due to error:
    ORA-00907: missing right parenthesis

    Master table "SLIDEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ************************************************** ****************************
    Dump file set for SLIDEL.SYS_EXPORT_SCHEMA_01 is:
    /u01/oradata/dump/test.dmp
    Job "SLIDEL"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at

    I've looked up the ORA-00907 error but can't find anything useful, I've not checked on metalink yet (don't have account details here).

    Anyone any ideas?

    Many thanks,

    Tim

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Remap functions in my dba account:

    You post SQL that nominally creates Functions, but you do not post actual test cases that they can be successfully invoked.

    Either you have a problem or Oracle has a bug. The results are to be determined.
    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
    Apr 2012
    Posts
    2
    Hi anacedent,

    Thanks for looking. Apologies for my hastiness, I'll try again...

    By the way this is "SunOS 5.10 Generic_147441-01 i86pc i386 i86pc"

    Output from sqlplus (script attached as test.txt):

    SQL> set echo on
    SQL>
    SQL> CREATE USER "TEST" IDENTIFIED BY xyz
    2 DEFAULT TABLESPACE "USERS"
    3 TEMPORARY TABLESPACE "TEMP";

    User created.

    SQL> alter user test quota unlimited on users;

    User altered.

    SQL> grant connect, create session, create table, create sequence to test;

    Grant succeeded.

    SQL> create or replace type test.TestArray as varray(5) of varchar2(10);
    2 /

    Type created.

    SQL> create table test.test (id number, ta test.TestArray);

    Table created.

    SQL> insert into test.test values (1, test.TestArray('A', 'B'));

    1 row created.

    SQL>
    SQL> CREATE OR REPLACE PACKAGE "SLIDEL"."REMAP"
    2 as
    3 FUNCTION nullarray(qa in test.TestArray) return test.TestArray;
    4 end remap;
    5 /

    Package created.

    SQL>
    SQL> CREATE OR REPLACE PACKAGE BODY "SLIDEL"."REMAP"
    2 as
    3 FUNCTION nullarray(qa test.TestArray) return test.TestArray
    4 is
    5 begin
    6 return(null);
    7 end nullarray;
    8 end remap;
    9 /

    Package body created.

    ... then use the dump parameters and command line from previous email.

    Hope that's better?

    Tim
    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
  •