Oracle Update Stored procedure returning output parameter as o instead of 1. can you help me.

Status Output Parameter
Oracle Stored procedure returns status 0 for the update stored procedure in the oracle package.

Oracle Stored procedure returning o records updated for the output parameter and the record is phisically updating in the database when I run with input parameter EXT_TRACK_ID.

Below is the code.
------------------------------------------------------------------------------
 Procedure UPDATE_DATAFIX_CM_UP_V2(
            p_CM_EXT_TRACK_ID    IN      EXT_DP_ACCT.EXT_TRACK_ID%TYPE,
            p_STATUS             OUT     NUMBER)          

    IS
    BEGIN
    v_STATUS := 1;
    p_STATUS := 0;          
        BEGIN
            SELECT ORG_NBR INTO v_ORG_NBR
            FROM EXT_DP_ACCT
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;
        EXCEPTION
           WHEN OTHERS
                THEN v_ORG_NBR := - 1;
        END;
        BEGIN
            SELECT TENANT_NBR INTO v_TENANT_NBR
            FROM EXT_DP_ACCT
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;
        EXCEPTION
           WHEN OTHERS
                THEN v_TENANT_NBR := - 1;
        END;
        BEGIN
            SELECT ACCT_ID INTO v_ACCT_ID FROM CI_ACCT_CHAR
            WHERE CHAR_TYPE_CD = 'ZZOGTN'
            AND ADHOC_CHAR_VAL = v_ORG_NBR||v_TENANT_NBR;
        EXCEPTION
           WHEN OTHERS
                THEN v_ACCT_ID := - 1;
        END;
        BEGIN
            SELECT ADHOC_CHAR_VAL INTO v_ADHOC_CHAR_VAL FROM CI_ACCT_CHAR
            WHERE ACCT_ID = v_ACCT_ID
            AND CHAR_TYPE_CD = 'T-ONDATE';
        EXCEPTION
           WHEN OTHERS
                THEN v_ADHOC_CHAR_VAL := - 1;
        END;
        ---p_STATUS := 0;      
        BEGIN
            UPDATE EXT_DP_ACCT
            SET TURN_ON_DATE = v_ADHOC_CHAR_VAL
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;

            EXCEPTION
                   WHEN OTHERS
                        THEN p_STATUS := -1;
            ROllBACK;
            RETURN;
        END;
        COMMIT;
        p_STATUS := SQL%ROWCOUNT;
    END UPDATE_DATAFIX_CM_UP_V2;
-----------------------------------------------------------------------------------
Oracle package runs without errors.
I tried changing the commit statement after the SQL%ROWCOUNT. No Luck…
Please help me getting this work. I am struggling from 3 days.
Record is updating in the database, but the status is returning 0, If the record got updated in the datbase then the status should return as 1.

Please Help me
Thanks In advance.
sunniundaAsked:
Who is Participating?
 
vc01778Commented:
Move your rowcount here and you'll be all set:

Procedure UPDATE_DATAFIX_CM_UP_V2(
            p_CM_EXT_TRACK_ID    IN      EXT_DP_ACCT.EXT_TRACK_ID%TYPE,
            p_STATUS             OUT     NUMBER)          

    IS
    BEGIN
    v_STATUS := 1;
    p_STATUS := 0;          
        BEGIN
            SELECT ORG_NBR INTO v_ORG_NBR
            FROM EXT_DP_ACCT
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;
        EXCEPTION
           WHEN OTHERS
                THEN v_ORG_NBR := - 1;
        END;
        BEGIN
            SELECT TENANT_NBR INTO v_TENANT_NBR
            FROM EXT_DP_ACCT
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;
        EXCEPTION
           WHEN OTHERS
                THEN v_TENANT_NBR := - 1;
        END;
        BEGIN
            SELECT ACCT_ID INTO v_ACCT_ID FROM CI_ACCT_CHAR
            WHERE CHAR_TYPE_CD = 'ZZOGTN'
            AND ADHOC_CHAR_VAL = v_ORG_NBR||v_TENANT_NBR;
        EXCEPTION
           WHEN OTHERS
                THEN v_ACCT_ID := - 1;
        END;
        BEGIN
            SELECT ADHOC_CHAR_VAL INTO v_ADHOC_CHAR_VAL FROM CI_ACCT_CHAR
            WHERE ACCT_ID = v_ACCT_ID
            AND CHAR_TYPE_CD = 'T-ONDATE';
        EXCEPTION
           WHEN OTHERS
                THEN v_ADHOC_CHAR_VAL := - 1;
        END;
        ---p_STATUS := 0;      
        BEGIN
            UPDATE EXT_DP_ACCT
            SET TURN_ON_DATE = v_ADHOC_CHAR_VAL
            WHERE EXT_TRACK_ID = p_CM_EXT_TRACK_ID;
        p_STATUS := SQL%ROWCOUNT;
            EXCEPTION
                   WHEN OTHERS
                        THEN p_STATUS := -1;
            ROllBACK;
            RETURN;
        END;
        COMMIT;

    END UPDATE_DATAFIX_CM_UP_V2;


VC
0
 
seazodiacCommented:
@hi, Vc, take a break from work and enjoy here? ;-)

Just in case you are wondering why so,  
Once you commit, the cursor is gone.
Therefore you have to move the SQL%ROWCOUNT before the commit,


 

0
 
sunniundaAuthor Commented:
Thanks a lot for your help vc01778
For some of the proframs I have commit then ther SQL%rowcount. those worked fine. I do not know Why this one is different. I though If you say commit then it will update in the database and get the row count.
Please advice
IBut it worked for this program
Thanks
sunniunda
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
seazodiacCommented:
@sunniunda:

there is a subtle difference between implicit cursors and explicit cursors.

IMPLICIT cursors, like one of yours here, is closed once you commit.

but you have more control over explicit cursors, you can commit in between because you have to manually close the explicit cursors.

hope this helps
0
 
DanielztCommented:

Hi guys,

I did this as follows and I always got the exact records number.
May you guys have a try?
----------------------------
declare
 aaa number;
begin
 aaa:=0 ;
 dbms_output.put_line(' before call SP, aaa='||aaa);
 up_out_test(aaa);  
 dbms_output.put_line(' after call SP, aaa='||aaa);
end;
 
 
create or replace procedure up_out_test(p_STATUS out number)
as
begin
      BEGIN
           p_STATUS :=0;
            dbms_output.put_line(' before update,p_status='||p_status);            
            update report.TMP_LOADER_LOG_BF
            set message ='test';
            p_STATUS := SQL%ROWCOUNT;
            dbms_output.put_line(' after update,p_status='||p_status);
            commit;
            EXCEPTION
                   WHEN OTHERS
                        THEN p_STATUS := -1;
                        ROllBACK;
            RETURN;
        END;
   p_STATUS := SQL%ROWCOUNT;      
   dbms_output.put_line(' outside PL/SQL BLOCK and before commit,p_status='||p_status);      
   declare
 aaa number;
begin
 aaa:=0 ;
 dbms_output.put_line(' before call SP, aaa='||aaa);
 up_out_test(aaa);  
 dbms_output.put_line(' after call SP, aaa='||aaa);
end;
 
 
create or replace procedure up_out_test(p_STATUS out number)
as
begin
      BEGIN
           p_STATUS :=0;
            dbms_output.put_line(' before update,p_status='||p_status);            
            update report.TMP_LOADER_LOG_BF
            set message ='test';
            p_STATUS := SQL%ROWCOUNT;
            dbms_output.put_line(' after update,p_status='||p_status);
            commit;
            EXCEPTION
                   WHEN OTHERS
                        THEN p_STATUS := -1;
                        ROllBACK;
            RETURN;
        END;
   p_STATUS := SQL%ROWCOUNT;      
   dbms_output.put_line(' outside PL/SQL BLOCK and before commit,p_status='||p_status);      
   commit; -- commit again
      p_STATUS := SQL%ROWCOUNT;      
   dbms_output.put_line(' outside PL/SQL Block and after commit,p_status='||p_status);      
end;

      p_STATUS := SQL%ROWCOUNT;      
   dbms_output.put_line(' outside PL/SQL Block and after commit,p_status='||p_status);      
end;

0
 
vc01778Commented:
@Danielzt,

8.1.7 behaves differently than 9.2 :

SQL> create table t1(x int);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

         X
----------
         1
         1
         1

CREATE OR REPLACE procedure p1
as
begin
 dbms_output.put_line(' before update '||SQL%ROWCOUNT);            
 update t1 set x=1;
 dbms_output.put_line(' after update '|| SQL%ROWCOUNT);            
 commit;
 dbms_output.put_line(' after commit '||SQL%ROWCOUNT);            
end;
/

set serverout on
exec p1;

In 9.2 and above:

before update
after update 3
after commit 0


In 8.1.7.4:

before update
after update 3
after commit 3

One should not rely on 8i's behaviour since Oracle never guaranteed it -- sql%errno returns the result of the _last_ implicit cursor execution and it's quite legitimate that the value may be over-written by a subsequent 'commit' (as it is in 9i and above).


VC
0
 
vc01778Commented:
@sunniunda,

The proc apparently worked for you in 8i and stopped doing so in 9i.  Pls. see above.

VC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.