SQL Nav 6.5 doesn't understand RELIES_ON clause

SQL Navigator
1

If package body contains result_cache function with RELIES_ON () clause then SQL Nav 6.5:

  1. incorrect displays the package body structure in Outline window;
  2. compiles the package body with error ORA-00900.
2

Hi Nicolay,

I was not able to reproduce the issue. Could you please provide an example?

Note that RELIES_ON clause makes sense only in 11g R1, R2 takes care of the dependencies automatically.

Regards,
Roman

3

Sorry, RELIES_ON clause should contain more one data_source element.
Note. It depends what do you want exactly, for example you select from 3 tables but want that cache should depends from 2 tables only.

4

Hi Nicolay,

We are confident that we used the correct syntax to test this issue and RESULT_CACHE didn’t cause any problems to the Outline or the compilation error you mentioned, with or without the RELIES_ON clause. That’s why we requested for more information from you.

My note about RELIES_ON clause was simply referring to the following paragraph from Oracle documentation (and has nothing to do with your issue):

Note: The RELIES ON clause is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.

Regards,
Roman

5
  1. In SQL Nav File -> Open File x_pkgb.sql, click on Outline. SQL Nav doesn’t show anything before procedure p
  2. File -> Open File x_pkgs.sql, Press F8, move in tab x_pkgb.sql, Press F8, SQL Nav says ORA-00900.

About Note, I know this paragraph from Oracle documentation but it related to 11.2 only, but

  1. Does it mean that SQL Nav should be used with Oracle DB 11.2 or above?
  2. If you references 3 or more tables in function definition but you cache depends from only 2 tables if you don’t specify that the cache function will be depends from all used tables. But this is question to Oracle Corp.
    x_pkgs.sql (70 Bytes)
6
  1. In SQL Nav File -> Open File x_pkgb.sql, click on Outline. SQL Nav doesn’t show anything before procedure p
  2. File -> Open File x_pkgs.sql, Press F8, move in tab x_pkgb.sql, Press F8, SQL Nav says ORA-00900.

About Note, I know this paragraph from Oracle documentation but it related to 11.2 only, but

  1. Does it mean that SQL Nav should be used with Oracle DB 11.2 or above?
  2. If you references 3 or more tables in function definition but you cache depends from only 2 tables if you don’t specify that the cache function will be depends from all used tables. But this is question to Oracle Corp.
    x_pkgb.sql (410 Bytes)
7

Hi Nicolay,

Thank you for the provided example. I can see the problem now. Our parser gets confused because of the comma in the RELIES_ON clause. When I specify only one object in the list, it works fine. I will raise a CR and will let you know when it’s going to be addressed. In the meantime, a workaround is to run the script with the RELIES_ON clause removed or reduced to one item, and then open the package and adjust the list.

Hope this helps,
Roman

8

CR 14271 has been raised