Solved

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

Posted on 2004-03-31
7
4,795 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:sunniunda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 11

Accepted Solution

by:
vc01778 earned 435 total points
ID: 10724888
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 10725038
@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
 

Author Comment

by:sunniunda
ID: 10725121
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 23

Expert Comment

by:seazodiac
ID: 10725185
@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
 
LVL 8

Expert Comment

by:Danielzt
ID: 10725252

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
 
LVL 11

Expert Comment

by:vc01778
ID: 10725657
@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
 
LVL 11

Expert Comment

by:vc01778
ID: 10725661
@sunniunda,

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

VC
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question