0

My situation is quite strange, and i don't have any ideea on how to handle it. Scenario:

In variable v_tables_param I have the following string (the names of the tables that iwant to export) 'IN(''REPORT_PERIOD'',''OBJECT_AVAILABILITY'')'.

when i try to specify the following metadata filter that i need in order export the tables :

DBMS_DATAPUMP.METADATA_FILTER(handle => n_h1, name =>'NAME_EXPR',value =>v_tables_param);

i get a ORA-39001: invalid argument value.

However, if i hrad code the exact value of v_tables_param into the metadata filter, it works like a charm :

DBMS_DATAPUMP.METADATA_FILTER(handle => n_h1, name =>'NAME_EXPR',value =>'IN(''REPORT_PERIOD'',''OBJECT_AVAILABILITY'')');

Any idea what is happening here?

Are there some weird scenarios in oracle when a hard coded string is different from a variable that has the same value ?

EDIT: I added the function that computes the value of v_tables_param

FUNCTION SPLIT_TABLES(
    v_tables_list   VARCHAR2  --this is a string that looks like "table1,table2,table3"
) RETURN VARCHAR2 
IS
    n_idx               PLS_INTEGER;
    n_i                 PLS_INTEGER := 0;
    v_tables            VARCHAR2(2000) := v_tables_list;
    v_filter_value      VARCHAR(2000);
    v_current_table     VARCHAR2(200);
BEGIN

  v_filter_value := '''IN(';

  LOOP
    n_idx := instr(v_tables,',');
    IF n_idx > 0 THEN
      v_current_table := (substr(v_tables,1,n_idx-1));
      v_filter_value := v_filter_value || '''''' || v_current_table || ''''',';
      v_tables := substr(v_tables,n_idx+1);
      n_i := n_i + 1;
    ELSE
      v_current_table := v_tables;
      v_filter_value := v_filter_value || '''''' || v_current_table || ''''')''';
      EXIT;
    END IF;
  END LOOP;

  RETURN v_filter_value;

END SPLIT_TABLES;
Teshte
  • 624
  • 1
  • 7
  • 26
  • Are you setting the variable as `v_tables_param := 'IN(''REPORT_PERIOD'',''OBJECT_AVAILABILITY'')';`? – Alex Poole Jun 14 '13 at 12:13
  • not exactly, the process is more complex. What i mean by this is that i have in a configurable table, a row which contains the names of the tables that need to be exported, separated by comma. Ex: `Table1,Table2,Table3,Table4`. This value is then parsed to create the above mentioned string by you. If you want i can give that code too – Teshte Jun 14 '13 at 12:20
  • updated my answer below – HAL 9000 Jun 17 '13 at 06:24

2 Answers2

1

Ther're actually more parameters to metadata_filter (nice example of a doc bug):

SQL> desc dbms_datapump.metadata_filter
Parameter   Type     Mode Default? 
----------- -------- ---- -------- 
HANDLE      NUMBER   IN            
NAME        VARCHAR2 IN            
VALUE       VARCHAR2 IN            
OBJECT_PATH VARCHAR2 IN   Y        
OBJECT_TYPE VARCHAR2 IN   Y        
HANDLE      NUMBER   IN            
NAME        VARCHAR2 IN            
VALUE       CLOB     IN            
OBJECT_PATH VARCHAR2 IN   Y        
OBJECT_TYPE VARCHAR2 IN   Y

...and I believe you'll have to qualify the object type you're filtering for:

object_type => 'TABLE'

update after you provided the function source:

Remove two apostrophes from each side of the filter values.

Double apostrophes are required by the compiler only. The value of the filter parameter must contain single apostrophes, but your function creates them in pairs.

HAL 9000
  • 3,877
  • 1
  • 23
  • 29
  • I think it's optional; it'll match any object with that name if you don't specify, won't it? But doesn't explain why it works with a hard-coded string with exactly the same text. – Alex Poole Jun 14 '13 at 15:52
  • @hal9000 you mean these ` v_filter_value := v_filter_value || '''''' || v_current_table || ''''',';` ? – Teshte Jun 17 '13 at 07:36
  • exactly. Also here: v_filter_value := '''IN('; – HAL 9000 Jun 17 '13 at 08:30
  • @hal9000 please edit your post and leave only the correct answer :) – Teshte Jun 17 '13 at 09:19
-2

your variable should contain only single quote

variable = 'IN('REPORT_PERIOD','OBJECT_AVAILABILITY')'

then, pls try , it works

user7500596
  • 1