5

I would like to load parallel data from oracle to oracle using dblink and dbms_hs_parallel package. In order to do it I have plsql listed below:

declare
  lInserted number;
begin
  dbms_hs_parallel.LOAD_TABLE('SIEBEL.S_LOY_CARD','SIEBEL', 'BRED_SIEBEL_CARDS', true, 16, lInserted);
  dbms_output.put_line(lInserted);
end;

All DB objects are valid but I get an error

"ORA-00904: "DBMS_HS_PASSTHROUGH"."AGENT_CLASS_NAME": invalid identifier

ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 1097

ORA-06512: at line 4".

I'm pretty sure it fails during an attempt to insert. Because if I have no BRED_SIEBEL_CARDS in my DB, the package creates it. Which means the package does what is expected, but doesn't insert data unfortunately.

Community
  • 1
  • 1
Alexander Tokarev
  • 1,000
  • 7
  • 16
  • What is the DBMS you are loading data from? Is it Netezza? One of possible source of error is inconsistency in ODBC driver realisation ... – ThinkJet Sep 05 '13 at 12:30
  • as I mentioned in the first sentence I load data from 1 Oracle server to another one. dblink speed is not enough due it is single-thread. – Alexander Tokarev Sep 05 '13 at 13:00
  • Ok. Sorry, I missed this introduction phrase. – ThinkJet Sep 05 '13 at 14:13
  • What's very bad Tom Kyte has a backlog so I can't use AskTom :( – Alexander Tokarev Sep 06 '13 at 12:18
  • I searched through MySupport.oracle.com but didn't found any related information ... May be someone can help you on [Oracle forums](https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql) ... – ThinkJet Sep 06 '13 at 13:11
  • 1
    I don't think this will work. According to the documentation for [DBMS_HS_PASSTHROUGH](http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_hspass.htm) "The DBMS_HS_PASSTHROUGH package is a virtual package. It conceptually resides at the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services..." You need to create a heterogeneous service agent (?) for this connection. But I don't think you can do that for Oracle to Oracle connections. Perhaps this question should be rephrased as "how do I perform parallel DML over a database link"? – Jon Heller Sep 07 '13 at 05:18
  • In order to do 'parallel dml' over db link dbms_hs_parallel was created by Oracle. Why it doesn't work - no idea. – Alexander Tokarev Sep 09 '13 at 09:50

1 Answers1

1

I believe Oracle Database docs explain why you got your error:

The remote database link name. The call can only be applied to a heterogeneous services database link.

Source: DBMS_HS_PARALLEL

Oracle DB - Oracle DB is a homogeneous connection, that does not use a Heterogeneous Services Agent, because none is required.

If you don't want to use a regular CTAS/INSERT/MERGE statement, look into dbms_parallel_execute.

piotrp
  • 3,755
  • 1
  • 24
  • 26
  • I see your point but why it creates the table for data? Morover I tryed to check it even with MsSQL - it doesn't work as well (by defferent reason but it doesn't matter so my opinion the package doesn't work at all). – Alexander Tokarev Sep 12 '13 at 08:13
  • You used this procedure in an improper context, so its behavior is undefined - breaking in random places. Oracle may have used plain SELECTs over dblink to get dictionary data, recognizing at one place that it reads from another Oracle database, and then failed when it tried to explicitly use HS Agent to read data (but that's just my guess). – piotrp Sep 12 '13 at 14:58