Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBLink problem ORA-22992

398072Jul 28 2005 — edited May 11 2009
Hi all !!

I'm using an active dblink between db1 and db2 named orcl. Database db2 in schema usr has a couple of tables A and B looking like that:

Table A
column id number, primary key
column id_ref, number, not null,
column eventdate date not null
column longfield CLOB

Table B
column id_ref, number, primary key
column description varchar2(256) not null

In database db1 I ran the following query:

SELECT ID, ID_REF, EVENTDATE
FROM A@ORCL

and It works fine... But, If I try to run the following query:

SELECT A.ID, A.ID_REF, A.EVENDATE, B.DESCRIPTION
FROM A@ORCL A
INNER JOIN B@ORCL B
ON (A.ID_REF = B.ID_REF);

I get the following message:

ERROR in line 1:
ORA-22992: cannot use LOB locators selected from remote tables

db1 is an Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production running over solaris

db2 is an Oracle Database 10g Release 10.1.0.4.0 - 64bit Production With the Real Application Clusters option, running over solaris

How can I solve this problem ? Any help will be appreciated

Thanks in Advance

Comments

Solomon Yakobson

And what CREATE TYPE has to do with generating series of numbers? And how generate_series would help eample in link you posted:

with sample as (
                select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
                        --There are more rows in the actual table.
                  from  dual
               )
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  sample,
        lateral(
                select  level part_num,
                        regexp_substr(multipart_lines,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(multipart_lines,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )
/

  PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54

SQL>

SY.

1 person found this helpful
Solomon Yakobson

and if you are on 19C:

create or replace
  function split_multipart_line(
                                p_line varchar2
                               )
    return varchar2
    sql_macro
    is
    begin
        return q'[
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  dual,
        lateral(
                select  level part_num,
                        regexp_substr(p_line,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(p_line,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )]';
end;
/

Function created.

SQL> with sample as (
  2                  select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
  3                  select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
  4                 )
  5  select  l.*
  6    from  sample,
  7          lateral(
  8                  select  *
  9                    from  split_multipart_line(multipart_lines)
 10                 ) l
 11  /


  PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54
         1          1          1          2          3
         1          2          4          5          6
         1          3          7          8          9
         1          4         10         11         12
         2          1         22         33         44
         2          2         55         66         77


11 rows selected.

SQL>

SY.

1 person found this helpful
Mike Kutz

19c+ code. Its also on GitHub

package generate_series
authid definer
as
    /* package of SQL_MACROS for generating series of values
    *
    * @headcom
    */

    /* Generates a series of integers.
    *
    * @param start_value initial integer
    * @param n The number of row to generate
    * @return Series of integers
    */
    function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE);

    /* Generates a series of integers.
    *
    * @param start_value initial date
    * @param n The number of row to generate
    * @return Series of Dates
    */
    function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table);
end;
/

create or replace
package body generate_series
as
    function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE)
    as
    begin
        return q'[select of_numbers.start_value + (level - 1) number_series
        from dual
        connect by level <= of_numbers.n]';
    end;

    function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table)
    as
    begin
        return q'[select of_dates.start_value + (level - 1) date_series
        from dual
        connect by level <= of_dates.n]';
    end;
end;
/
2 people found this helpful
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 8 2009
Added on Jul 28 2005
#11g, #database-key-features, #error, #oracle, #query, #replication
5 comments
76,362 views