This discussion is archived
5 Replies Latest reply: Feb 1, 2013 1:28 PM by JimTRjr RSS

Hiearchical Queries and database links

JimTRjr Newbie
Currently Being Moderated
I'm having an issue how the sql parser is treating my hiearchical query across a dblink. Right now, I'm still developing in Toad in my APEX database, however this is not really an APEX issue, but more of a general database question.

I can write a query in my main application database that works. I can write queries in my APEX database with links to my parent database that work just fine. When I try to tackle a working hiearchical query (one with start with and connect by syntax) from my parent database, copy into my APEX DB, add the appropriate dblinks to all the table references, the query errors.

When I run an explain plan, reveiw the associated parced SQL, the parcer is not correctly identifying my alias' and throws an error that a column is not in the table. I tried removing all alias that I could, but the problem is I have two joined tables that have the same column names in my base query, so one at least one of needs to be aliased. The pasrser is not recognizing the alias. The parcer will reassign is own alias as A10, A11, etc..., but when it tries to find A10.aliased_column, it throws the error.

Any advice using hiearchical queries across a DBlink?

Edited by: JimTRjr on Feb 1, 2013 2:52 PM
  • 1. Re: Hiearchical Queries and database links
    sb92075 Guru
    Currently Being Moderated
    JimTRjr wrote:
    I'm having an issue how the sql parser is treating my hiearchical query across a dblink. Right now, I'm still developing in Toad in my APEX database, however this is not really an APEX issue, but more of a general database question.

    I can write a query in my main application database that works. I can write queries in my APEX database with links to my parent database that work just fine. When I try to tackle a working hiearchical query (one with start with and connect by syntax) from my parent database, copy into my APEX DB, add the appropriate dblinks to all the table references, the query errors.

    When I run an explain plan, reveiw the associated parced SQL, the parcer is not correctly identifying my alias' and throws an error that a column is not in the table. I tried removing all alias that I could, but the problem is I have two joined tables that have the same column names in my base query, so one at least one of needs to be aliased. The pasrser is not recognizing the alias. The parcer will reassign is own alias as A10, A11, etc..., but when it tries to find A10.aliased_column, it throws the error.

    Any advice using hiearchical queries across a DBlink?

    Edited by: JimTRjr on Feb 1, 2013 2:52 PM
    based upon the plethora of provided details, I can conclude that the source of the error is either an Oracle bug or Problem Exists Between Keyboard And Chair.
  • 2. Re: Hiearchical Queries and database links
    JimTRjr Newbie
    Currently Being Moderated
    I'm leaning toward the bug side, verses the keyboard and chair.

    Edited by: JimTRjr on Feb 1, 2013 3:06 PM
  • 3. Re: Hiearchical Queries and database links
    sb92075 Guru
    Currently Being Moderated
    JimTRjr wrote:
    I'm leaning toward the bug side, verses the keyboard and chair.

    Edited by: JimTRjr on Feb 1, 2013 3:06 PM
    then post reproducible test case so we can test it ourselves.

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 4. Re: Hiearchical Queries and database links
    JimTRjr Newbie
    Currently Being Moderated
    Okay, I understand there is missing data in my posts. Again, I am a newbie.

    I went back to basics and tried to recreate the issue using a very basic example from APEX setup tables:
    Since deptno exists in both tables, I had to alias the second one. I created a dblink to my db with the standard oracle tables and of course it works...+

    SELECT level, e.empno, e.ename, e.job, e.mgr, e.deptno, d.deptno as "DEPT_NO"
    FROM emp@htmldb_reid e,
    dept@htmldb_reid d
    Where e.deptno = d.deptno
    START WITH mgr IS NULL
    CONNECT BY PRIOR empno = mgr

    The error I'm getting is :
    ORA-00904: "A10"."CHILD_LINK": invalid identifier
    ORA-02063: preceding line from WCRPT

    A subset of the pasrsed version looks like this from the explain plan for the REMOTE call:
    ....
    "A10"."IDA3A5",
    "A10"."IDA3B5",
    "A10"."IDA2A2"
    FROM ( SELECT TRIM (
    MAX (
    LPAD (
    TRANSLATE ("A20"."VERSIONIDA2VERSIONINFO",
    '0123456789',
    ' '),
    3,
    ' ')))
    "VW_COL_1",
    "A20"."IDA3MASTERREFERENCE" "ITEM_0"
    FROM "BAEPART" "A20"
    WHERE "A20"."STATESTATE" <> 'CANCELLED'
    AND "A20"."LATESTITERATIONINFO" = 1
    GROUP BY "A20"."IDA3MASTERREFERENCE") "A12",
    "BAEPART" "A11",
    "WTPARTUSAGELINK" "A10"
    ...

    "CHILD_LINK" does not exist as a column in the "A10" table above... IDA2A2 exists in both A10 and A11, so I have to allias one of them.

    Since my mini-example works, maybe I can work backwards and figure it out. Appears a basic hiearchical can go across a dblink...
  • 5. Re: Hiearchical Queries and database links
    JimTRjr Newbie
    Currently Being Moderated
    Okay, I was able to track down my issue.

    I was using a inline view for my FROM clause, but referencing Hiearchical syntax like LEVEL and SYS_CONNECT_BY_PATH at the higher level.

    Once I got rid of the upper level and made a single hiearchical view, the parser was happy. It makes since that it ran at the local level fine, becasue it didn't have to do another level of parcing for the remote call until the dblink was brought into the picture.

    Thanks for those that took the time to read. - Jim

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points