RE: Hanging stored proc

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Wed, 27 Sep 2000 14:41:07 -0400
Message-Id: <10632.118046@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C028B2.82FCFBE2
Content-Type: text/plain;

        charset="iso-8859-1"

Its probably some bad SQL in the package (or an infinate loop.) The first thing to do is to determine what SQL is executing.

The attached PL/SQL will show all of the Active SQL in the database (excluding SYS, SYSTEM and DBSNMP).

Kevin

> -----Original Message-----
> From: Ajay K [mailto:ajay_at_tems.com]
> Sent: Wednesday, September 27, 2000 12:29 PM
> To: Toepke, Kevin M; Multiple recipients of list ORACLE-L
> Subject: Hanging stored proc
>
>
> Seems to be strange when I am trying to execute one of proc
> ,it doesn't come
> back at all (hangs out) ,any body see this problem before ?
> While trying to
> recomple the proc it get compiled well.
> Ajay
>

------_=_NextPart_000_01C028B2.82FCFBE2
Content-Type: application/octet-stream;

        name="sqlrunactive.sql"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

        filename="sqlrunactive.sql"

DECLARE=0A=
    CURSOR c_session IS=0A=

        SELECT ses.sid, ses.program, ses.sql_address, ses.username, = ses.osuser=0A=

              ,ses.serial#, ses.status, pro.spid=0A=
        FROM   v$session ses=0A=
              ,v$process pro=0A=
        WHERE  ses.username IS NOT NULL=0A=
        AND    ses.username NOT IN ('DBSNMP', 'sys', 'SYS', 'system', =
'SYSTEM')=0A=
        AND    ses.status   =3D 'ACTIVE'=0A=
        AND    pro.addr     =3D ses.paddr=0A=
        ORDER BY ses.sid, ses.program, ses.sql_address;=0A=

=0A=

    CURSOR c_sql_text (=0A=

        is_sql_address          IN v$session.sql_address%TYPE=0A=
        ) IS=0A=
        SELECT txt.sql_text=0A=
        FROM   v$sqltext_with_newlines txt=0A=
        WHERE  txt.address  =3D is_sql_address=0A=
        AND    NOT EXISTS (SELECT 1=0A=
                    FROM   v$sqltext_with_newlines txt2=0A=
                    WHERE  txt2.address =3D txt.address=0A=
                    AND    txt2.sql_text LIKE 'SELECT =
SES.SID,SES.PROGRAM,SES.SQL_ADDRESS,SES.USERNAME,SES.OSUS%')=0A=
        ORDER BY txt.piece;=0A=
=0A=
    ls_stmt                     VARCHAR2(4000);=0A=
    lb_first                    BOOLEAN;=0A=

=0A=

    PROCEDURE put_line (=0A=

        is_string               IN VARCHAR2=0A=
        ) IS=0A=
        ln_len                  NUMBER(4) :=3D LENGTH(is_string);=0A=
        ln_index                NUMBER(4);=0A=
        ln_comma                NUMBER(4);=0A=
        ln_space                NUMBER(4);=0A=
        ln_oparn                NUMBER(4);=0A=
        ln_cparn                NUMBER(4);=0A=
        ln_equal                NUMBER(4);=0A=
        ls_start                VARCHAR2(4000);=0A=
        ls_end                  VARCHAR2(4000);=0A=
    BEGIN=0A=
        IF (is_string IS NULL) THEN=0A=
            RETURN;=0A=
        END IF;=0A=
=0A=
        IF (ln_len <=3D 80) THEN=0A=
            IF (SUBSTR(is_string, ln_len, 1) =3D CHR(10)) THEN=0A=
                DBMS_OUTPUT.PUT(is_string);=0A=
            ELSE=0A=
                DBMS_OUTPUT.PUT_LINE(is_string);=0A=
            END IF;=0A=
        ELSE=0A=
            ln_index :=3D INSTR(is_string, CHR(10));=0A=
            IF (ln_index !=3D 0) THEN=0A=
                ls_start :=3D SUBSTR(is_string, 1, ln_index - 1);=0A=
                ls_end   :=3D SUBSTR(is_string, ln_index + 1, =
ln_len);=0A=
                put_line(ls_start);=0A=
                put_line(ls_end);=0A=
            ELSE=0A=
                ln_space :=3D INSTR(is_string, ' ', 79-ln_len, 1);=0A=
                ln_comma :=3D INSTR(is_string, ',', 79-ln_len, 1);=0A=
                ln_oparn :=3D INSTR(is_string, '(', 79-ln_len, 1);=0A=
                ln_cparn :=3D INSTR(is_string, ')', 79-ln_len, 1);=0A=
                ln_equal :=3D INSTR(is_string, '=3D', 79-ln_len, 1);=0A=
=0A=
                ln_index :=3D GREATEST(ln_space, ln_comma, ln_oparn, =
ln_cparn=0A=
                                    ,ln_equal);=0A=
                IF (ln_index !=3D 0) THEN=0A=
                    ls_start :=3D SUBSTR(is_string, 1, ln_index);=0A=
                    ls_end   :=3D SUBSTR(is_string, ln_index + 1, =
ln_len);=0A=
                    put_line(ls_start);=0A=
                    put_line(ls_end);=0A=
                ELSE=0A=
                    DBMS_OUTPUT.PUT_LINE('....' || SUBSTR(is_string, 1, =
70));=0A=
                END IF;=0A=
            END IF;=0A=
        END IF;=0A=

    END;=0A=
BEGIN=0A=
    FOR r_session IN c_session LOOP=0A=

        ls_stmt     :=3D NULL;=0A=
        lb_first    :=3D TRUE;=0A=
=0A=
        FOR r_sql_text in c_sql_text(r_session.sql_address) LOOP=0A=
            IF (lb_first) THEN=0A=
                DBMS_OUTPUT.PUT('SID, SERIAL#, USER, OSUSER, SPID: =
');=0A=
                DBMS_OUTPUT.PUT_LINE(r_session.sid        || ', ' ||=0A=
                                     r_session.serial#    || ', ' ||=0A=
                                     r_session.status     || ', ' ||=0A=
                                     r_session.username   || ', ' ||=0A=
                                     r_session.osuser     || ', ' ||=0A=
                                     r_session.spid       );=0A=
                DBMS_OUTPUT.PUT_LINE(RPAD('_', 79, '_'));=0A=
=0A=
                lb_first    :=3D FALSE;=0A=
            END IF;=0A=
=0A=
            IF (ls_stmt IS NULL) THEN=0A=
                ls_stmt :=3D r_sql_text.sql_text;=0A=
            ELSE=0A=
                ls_stmt :=3D ls_stmt || r_sql_text.sql_text;=0A=
            END IF;=0A=
        END LOOP;=0A=
=0A=
        IF (NOT lb_first) THEN=0A=
            put_line(ls_stmt);=0A=

Received on Wed Sep 27 2000 - 13:41:07 CDT