asked on

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.

ASKER CERTIFIED SOLUTION

vc01778

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

seazodiac

@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,

sunniunda

ASKER

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

seazodiac

@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

Danielzt

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;

vc01778

@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

vc01778

@sunniunda,

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

VC