All geek questions in one place

A table or view does not exist. Oracle complains about a comma, not the actual table or view name

I have never seen this before ... I have a query that starts as follows:

with q1 as (select a.V_ID, a.D_ID, a.C_ID, case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val, a.C_val from ab_a_table a where a.C_ID = '00000003' -- '00000007' -- test values and a.B_VAL = '6010001' and aQ = '11234567') select case when ... /* rest of query omitted */

When I try to run this, Oracle complains about this table or view does not exist . But it highlights the "," in line 3, and not the actual table / view name:

  case when a.percent> 0 THEN 'Y' ELSE 'N' end L_VAL, *
 ERROR on line 3:
 ORA-00942: table or view does not exist

The rest of the missed request is quite long and complicated - I will sanitize and publish it if necessary - now I will just say that this error only started after adding the third subquery referred to by q1 . Actually, it seems that I can delete any of the three subqueries, and all this will succeed (albeit with incorrect results), so it seems to me that I hit some kind of Oracle error, and not a pure SQL error. It is also interesting that I can run the q1 body as a standalone request, and I have no problem when I do this. Only when I run the entire query does it complain about the comma after the case in q1 .

Has anyone ever experienced this?

(using Oracle 10g).

Edit: tried the added AS keyword. Results:

  case when a.perc_fault> 0 THEN 'Y' ELSE 'N' end AS L_VAL, a.C_VAL
                                                                      *
 ERROR at line 3:
 ORA-00942: table or view does not exist

It looks like the asterisk is in the same position, but under V , because the word L_VAL been shifted by 3 characters. Very strange...

+4
source share
2 answers

Assuming you hit Oracle errors and cannot fix the database, you can try moving the subquery to a function. Not quite sure if this will work, and suppose your version of PL / SQL is in a package or there is one available that can add a function:

In the package specification:

 type q1_rec is record( d_id ab_a_table.v_id%TYPE, v_id ab_a_table.d_id%TYPE, c_id ab_a_table.c_id%TYPE, l_val char(1), c_val ab_a_table.c_val%TYPE); type q1_arr is varray(9999); -- assuming you can pick a max size function q1 return q1_arr pipelined; pragma restrict_references(q1, wnds);

In the package body:

 function q1 return q1_arr pipelined is cursor c is select a.V_ID, a.D_ID, a.C_ID, case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val, a.C_val from ab_a_table a where a.C_ID = '00000003' -- '00000007' -- test values and a.B_VAL = '6010001' and aQ = '11234567'); begin for r in c loop pipe row(r); end loop; end;

And then in your main query, replace the table(q1()) subquery table(q1()) .

Using a ref cursor or a nested table may be a little tidier, but it will require a table type built in outside the package, which I suppose you want to avoid based on comments on additional objects about using the view.

0
source

I do not know for sure if I have experience with Oracle bug 5130732, but he is sure that it looks like. Anyway, I rewrote the request as follows:

 select case ... from (select ... from (select a.V_ID, a.D_ID, a.C_ID, case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val, a.C_val from ab_a_table a where a.C_ID = '00000003' -- '00000007' -- test values and a.B_VAL = '6010001' and aQ = '11234567') q1, <other tables> where ...) subquery1, (select ... from (select a.V_ID, a.D_ID, a.C_ID, case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val, a.C_val from ab_a_table a where a.C_ID = '00000003' -- '00000007' -- test values and a.B_VAL = '6010001' and aQ = '11234567') q1, <other tables> where ...) subquery2, (select ... from (select a.V_ID, a.D_ID, a.C_ID, case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val, a.C_val from ab_a_table a where a.C_ID = '00000003' -- '00000007' -- test values and a.B_VAL = '6010001' and aQ = '11234567') q1, <other tables> where ...) subquery3, <other tables> where....

Yes, I included a copy of q1 in every subquery that used it, and now everything works fine. A real look would also work, but it was easier (politically, that is, no requests to promote the code to the environment where the analysis should be performed, there are no meetings about a late added object in the database, etc.)

UPDATE

And now, when I added the query to my PL / SQL script, Oracle gives me ORA-00600 [qcscpqbTxt], [600] , which seems to be related to Oracle error # 5765958 .... * sigh * ... Maybe someone Would you like to suggest a workaround? I do not have access to metalink (well, I can, through DBA, if it can somehow get on their radar).

0
source

Source: https://habr.com/ru/post/1347737/

More articles:


All Articles