Solved

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

Posted on 2004-03-31
7
4,772 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 46
T-SQL Convert to PL/SQL 23 62
oracle rollup query 3 37
Migrating an SQL 2008 database to Oracle 12c 3 63
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now