Stored procedure never ends

[ceccina]

Hi, I have Oracle 8.1.7
I defined a store procedure 'A' which call another stored procedure 'B'.
If I execute procedure B and I substitute the result to the call of procedure 'B' inside 'A', it works.
But if I call 'A' leaving the call to 'B', it never ends.
It locks the table and I cannot kill the process. I must restart the server to unlock the table.

 

[SantaMufasa]

So, Ceccina, do we get any clues, like the code? <grin> We can't possibly hazzard a guess about causes without seeing the code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ceccina]

CREATE OR REPLACE FUNCTION SIPI.RETRIEVE_INGRESSO(inAnno number,inServizio number)
RETURN NUMBER IS

vIngresso number;

BEGIN
SELECT id_ingresso into vIngresso
FROM SETUP i
WHERE I.ID_SERVIZIO=inServizio
AND to_char(I.DT_INIZIO,'yyyy') = inAnno;
RETURN(vIngresso);

EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Impossibile determinare ingresso');
return (0);
END;
END RETRIEVE_INGRESSO;
/

This works:
SELECT SIPI.RETRIEVE_INGRESSO(2009,2)from dual;
> 45

This never ends:

CREATE OR REPLACE FUNCTION SIPI.RETRIEVE_SCUOLA(inAlunno number,inAnno number, inServizio number)
RETURN number IS

vScuola number;

BEGIN
SELECT vc.id_istituto into vScuola
FROM V_ALUNNO_CLASSE vc
WHERE vc.id_alunno = inAlunno
AND vc.id_ingresso = RETRIEVE_INGRESSO(inAnno,inServizio)
AND vc.DT_DIMISSIONE is null;
RETURN(vScuola);

EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Impossibile determinare la scuola');
return (0);
END;
END RETRIEVE_SCUOLA;
/

> SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)


But this works:
SQL>SELECT vc.id_istituto
FROM V_ALUNNO_CLASSE vc
WHERE vc.id_alunno = 78052
AND vc.id_ingresso = 45
AND vc.DT_DIMISSIONE is null;

 

[SantaMufasa]

This never ends:

SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)

Ceccina, when you say "This never ends...", I believe, in fact, that "This never starts"...If your code truly is only:

SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)

...then you have never told it to begin...there is no semicolon (";") or slash ("/") to tell SQL*Plus to begin execution. (You are probably seeing only a "2" at the beginning of the line following "SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)", which means, "Okay, Ceccina, I'm waiting for you to continue/complete the command by telling me "FROM <some table name>...", followed by either ";" or a "/" on a line of its own.


If you had told SQL*Plus to (attempt to) begin execution with either ";" or "/", then you would receive an error message saying:

SQL> SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)
  2  /
SELECT SIPI.RETRIEVE_SCUOLA(78052,2009,2)
                                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

The proof-of-concept that your code works successfully is my copy of your code. (I've also included displays of the contents of my contrived copies of your tables.):

SQL> select * from setup;

ID_INGRESSO ID_SERVIZIO DT_INIZIO
----------- ----------- ---------
         45           2 06-APR-09

1 row selected.

SQL> select * from V_ALUNNO_CLASSE;

 ID_ALUNNO ID_INGRESSO ID_ISTITUTO DT_DIMISS
---------- ----------- ----------- ---------
     78052          45         999

1 row selected.

CREATE OR REPLACE FUNCTION RETRIEVE_INGRESSO(inAnno number,inServizio number) RETURN NUMBER
IS
    vIngresso number;
BEGIN
    SELECT id_ingresso into vIngresso
      FROM SETUP i
     WHERE I.ID_SERVIZIO=inServizio
       AND to_char(I.DT_INIZIO,'yyyy') = inAnno;
    RETURN(vIngresso);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        BEGIN
            DBMS_OUTPUT.PUT_LINE('Impossibile determinare ingresso');
            return (0);
        END;
END RETRIEVE_INGRESSO; 
/

Function created.

CREATE OR REPLACE FUNCTION RETRIEVE_SCUOLA(inAlunno number,inAnno number, inServizio number)
    RETURN number
IS
    vScuola number;
BEGIN
    SELECT vc.id_istituto into vScuola
      FROM V_ALUNNO_CLASSE vc
     WHERE vc.id_alunno = inAlunno
       AND vc.id_ingresso = RETRIEVE_INGRESSO(inAnno,inServizio)
       AND vc.DT_DIMISSIONE is null;
    RETURN(vScuola);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        BEGIN
            DBMS_OUTPUT.PUT_LINE('Impossibile determinare la scuola');
            return (0);
        END;
    END RETRIEVE_SCUOLA;
/

Function created.

SQL> SELECT RETRIEVE_SCUOLA(78052,2009,2) from dual;

RETRIEVE_SCUOLA(78052,2009,2)
-----------------------------
                          999

1 row selected.

Let us know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ceccina]

Of course the correct syntax is:
> SELECT RETRIEVE_SCUOLA(78052,2009,2) from dual;
but it never ends!
Looking the session, the query seems 'freezing' on the RETRIEVE_INGRESSO call.

 

[karluk]

I don't see anything wrong with your logic, so I suspect Oracle is just using an extremely poor access path. It's very possible that Oracle will execute the call to RETRIEVE_INGRESSO once for each row in V_ALUNNO_CLASSE, not realizing that the function will always return the value 45. Please try doing the call to RETRIEVE_INGRESSO first and see if it improves your performance.

CREATE OR REPLACE FUNCTION RETRIEVE_SCUOLA(inAlunno number,inAnno number, inServizio number)
RETURN number IS

vScuola number;
v_hold_Ingresso number;

BEGIN
SELECT RETRIEVE_INGRESSO(inAnno,inServizio) into v_hold_Ingresso from dual;
SELECT vc.id_istituto into vScuola
FROM V_ALUNNO_CLASSE vc
WHERE vc.id_alunno = inAlunno
AND vc.id_ingresso = v_hold_Ingresso
AND vc.DT_DIMISSIONE is null;
RETURN(vScuola);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Impossibile determinare la scuola');
return (0);
END;
END RETRIEVE_SCUOLA;

 

[ceccina]

Thanks, that works!
Another question:
if RETRIEVE_INGRESSO is defined in another schema, how can I call that?
if I simply write :
SELECT SIPIMENSETEST.RETRIEVE_INGRESSO(inAnno,inServizio) into v_hold_Ingresso from dual;

I have error:
PLS-00201: identifier 'SIPIMENSETEST.RETRIEVE_INGRESSO' must be declared

The user SIPIMENSETEST is defined on the same Oracle istance.

 

[Thargy]

A suitably authorised user has to grant you the privilege -usually the owner of the schema.

GRANT EXECUTE ON SIPIMENSETEST TO <YOUR USER NAME>;

So, if you log in as CECCINA, the grant statement would be

GRANT EXECUTE ON SIPIMENSETEST TO CECCINA;


Regards

T

 

[ceccina]

That's ok.
Thanks.