CODEWITHSUNDEEP
xmloracle-databasejoin
Jonas
Jonas

Reputation: 784

Joining XML and Oracle SQL throws ORA Exception

I try to merge xml code and Oracle SQL code in 1 table. My XML structure looks like

<Statuses>
  <Status id="FS_MAIN" status="Main folder" isException="0"/> 
  <Status id="FS_PROCESSING" status="Processing subfolder" isException="0"/> 
</Statuses>

My sql looks like

select db.status, count(db.id) as "Number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - db.time_event),'hh24:mi:ss')) as "Time in system"
from MyDb db
where db.seq_number = (select max(dm2.SEQ_NUMBER) from MyDb dm2 where dm2.id= db.id) and db.technology = 'Repository'
group by db.status

I use Logi analytics to merge this, I use following structure

Logi structure

To explain this, Logi is a graphical tool to compile graphical charts into html pages.

My Query is the SQL query, I tested and compiled this in Oracle SQL developper, it works.

The statuslayer is the XML-file, I guess here is the problem.

To make a join I want to make a statusMatch on the status values, but this doesn't work when I compile.

I get the following exception:

Logi Debugger Trace Report

There was an error while processing your request.

The error was:

ORA-00942: table or view does not exist

Upvotes: 0

Views: 49

Answers (2)

HWJ
HWJ

Reputation: 77

Maybe you are not connected to the same schema in logi compared to oracle developer? Can you run this query:

SELECT owner, table_name FROM all_tables;

From your logi application and display the results somehow?

That might give you a clue as to what is going on.

Upvotes: 0

massie
massie

Reputation: 556

It looks like your table MyDb is not there or you don't have permission to read from it. ( grant select to your user on the table )

The good thing is you get an ORA error, so you are on the database allready.

Upvotes: 1

Related Questions