How to find the error causing ora-06575?

Question

I recently had to write an oracle function, and error ora-06575 arose a lot. Usually this was due to the lack of a colon from the destination, for example: z = 5 (versus z: = 5) Or missed ';' at the end of the statement.

In any case, I managed to create a function, but it failed at runtime with this error and did not indicate where the problem was (except that it was in the function).

I wrote the same function in MSSQL and Sybase, and both of them actually tried to tell me the location of any errors. Therefore, I suppose that I am doing something wrong in Oracle - it cannot just tell me "there is an error."

In oracle, I have an instruction like this:

CREATE OR REPLACE FUNCTION... 

I compile a function from an SQL developer by selecting a function and pressing F9. When I select the operator that performs this function and press F9, I get ora-06575 error.

If I press F5 to compile the function, it tells me:

 ORA-24344: success with compilation error Compiled. 

So, I found this site: http://www.dba-oracle.com/t_ora_24344_success_with_compilation_error.htm But I can not run "show errors". When I run it, I do not have the output that I see.

Can this only work with sqlplus? I am using a SQL developer, I would rather stick with an SQL developer. Is there something I'm missing? I want him to tell me where the mistake is.

Answer 1

SHOW ERRORS is a sql * plus command
You can also request a USER_ERRORS

 SELECT line, position, text FROM user_errors WHERE name = '<<your_object_name>>' 

Answer 2

SHOW ERRORS works in SQL * Developer too (at least in the versions I used recently, of course 3.1). You mentioned in a comment that you are connected as SYS, so I really hope that you explicitly create your function in a different circuit - I would generally avoid this just in case you forget one day and make changes to the previously prepared circuit (SYS, SYSTEM etc.) is a bad idea. If this is the case, you also need to prefix the object with an error using the scheme:

 create or replace function my_schema.x return number is begin return sysdate; end; / show errors my_schema.x 

When run as a script (F5), it says:

 FUNCTION X compiled Warning: execution completed with warning 3/8 PLS-00382: expression is of wrong type 3/1 PL/SQL: Statement ignored 

The first two lines of output come from compiling the function, the last two from SHOW ERRORS . You can also run two statements separately with F9 to see the same results.

But I'm not sure how you get the ORA-24344 , so maybe you are using an earlier version; and it is possible that this will not work. The ABCade solution will work regardless of your customer.