Problem with stored procedure in SQL Developer - no "refreshing" the changes made in the block

user_12511801 0
Viewed 770

N.B, the problem I'm facing is not related to the business logic, but rather, with the stored procedured itself. it's a very weird problem I'm facing and I haven't had this kind of problem before.

I'm modifying a stored procedure written in PL/SQL - called "MY_STORED_PROC" - and, each time I change its content, the previous changes still in the result of the execution of the SP.

This is the sample of the stored procedure:

create or replace PROCEDURE MY_STORED_PROC
(
    V_USER IN VARCHAR2 DEFAULT NULL,
    V_NUMBER_PARAM IN NUMBER DEFAULT 0,
    V_ORIGIN IN NUMBER DEFAULT 0
)
AS
    CV_1                    SYS_REFCURSOR; 
    V_SAMPLE                NUMBER;
BEGIN
    SELECT  BAP.APA_CNAME
    INTO    V_USER_DB
    FROM    BH_APPLICATION_PARAM BAP
    WHERE   BAP.APA_NCODE = 84;
        
    INSERT INTO T_DEBUG (ERR_LINE, MESSAGE_INFO)
    SELECT ID, 'EXAMPLE_MESSAGE'
    FROM <my_table> 
    WHERE <ID = 1>;
    
END MY_STORED_PROC;

In the previous structure, I have the EXAMPLE_MESSAGE string which is inserted in T_DEBUG table when this SP is executed and the condition is met.

Now, after change the EXAMPLE_MESSAGE string sample with another text and compile and execute the SP, the message EXAMPLE_MESSAGE still shows in the results.

I don't understand why - if only this SP has the given string sample and the table T_DEBUG is truncated before the SP is called.

What I had tried:

  • Execute DELETE FROM T_DEBUG; and TRUNCATE TABLE T_DEBUG before calling the SP.
  • Execute DROP PROCEDURE MY_STORED_PROC, then execute CREATE OR REPLACE PROCEDURE MY_STORED_PROC (with and without declaring the schema name) with the logic of the procedure changed completely.
  • Compile and "Compile for debug" the SP - i.e: MY_STORED_PROC with and without schema name.

Example:

CREATE OR REPLACE PROCEDURE <SCHEMA>.MY_STORED_PROC ...
CREATE OR REPLACE PROCEDURE MY_STORED_PROC ...

  • Modify the SP for truncate the table T_DEBUG "which contains the string sample" - this action (before the SP makes any action "i.e. insert data in T_DEBUG").

  • Dropping the stored procedure: DROP PROCEDURE MY_STORED_PROC; and execute: CREATE OR REPLACE PROCEDURE <SCHEMA>.MY_STORED_PROC ....

  • Check the T_DEBUG table and it does not contain any trigger - it's a single table with no additional features or characteristics (like indexes, triggers, back-up table(s), etc).

  • Compile the (SP) with the following intentional exception:

     -- Generate intentional DivisionByZero unhandled exception: 
 /*SELECT 1/0 
 INTO V_SAMPLE 
 FROM DUAL;*/

In the later point, the exception ORA-01476 is raised and, in my opinion, the SP is taking the changes, but, after all these changes, I cannot explain why the sample string stays (even changing the content of the SP).

  • In (30/10/2020) I also added: 'SAMPLE - ' || TO_CHAR(SYSDATE) as a string sample, then removed, then compile the SP - without this code - and I still got this sample string with date of 30/10/2020 - date when I added such sample, despite the fact I executed this SP at 05/11/2020.

Is there any way to "refresh" the stored procedure or what other tests can be made for refresh this stored procedured?

I'm not the database administrator of this database, but, I could get more information for share with the DBA - I already explain my problem to they, but, no help has been provided.

plsql oracle sql
3 Answers

Okay, that does sounds weird. There should be no "refresh" required, the stored procedure is stored in the database and that's the one that gets executed. As long as you aren't using EBR then there's no risk of different versions being called as long as you are executing exactly the same procedure.

The most likely explanation is some silly mistake that you've overlooked, so start by simplifying your procedure further. You've already confirmed with the error message that it is being called each time. Remove the other parts of your code so it's just the insert into t_debug statement, maybe make it so that it selects from dual rather than your other table with a filter. Remove the arguments for the procedure. Try inserting into a different new table (maybe you have a trigger). If you still manage to replicate the behaviour when you have simplified it to:

drop table my_table;
create table my_table (my_string varchar2(200));
create or replace procedure my_proc
is
begin
  insert into my_table (my_string) 
  select 'error 1' from dual;
end;
/
exec my_proc 
select * from my_table;
delete my_table;
create or replace procedure my_proc
is
begin
  insert into my_table (my_string) 
  select 'error 2' from dual;
end;
/
exec my_proc
select * from my_table;

Then there's a problem.

user_98561760

I think the two most likely causes are:

  1. you are changing the code in an IDE,but forgetting to 'compile' it.
  2. you have the same procedure in two different schemas, and you are not executing the same one you are editing.
user_57325370

I finally could solve this problem.

The problem was data-related; the database itself has too many tables and it's complex, so, I had to generate a full test of the program - after changing the text sample in the stored procedure MY_STORED_PROC to:

'Testing in LBASPOC - DATE: ' || TO_CHAR(SYSDATE, 'dd/MM/yyyy HH:mi:ss')

The result was as follows:

Testing in LBASPOC - DATE: 09/11/2020 10:11:30

When I generated more testings, new records were added in T_DEBUG table - and the DATE value did change in each test results:

Samples:

Testing in LBASPOC - DATE: 09/11/2020 10:12:29
Testing in LBASPOC - DATE: 09/11/2020 10:21:55
Testing in LBASPOC - DATE: 09/11/2020 10:37:41

I then discovered that - in other parts of the program (i.e the database) - another records were added (in a table called "T_OBSERV") - those records were based on records from table "T_DEGUB".

The solution was: modify the SP "MY_STORED_PROC" for "before do any action" - delete the duplicated records in "T_OBSERV".

I was very sure the problem were in the SP itself, but, the problem was really in the duplicated data.

user_125118010
Related