Cannot see all compile errors in a program window

[Bradnam]

Having problems running a create/replace on a stored procedure (from within a program window) after making amendments....
I get notification that there are issues but all I see is a long list of "PLW-07204: conversion away from column type " errors (20 in all).
Switching to a command window produces the same. If I then run the same code in SQLPLUS on my PC then show errors I get the correct 'failures' e.g.

11/4 PL/SQL: Statement ignored
11/4 PLS-00367: a RAISE statement with no exception name must be
inside an exception handler

17/11 PL/SQL: Statement ignored
17/42 PLS-00201: identifier 'R_AGENT.AGENT_ID' must be declared

Any ideas why these errors are not shown in PL/SQL Developer?

Thanks

 

[Marco Kalter]

What do you see when you query the sys.user_errors view for this object? For example:

select line, position, text
  from user_errors
 where name = '&Name'
   and type = '&Type'
 order by sequence

 

[Bradnam]

Marco,

OK - some time has passed due to the Forum being down but I have tried to simulate the issue; I took a working stored procedure and changed the name of one of the driving cursors and re-compiled (from a program window). No errors shown but the procedure went invalid in the browser window view. Double clicking this showed the list of 'PLW' errors and the code snippet you provided produced the listing below:

LINE POSITION TEXT
107 18 PLW-07204: conversion away from column type may result in sub-optimal query plan
107 57 PLW-07204: conversion away from column type may result in sub-optimal query plan
107 66 PLW-07204: conversion away from column type may result in sub-optimal query plan
107 73 PLW-07204: conversion away from column type may result in sub-optimal query plan
107 12 PLW-07204: conversion away from column type may result in sub-optimal query plan
107 72 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 18 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 57 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 66 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 73 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 12 PLW-07204: conversion away from column type may result in sub-optimal query plan
106 72 PLW-07204: conversion away from column type may result in sub-optimal query plan
150 14 PLW-07204: conversion away from column type may result in sub-optimal query plan
150 40 PLW-07204: conversion away from column type may result in sub-optimal query plan
150 8 PLW-07204: conversion away from column type may result in sub-optimal query plan
150 34 PLW-07204: conversion away from column type may result in sub-optimal query plan
198 50 PLW-07202: bind type would result in conversion away from column type
198 28 PLW-07202: bind type would result in conversion away from column type
204 28 PLW-07202: bind type would result in conversion away from column type
204 47 PLW-07202: bind type would result in conversion away from column type

Now, if I save the procedure script to a file on my C: drive and run in in native SQL Plus (as provided by Oracle) I see this:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 30 09:44:04 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> @c:\carol\carol.sql

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE SP_AGENT_SEND_EMAIL_CAROL1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
281/4 PL/SQL: Statement ignored
281/21 PLS-00201: identifier 'CURSTATUS' must be declared
SQL>

Hope this is of use - Steve

PS - using PL/SQL Developer version 7.1.5.1398 (and connot yet upgrade)

 

[Marco Kalter]

What happens if you query the user_errors view after compiling in SQL*Plus?

This might be a problem with the "debug information" mode. To verify this, go to Tools > Preferences > Debugger tab page, disable the "Add debug information when compiling" option, and retry the compilation.

 

[Bradnam]

Blimey, you're up early.....

Removing the 'Debugger' option from compile made a slight difference - we now get a hint for the real issue (but still get all the PLW errors and it doesn't tell us the real issue i.e. PLS-00201:identifier 'CURSTATUS' must be declared - notice that to force a compile error we removed the 'S' from the cursor name)

Error: Hint: Cursor 'curtatus' is declared but never used in 'sp_agent_send_email_carol1'
Line: 137
Text: CURSOR curtatus

The user_errors view gives

LINE POSITION
---------- ----------
TEXT
--------------------------------------------------------------------------------
107 18
PLW-07204: conversion away from column type may result in sub-optimal query plan

107 57
PLW-07204: conversion away from column type may result in sub-optimal query plan

107 66
PLW-07204: conversion away from column type may result in sub-optimal query plan

107 73
PLW-07204: conversion away from column type may result in sub-optimal query plan

107 12
PLW-07204: conversion away from column type may result in sub-optimal query plan

107 72
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 18
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 57
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 66
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 73
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 12
PLW-07204: conversion away from column type may result in sub-optimal query plan

106 72
PLW-07204: conversion away from column type may result in sub-optimal query plan

150 14
PLW-07204: conversion away from column type may result in sub-optimal query plan

150 40
PLW-07204: conversion away from column type may result in sub-optimal query plan

150 8
PLW-07204: conversion away from column type may result in sub-optimal query plan

150 34
PLW-07204: conversion away from column type may result in sub-optimal query plan

198 50
PLW-07202: bind type would result in conversion away from column type

198 28
PLW-07202: bind type would result in conversion away from column type

204 28
PLW-07202: bind type would result in conversion away from column type

204 47
PLW-07202: bind type would result in conversion away from column type

20 rows selected.

 

[Marco Kalter]

Removing the 'Debugger' option from compile made a slight difference - we now get a hint for the real issue (but still get all the PLW errors and it doesn't tell us the real issue i.e. PLS-00201:identifier 'CURSTATUS' must be declared - notice that to force a compile error we removed the 'S' from the cursor name)

I'm not quite sure what you mean. Do you see the error now, or do you still not see the error?

 

[Bradnam]

Marco,

OK - What I did to force a compile error was change the name of a driving cursor - I have a cursor called "curStatus" and I deleted the "S" to make it "curtatus"; removing the debugger option gave me a hint that the renamed cursor was defined but never used

Error: Hint: Cursor 'curtatus' is declared but never used in 'sp_agent_send_email_carol1'
Line: 137
Text: CURSOR curtatus

but I still see loads of PLW warnings.

What is doesn't tell me is the real reason for the compilation error i.e. i am trying to open a cursor that hasn't been defined. When I run the compile in native SQL-PLUS (and run show errors) I see the error I would expect

SQL> show errors
Errors for PROCEDURE SP_AGENT_SEND_EMAIL_CAROL1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
281/4 PL/SQL: Statement ignored
281/21 PLS-00201: identifier 'CURSTATUS' must be declared

If I run your code snippet I still just get loads of PLW warnings and no mention of the PLS-00201 error....

Does this clarify?

 

[Bradnam]

Marco,

I think i have resolved this to my satisfaction:

There is a sql script in the "Program Files\PLSQL Developer" folder alled "AfterConnect.sql" - this contains the following line:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Changing the ENABLE to a DISABLE gets rid of all the duff warnings and allows the 'real' cause of the compilation error to be displayed.

 

[Marco Kalter]

Okay, thanks for letting me know.

 

[pessimista]

is there a way to increase the number of plsql messages (warnings + errors) to be displayed in the window after the object compilation? I do want to see all the warnings and errors, but if an error occurs after 20 warnings I cannot see the error message...

the session parameter is activated to ALL warning categories. I can see the error message if i changed the session parameter to ENABLE:SEVERE, but I would like to see all kind of warning categories.

thanks in advance.

 

[Marco Kalter]

You can resize the error message pane by dragging the splitter between the editor and the messages.

 

[Claus Pedersen]

This is a feature/bug in the Oracle 10g/11g databases. The Oracle DB compiler will only report the first 20 warnings/errors. When the first 20 messages are all warnings (PLW-...), no error messages are shown at all, but the program unit will still be invalid, and also PLD will not indicate that compilation errors have occurred, only a small text message in the bottom of the screen, that the program unit was compiled with errors. No line is highligted as would normally happen on compilation errors.

You must bite the bullet and use the command
SET PLSQL_WARNINGS='DISABLE:ALL'
in order to see all the compilation errors and then re-enable them again to see the warnings.

Please also refer to an earlier post that also addresses this problem: Request: visual indication of compilation error in program window, even if no error line number

 

[Claus Pedersen]

An idea: if package (or another object) compilation fails, and no line number is reported back from the Oracle compiler, PL/SQL Developer should automatically temporarily turn off PL/SQL warnings (save the old setting) and compile the object again. After compilation, the old value for plsql_warnings should be restored. In this way, the user can always see the 'real' compilation errors.