oracle
Why does my procedure with syntax errors is executing with success ?
I have some procedures in .sql files, I'm reading these files and executing them through execute() method, these scripts are basically ( CREATE OR REPLACE PROCEDURE). The problem is: Even when there are some syntax errors the script is executed with success, is There way to get the errors instead of success? Thanks everybody already.
@gspaiva The object will still be created, just in an invalid state.
Given the following procedure:
create or replace procedure test
as
l_number number;
begin
select 1
into l_number
from table_that_does_not_exist;
end;
/
You can get the compilation errors with:
select *
from user_errors
where name = 'TEST'
and type = 'PROCEDURE'
order by sequence;
You can check the status with:
select status
from user_objects
where object_name = 'TEST'
and object_type = 'PROCEDURE';
So this seems fine if you know the script you are running. But what if you are running n number or arbitrary statements. For example you have to compile multiple packages or procedures.
Could there be a way to force a compilation error to show up as warnings. Kind of similar to the show errors command in sqlplus? Or something like oracledb.warningsAsErrors = true.
Or even a more generic query to get the result of the last thing you ran?
@bill-kitsune good question, and one on our list to look at. Our rough thoughts were to have an execute() property like the one you mention, and also a boolean flag in the Error structure to indicate whether the error was a real error or SUCCESS_WITH_INFO. Would this work?
@cjbj It would be a great way to help us to get warning messages or even success with info messages. Already waiting for that.
@cjbj yeah I think that would be great, it would certainly suite my use case pretty well. Should I make a feature request issue for this? Does one already exist?
@bill-kitsune I changed this issue to an enhancement so you can track it. I already had it noted on the big todo list I keep.
@bill-kitsune @gspaiva Good news that you have been waiting for!
We have introduced the warning property to report PL/SQL compilation failures and password being in grace period warnings in node-oracledb 6.3.
Please check the PL/SQL warning and connection warning documentation for more information.
Some sample code is available in test/passwordExpiryWarning.js and test/plsqlWarnings.js. Will update the examples folder also.
