Toad World® Forums

Strange statement issued (SQL Monitor debug)


#1

Hi

while trying to help Dominique in another thread (http://sqlnavigator.inside.quest.com/thread.jspa?messageID=43183&#43183) I came across the following statement issued by the build 1498 when I make a

select 1 from dual

(see above screenshot)

with SQLNav 5.5.4 and 6.1.1 I do not get the that error.
SELECT dual.,ROWID FROM dual
“Runtime error occurred: 1747 (ORA-01747: …)” invalid specification of table.column

Update: I get that for every statement that has literal in the select expression.

Thanks
Filipe

Message was edited by: Fsilva
image005.png


#2

Hi again

another situation (I think it’s related) with an example

I will use the objects used in team_coding for the example

SELECT psl.psl_object_id
, vcs.filename
FROM sqlnav.sqlnav_program_status_log psl
, sqlnav.sqlnav4_psl_vcs_info vcs
WHERE vcs.psl_object_id = psl.psl_object_id
AND psl_frozen = ‘N’
AND psl_checked_out = ‘Y’

give in SQL Monitor (I formated the output)

Timestamp: 15:42:25.305
SELECT psl.psl_object_id,
psl.filename, ROWID
FROM sqlnav.sqlnav_program_status_log psl
, sqlnav.sqlnav4_psl_vcs_info vcs
WHERE vcs.psl_object_id = psl.psl_object_id
AND psl_frozen = ‘N’
AND psl_checked_out = ‘Y’
Execution time: 0 ms
Runtime error occurred: 918 (ORA-00918: coluna definida de modo ambíguo)

Timestamp: 15:42:25.337
SELECT psl.psl_object_id
, vcs.filename
FROM sqlnav.sqlnav_program_status_log psl
, sqlnav.sqlnav4_psl_vcs_info vcs
WHERE vcs.psl_object_id = psl.psl_object_id
AND psl_frozen = ‘N’
AND psl_checked_out = ‘Y’
Execution time: 0 ms

Timestamp: 15:42:25.395
begin dbms_output.get_line(:line,:status); end;
line =
status = 1
Execution time: 0 ms

Two Problems with that first statment: ROWID (the one that give the ORA-918) and changed the table alias used in filename to psl.
That would give also an error even if the ROWID was not there (due that the column do not exists in the others table, but if that column existed the result would be wrong)

Regards
Filipe


#3

Hi Filipe,

I think you should not be too worried about that. Yes indeed, SQL Navigator issues an invalid SQL statement when you try to execute a non-updateable statement (e.g. containing expressions) in updateable mode. After it fails, Nav re-executes the original SQL (i.e. non-updateable). We checked the code and can confirm that this shouldn’t cause any problems. We will make some improvements in the future so that when expressions are present, Nav won’t even try to generate and execute an invalid statement. So thanks for pointing this out and please ignore this issue for the time being.

Regards,
Roman


#4

Hi Roman

You were right! It only happens in update mode.
I find it odd that for second statement it changed the alias…ok a query for 2 tables is not updatable but if the table has the also columns named as the other maybe even changing the alias will produce an executable query (wrong one, but executable).
Am I wrong? Will that not make that statemente updatabled (but to the wrong table?)

Thanks
Filipe


#5

Hi Filipe,

There is a restriction for Updateable queries in Nav. For queries that contain JOIN clause like this one, it won’t be made updateable to the wrong table.

Gwen