stored procedure strange behaviour

Question

I‘ve got a query like

(SELECT ...
FROM T2
WHERE x=1)

The select returns a list of tupel which are inserted into T1. So long, so good.

As ‚1‘ may change a stored procedure with a parameter does the job here.

BEGIN
    INSERT INTO T1
    (SELECT ...
    FROM T2
    WHERE x=value)
END;

The procedure is called by a Trigger AFTER INSERT on T3.

Running the select on Oracle 12c inserts e.g. two rows into T1. Also running with different values instead of ‚1‘ works fine.

When calling the procedure, although called with different parameters, always inserts the same number of rows with the same values. Looks like it is executed with the same parameter, which it isn‘t. The executiontime of the procedure is about 18 times higher that querying the insert statement directly.

Above Code may not compile, it‘s just for demonstration.

Any idea why the procedure execution taking so long and why it‘s obviously doing sth. else?

Answer 1

Reading what you described, it sounds as if you misused procedure's parameter. Here's an example which shows what I mean.

Let's create a table (just like T1 you have) and a procedure which accepts a parameter and performs insert into that table.

SQL> create table test (deptno number, ename varchar2(10));

Table created.

SQL>
SQL> create or replace procedure p_test (deptno in number)
  2  is
  3  begin
  4    insert into test (deptno, ename)
  5    select deptno, ename
  6    from emp
  7    where deptno = deptno;
  8  end;
  9  /

Procedure created.

I'd like to insert employees who work in department 10:

SQL> select deptno, ename
  2  from emp
  3  where deptno = 10;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER

Fine; I expect 3 rows to be inserted. Let's call the procedure, then:

SQL> exec p_test (10);

PL/SQL procedure successfully completed.

Result:

SQL> select * From test;

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

14 rows selected.

SQL>

Wooops! Not exactly what I wanted.

The culprit is this:

7    where deptno = deptno;

because I named the parameter exactly like column name, so Oracle knows that this condition is always true (as if where 1 = 1) and inserts all rows it finds in the EMP table, as there are no other conditions involved.

What to do? Rename the parameter and repeat the test:

SQL> create or replace procedure p_test (p_deptno in number)     --> here
  2  is
  3  begin
  4    insert into test (deptno, ename)
  5    select deptno, ename
  6    from emp
  7    where deptno = p_deptno;                                  --> here
  8  end;
  9  /

Procedure created.

SQL> truncate table test;

Table truncated.

SQL> exec p_test (10);

PL/SQL procedure successfully completed.

SQL> select * From test;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER

SQL>

Much better, is it not?

---

If that's what you did, now you know how to fix it. If not, post an example just like me so that we could see what you did and how Oracle responded.

---

As of procedure talking time ... well, who knows? Too few info to compute. Trace the session and use TKPROF and you'll know exactly what's going on.