HI, i have a problem with Oracle SP.....

[somaiyatushar11]

i have a simple SP which gives me information from 5 different tables... This SP compiles successfully... But sometimes SP gets dropped automatically.. or i don't know what happens but i have to recompile it and then execute it...this happens very regularly....

i don't know if anything is wrong with the SP or server settings or what.. because other 40 SPs run fine. i have this peculier problem with this SP only...

if anybody knows why this happens or / and how to solve it then please let me know. PLease let me know if you require SP code.

 

[stefanhei]

Hi.
Does the SP really get dropped or just become invalid?
If it just becomes invalid, I guess one of the tables the SP accesses was dropped and recreated. You can solve this by not dropping but truncating the table. Invalid Procedures get compiled when called. If you don't run into problems with privileges this just decreases performance for the first call.

Stefan

 

[somaiyatushar11]

no i am not altering any underlying table and as you said the SP gets recompiled when called if that happens....

but i am encountering this problem very uncanningly....

that is were i am stuck... i don't know why it happens with this SP only.. if underlying table is altered then other 15 SPs on those tables should also get invalid.. right?

but this is not the case...

 

[stefanhei]

Maybe it helps if you post the code, but if the SP just reads from those tables my above answer gives the only reason which comes into my mind now. Is it possible that those other SPs are called before you check?

Stefan

 

[somaiyatushar11]

i checked those SPs when i encountered this unusual error. They work fine. I think something is wrong with this SP or settings of this SPs.

i would be very happy to send you the code. but can i paste the whole code over here. its 6 pages long. Simple SP bu a longer one.

if you can send me your mail id then i can send the code to you or i will post it here... whatever you say.....

but i have to solve this problem ASAP. because this is happening in production server, i mean for a LIVE project...

 

[sem]

I also had the same problem. My STANDALONE stored procedure called a set of PACKAGED procedures. When one of them became invalid due to some changes, then the calling procedure was not recompiled on first invocation so we had to recompile it manually. But in any case this was due to some DDL operations on called objects.

The only way we found was to package it. In this case the error may be rased only 1 time (on first call after change, if it has already been called during the session)

 

[somaiyatushar11]

Following is the code of my SP which is long but very simple...

procedure apptrack_ware_funding_retrieve
(
al_seller_id IN NUMBER,

al_julian_id_num IN NUMBER,

as_pgm_type_cde_uw_out OUT VARCHAR2,

as_trust_acct_ind_out OUT VARCHAR2,

af_tran_amt_sum_out OUT NUMBER,

as_updt_dt_tm OUT VARCHAR2,

al_traunch_use_out OUT NUMBER,

al_whse_line_lmt_out OUT NUMBER,

as_rpt_end_dt_out OUT VARCHAR2,

al_pay_down_amt_out OUT NUMBER,

al_tot_trust_acct_adva_out OUT NUMBER, /* Date:07/21/2000 .Added By Sunil */

al_tot_trust_acct_reversal_out OUT NUMBER, /* Date:07/21/2000 .Added By Sunil */

al_trust_reversal_amt_out OUT NUMBER

)

AS

sql_err NUMBER;

err_msg VARCHAR2(300);

li_row_count NUMBER;

BEGIN



select count(1) into li_row_count

from TRUST_ACCT_ACTIVITY

where SEQ_NUM = (select max(SEQ_NUM) from TRUST_ACCT_ACTIVITY

where SELLER_ID = al_seller_id and

JULIAN_ID_NUM = al_julian_id_num) and TRAN_CDE = 'R';



if li_row_count = 1 then

select TRAN_AMT into al_trust_reversal_amt_out

from TRUST_ACCT_ACTIVITY

where SEQ_NUM = (select max(SEQ_NUM) from TRUST_ACCT_ACTIVITY

where SELLER_ID = al_seller_id and

JULIAN_ID_NUM = al_julian_id_num) and TRAN_CDE = 'R';

else

al_trust_reversal_amt_out := 0;

end if;





/* Anthony Start 07/06/2000.*/



select count(1) into li_row_count

from seller_pay_down

where (seller_id = al_seller_id and

julian_id_num = al_julian_id_num );



if li_row_count > 0 then

select sum(pay_down_amt) into al_pay_down_amt_out

from seller_pay_down

where (seller_id = al_seller_id and julian_id_num = al_julian_id_num);

else

al_pay_down_amt_out := 0;

end if;

/* Anthony End. 07/06/2000.*/



/* Dhawal changes begin. 06/22/2000.*/



select count(1) into li_row_count

from seller

where seller_id = al_seller_id;



if li_row_count = 1 then



select to_char(max(rpt_end_dt),'MM/DD/YYYY') into as_rpt_end_dt_out

from seller_settlement_summary

where seller_id = al_seller_id;

end if;

/* Dhawal changes end. 06/22/2000.*/



select count(1)

into li_row_count

from seller_traunch

where seller_id = al_seller_id;



if li_row_count > 0 then

select sum(traunch_use_lmt) into al_traunch_use_out from seller_traunch

where seller_id = al_seller_id and

traunch_type||eff_dt in (select traunch_type||max(eff_dt)

from (select * from seller_traunch where seller_id = al_seller_id)

group by traunch_type);

end if;



select count(1)

into li_row_count

from seller

where seller_id = al_seller_id;



if li_row_count > 0 then

select whse_line_lmt

into al_whse_line_lmt_out

from seller

where seller_id = al_seller_id;

end if;



select count(1) into li_row_count

from underwriting_info

where (julian_id_num = al_julian_id_num

and loan_type = '08'

and preflite_ind ='P');



if li_row_count = 1 then

select pgm_type_cde_uw into

as_pgm_type_cde_uw_out

from underwriting_info

where (julian_id_num = al_julian_id_num

and loan_type = '08'

and preflite_ind ='P');

end if;



select count(1) into li_row_count

from seller

where seller_id = al_seller_id;



if li_row_count = 1 then

select trust_acct_ind into

as_trust_acct_ind_out

from seller

where seller_id = al_seller_id;

end if;



select nvl(sum(tran_amt),0) into

af_tran_amt_sum_out

from trust_acct_activity

where seller_id = al_seller_id;



/* Date:07/21/2000 .Added By Sunil to calculate total of all trust account advances for seller id */

select nvl(sum(tran_amt),0) into

al_tot_trust_acct_adva_out

from trust_acct_activity

where seller_id =al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'F' ;



/* End of Adddition -------- Sunil. */



/* Date:07/21/2000 .Added By Sunil to calculate total of all trust account reversals for seller id */



select nvl(sum(tran_amt),0) into

al_tot_trust_acct_reversal_out

from trust_acct_activity

where seller_id =al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'R';



/* End of Adddition -------- Sunil. */



select count(1) into li_row_count

from trust_acct_activity

where seller_id = al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'F'

and seq_num = (select max(seq_num) from trust_acct_activity

where seller_id = al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'F');



if li_row_count = 1 then

select to_char(updt_dt_tm,'MM/DD/YYYY HH24:MI:SS') into as_updt_dt_tm

from trust_acct_activity

where seller_id = al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'F'

and seq_num = (select max(seq_num) from trust_acct_activity

where seller_id = al_seller_id

and julian_id_num = al_julian_id_num

and tran_cde = 'F');

end if;



dbms_output.put_line ('program Type Code UW = ' || as_pgm_type_cde_uw_out);

dbms_output.put_line ('Trust Account Indicator = ' || as_trust_acct_ind_out);

dbms_output.put_line ('Trust Account Current Balance = ' || af_tran_amt_sum_out);

dbms_output.put_line ('Report End Date = ' || as_rpt_end_dt_out);



EXCEPTION

when others then

sql_err := sqlcode;

err_msg := sqlerrm;

DBMS_OUTPUT.PUT_LINE (sql_err);

DBMS_OUTPUT.PUT_line (err_msg);

APPTRACK_UPDATE_ERRLOG('OTHERS', SQLCODE, SQLERRM, 'apptrack_ware_funding_retrieve', 'JULIAN_ID_NUM='||TO_CHAR(NVL(al_julian_id_num, 0)));

raise_application_error(-20011,'Database Error - Database Error code = '||sql_err||', Database Error Text = '||err_msg);

END apptrack_ware_funding_retrieve;

it give me following error:


ORA-06553: PLS-707: unsupported construct or internal error [2601]
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored


After some R&D i found out that this SP gets invalid(or something else i don't know what) after some specific time say 10 min or 15 min...

it seems the error is dependent on time... Its Strange but.. i don't know maybe some settings or something is wrong....

 

[sem]

I suppose that when you call APPTRACK_UPDATE_ERRLOG in error handler, you should use sql_err and err_msg instead of SQLCODE and SQLERRM, as after successfull call to dbms_output the later doesn't hold error information anymore.

You may also check LAST_DDL_TIME to see the objects that where changed since last successful invocation.

 

[stefanhei]

From Doku:
PLS-00707 unsupported construct or internal error [string]

Cause: At run time, this is an internal error. At compile time, it indicates one of the following problems:

A call was made to a remote subprogram that has a parameter type or default expression not supported at the calling site.

An incomplete upgrade or downgrade was done to a database that has stored procedures. Perhaps incorrect versions of system packages such as STANDARD.SQL were installed.

A compiler bug was encountered. In such cases, legal PL/SQL syntax will fail to compile.

Action: Either report the internal error to Oracle Support Services or, depending on the problem, take one of the following actions:

Revise the logic of the application to use parameter types and default expressions that are supported at both the local and remote sites.

Complete the upgrade or downgrade properly, making sure to install correct versions of all system packages.

Report the legal-syntax error to Oracle Support Services. If there are line and column numbers displayed with the error message, they might help you find a workaround. For example, try recoding the offending line to avoid the bug.


As your error occus at runtime, incomplete upgrade or compiler error might be the cause of your problem.

Stefan

 

[somaiyatushar11]

i am in process of testing the use of sql_err and err_msg instead of SQLCODE and SQLERRM....

lets see whats the results are....

i will keep posting the results......

 

[sem]

I suppose that the error with SQLCODE and SQLERRM should be corrected, but this will hardly correct the main error. What about LAST_DDL_TIME?

 

[somaiyatushar11]

am also implementing LAST_DDL_TIME....

No probs till now but i was wondering that the same error tracking mechanism is been used in all the 63 SPs then why is the problem been raised for this SP only....

Anyways let me check if error handling is the culprit or not...

 

[sem]

I'm about to be sure that the error handler itself is not the source of the main error. It's just writes nothing useful to your log. Another thing with APPTRACK_UPDATE_ERRLOG, as it may use dbms_pipe or aq or autonomous transactions or whatever else.

 

[somaiyatushar11]

how about this query..... where i concatenate in the select query without any variables....

"traunch_type||eff_dt in (select traunch_type||max(eff_dt)"

in following select query:

if li_row_count > 0 then

select sum(traunch_use_lmt) into al_traunch_use_out from seller_traunch

where seller_id = al_seller_id and

traunch_type||eff_dt in (select traunch_type||max(eff_dt)

from (select * from seller_traunch where seller_id = al_seller_id)

group by traunch_type);

end if;


Anything wrong with this.....

 

[somaiyatushar11]

hey techies,

thanks all of you for your help..

i have to leave now... would be working to solve the error tommorrow also... thanks for all the help you all provided...


see you tommorrow...

 

[sem]

I do not know a lot about your data, but it seems to me that the following query makes the same:

select sum(traunch_use_lmt)
into al_traunch_use_out
from seller_traunch
where seller_id = al_seller_id and
(traunch_type, eff_dt) in
(select traunch_type, max(eff_dt)
from seller_traunch where seller_id = al_seller_id
group by traunch_type);

If you're on 9.2 you may try to use analytical functions.
Regards, Dima