Re: PL/Sql Error Handling Package
I sent such code some 2-3 months ago to this list, cannot find it quickly now. That used autonomous transactions and stored errors in a table. I'm not posting much, therefore You may search in archives using my name ... Gints Plivna IT Sistçmas, Meríeïa 13, LV1050 Rîga http://www.itsystems.lv/gints/ johanna.doran@sun gard.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: PL/Sql Error Handling Package 2002.07.11 23:52 Please respond to ORACLE-L Amy one have any decent stanard error handling packages or link to advice on creating such a package? Thanks, Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/Sql Error Handling Package
Thanks! -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 5:34 PM To: Multiple recipients of list ORACLE-L Subject: Re:PL/Sql Error Handling Package Hanah, This is one procedure out of an entire package, but it works for us. procedure oracle_err(message in varchar2, status out number, mailing_list in varchar2 default 'NONE' ) is file_hndl utl_file.file_type; luser varchar2(40); sname varchar2(40); sn varchar2(7); fname varchar2(40); err_date varchar2(20); begin status := sqlcode; if(status 0) then select user, name, to_char(err_file_sqnc.nextval), to_char(sysdate,'DD-MON- HH24:MI') into luser, sname, sn, err_date from oracle_server; fname := 'oracle'||sn||'.err'; file_hndl := utl_file.fopen(utl_home,fname,'a'); utl_file.putf(file_hndl, 'Oracle Error Report from %s\n%s\n', sname, err_date); utl_file.putf(file_hndl, 'Error: %s\nLocation: %s', sqlerrm, message); utl_file.fflush(file_hndl); utl_file.fclose(file_hndl); rollback; if(mailing_list != 'NONE') then cron_mail_list(mailing_list, fname, 'Oracle Procedure Error'); end if; end if; end; Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/Sql Error Handling Package
We have package for logging diferent errors like 1. user error -- for user defined exception 2. system error -- for system exception 3. other error -- for other error 4. debug -- for debug logging every time an exception is caught , based on type a satus is returned to calling program at the same time error is logged calling proc in above package . program generates some bedug string which is logged along with error which helps in debugging . Is it what ur looking for ? -Bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 11, 2002 1:53 PM Amy one have any decent stanard error handling packages or link to advice on creating such a package? Thanks, Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql error
Harvinder, You must have a carriage return at the end of the enum variable. Try rtrim, like this: name2 :='t_pv_'||substr(rtrim(enum,chr(10),i+1)||'_2'; Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Harvinder Singh Harvinder.Singh@MetrTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] aTech.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: pl/sql error 06/06/2001 07:06 PM Please respond to ORACLE-L Hi, I am running the code containing following statements name1 :='t_pv_'||substr(enum,i+1)||'_1'; name2 :='t_pv_'||substr(enum,i+1)||'_2'; name3 :='t_pv_'||substr(enum,i+1)||'_3'; dbms_output.put_line(name1); dbms_output.put_line(name2); dbms_output.put_line(name3); str :='update'||' '||name2||' '||'set id_sess=id_sess+'||temp_id_sess_2; execute immediate str; it show output og name 1 as t_pv_ps_cc_credit _3 and not as t_pv_ps_cc_credit_3 .. and i feels it failing my execute immediate str statement and getting error: ORA-00911: invalid character ORA-06512: at line 37 How i can improve this code Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Error
David, What I'd want to see is the actual call you are making in the ASP code. Your package is fine, the problem is with the variables (you have defined in the ASP code), that are used when this procedure is being called. If you can ensure that the variables have been declared as following you should be okay ... otherwise Oracle is right ... declare var1 course.course_bulletin.tbl_call_no; var2 course.course_bulletin.tbl_schl_cd; var3 course.course_bulletin.tbl_dept_cd; begin course.course_bulletin(i_ccyy, var1, var2, var3); end; __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Error
David, How are you calling this procedure? Into what structures are you receiving the PL/SQL Out Arguments, which are PL/SQL tables? I'll bet that's your problem. Show us the code that calls this procedure and handles the output. BTW, if you checked the speeling of your PL/SQL as well as you did your message, there may be even more problems. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- David Sent: Wednesday, May 30, 2001 12:07 PM To: Multiple recipients of list ORACLE-L Hi all Can someone help me out here? I am getting this error when I try to run this PL/SQL code from an ASP page. The error manual just says to check speeling. I DID. Is there anything I am missing? The DB is V7.3 I am trying to return the resultset to the page. PLS-00306: wrong number or types of arguments in call to 'COURSE_SELECT' Here is the PL/SQL code create or replace package course.course_bulletin is TYPE tbl_call_no IS TABLE of cmcmcrst.call_no%type INDEX BY BINARY_INTEGER; TYPE tbl_schl_cd IS TABLE of cmcmcrst.schl_cd%type INDEX BY BINARY_INTEGER; TYPE tbl_dept_cd IS TABLE of cmcmcrst.dept_cd%type INDEX BY BINARY_INTEGER; PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd); end; create or replace package body course.course_bulletin as PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd) IS CURSOR c1 is SELECT call_no,schl_cd,dept_cd FROM COURSE.cmcmcrst WHERE ccyy = i_ccyy AND term_cd = '2' AND instrl_cd = '0'; crscount NUMBER DEFAULT 1; BEGIN FOR c IN c1 LOOP o_call_no(crscount) := c.call_no; o_schl_cd(crscount) := c.schl_cd; o_dept_cd(crscount) := c.dept_cd; crscount := crscount + 1; END LOOP; END; end; -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Error
Here is the ASP code
ccyy = 2001
set cn = server.CreateObject(adodb.connection)
set cmd = server.CreateObject(adodb.command)
set rs = server.CreateObject(adodb.recordset)
cn.ConnectionTimeout = 120
cn.CommandTimeout=30
cn.ConnectionString=DSN=db;UID=user;Password=pwd
cn.Open
cmd.ActiveConnection = cn
cmd.CommandText = {call course.course_bulletin.course_select({resultset
1, o_call_no,
o_schl_cd, o_dept_cd})}
cmd.CommandType=adCmdText
cmd.Parameters.Append cmd.CreateParameter(i_ccyy, adInteger, adParamInput,
, clng(ccyy))
set rs.Source = cmd
rs.Open
-Original Message-
Sent: Wednesday, May 30, 2001 12:01 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
I think the error is in the call to the procedure, not in the procedure
itself -- the procedure looks fine at a glance. Could you post the ASP
code that makes the call?
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
Bartolo,
David To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
bartolo@USCOcc:
LO.edu Fax to:
Sent by: Subject: PL/SQL Error
root@fatcity.
com
05/30/2001
01:06 PM
Please
respond to
ORACLE-L
Hi all
Can someone help me out here? I am getting this error when I try to run
this
PL/SQL code from an ASP page.
The error manual just says to check speeling. I DID. Is there anything I am
missing? The DB is V7.3 I am trying to
return the resultset to the page.
PLS-00306: wrong number or types of arguments in call to 'COURSE_SELECT'
Here is the PL/SQL code
create or replace package course.course_bulletin is
TYPE tbl_call_no IS TABLE of cmcmcrst.call_no%type
INDEX BY BINARY_INTEGER;
TYPE tbl_schl_cd IS TABLE of cmcmcrst.schl_cd%type
INDEX BY BINARY_INTEGER;
TYPE tbl_dept_cd IS TABLE of cmcmcrst.dept_cd%type
INDEX BY BINARY_INTEGER;
PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no,
o_schl_cd OUT tbl_schl_cd,
o_dept_cd OUT tbl_dept_cd);
end;
create or replace package body course.course_bulletin as
PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no,
o_schl_cd OUT tbl_schl_cd,
o_dept_cd OUT tbl_dept_cd) IS
CURSOR c1 is
SELECT call_no,schl_cd,dept_cd
FROM COURSE.cmcmcrst
WHERE ccyy = i_ccyy
AND term_cd = '2'
AND instrl_cd = '0';
crscount NUMBER DEFAULT 1;
BEGIN
FOR c IN c1 LOOP
o_call_no(crscount) := c.call_no;
o_schl_cd(crscount) := c.schl_cd;
o_dept_cd(crscount) := c.dept_cd;
crscount := crscount + 1;
END LOOP;
END;
end;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bartolo, David
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bartolo, David
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL Error
I think the error is in the call to the procedure, not in the procedure itself -- the procedure looks fine at a glance. Could you post the ASP code that makes the call? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Bartolo, David To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] bartolo@USCOcc: LO.edu Fax to: Sent by: Subject: PL/SQL Error root@fatcity. com 05/30/2001 01:06 PM Please respond to ORACLE-L Hi all Can someone help me out here? I am getting this error when I try to run this PL/SQL code from an ASP page. The error manual just says to check speeling. I DID. Is there anything I am missing? The DB is V7.3 I am trying to return the resultset to the page. PLS-00306: wrong number or types of arguments in call to 'COURSE_SELECT' Here is the PL/SQL code create or replace package course.course_bulletin is TYPE tbl_call_no IS TABLE of cmcmcrst.call_no%type INDEX BY BINARY_INTEGER; TYPE tbl_schl_cd IS TABLE of cmcmcrst.schl_cd%type INDEX BY BINARY_INTEGER; TYPE tbl_dept_cd IS TABLE of cmcmcrst.dept_cd%type INDEX BY BINARY_INTEGER; PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd); end; create or replace package body course.course_bulletin as PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd) IS CURSOR c1 is SELECT call_no,schl_cd,dept_cd FROM COURSE.cmcmcrst WHERE ccyy = i_ccyy AND term_cd = '2' AND instrl_cd = '0'; crscount NUMBER DEFAULT 1; BEGIN FOR c IN c1 LOOP o_call_no(crscount) := c.call_no; o_schl_cd(crscount) := c.schl_cd; o_dept_cd(crscount) := c.dept_cd; crscount := crscount + 1; END LOOP; END; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bartolo, David INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
