Home » SQL & PL/SQL » SQL & PL/SQL » replace function
replace function [message #222070] Thu, 01 March 2007 10:42 Go to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Hi,

I have the following statements in a package.

DECLARE
VINEXTROWID NUMBER(10) := NULL;
VICURRENTROWID NUMBER(10);
VICOUNTER NUMBER(10);
VROW_NUM NUMBER(10);
VCLAIMNO CHAR(20);
VRESERVENO CHAR(10);
VTRANSACTIONPROCESSDT DATE;
VTRANPRODTCHAR CHAR(22);
VTRANPRODTCHAR1 CHAR(22);
VERROR_MESSAGE_CD NUMBER(3);
VREPORTING_DATE CHAR(10);

VREPORT_DT_NO_DASH CHAR(10);
VTRAN_DT_NO_DASH CHAR(10);
VCLAIMSTATUS CHAR(10);
VPOLICYNO CHAR(25);
VPOLICYEFFDT VARCHAR(10);


VPOLEFF_DT_NO_DASH CHAR(10);
VCLMLOSS_DT_NO_DASH CHAR(10);
VCLMRPT_DT_NO_DASH CHAR(10);
VCLMOPEN_DT_NO_DASH CHAR(10);
VCLMCLS_DT_NO_DASH CHAR(10);
VCLMREOP_DT_NO_DASH CHAR(10);
VRESOPN_DT_NO_DASH CHAR(10);
VRESCLS_DT_NO_DASH CHAR(10);
VRESREOP_DT_NO_DASH CHAR(10);

VCLAIMLOSSDT VARCHAR(20);
VCLAIMREPORTEDDT VARCHAR(20);
VCLAIMOPENEDDT VARCHAR(20);
VCLAIMCLOSEDDT VARCHAR(20);
VCLAIMREOPENEDDT VARCHAR(20);
VRESERVEOPENEDDT VARCHAR(20);
VRESERVEREOPENDT VARCHAR(20);
VRESERVECLOSEDDDT VARCHAR(20);

VACCYEARMONTHDESCE VARCHAR(100);
VCLCYEARMONTHDESCE VARCHAR(100);
VCLRYEARMONTHDESCE VARCHAR(100);
VOPCYEARMONTHDESCE VARCHAR(100);
VOPRYEARMONTHDESCE VARCHAR(100);
VPOLICYINCEPYEARDESCE VARCHAR(100);
VQUARTERNAME VARCHAR(20);


VTIME_DATE CHAR(25);
VHOUR_HOLD CHAR(2);
VMIN_HOLD CHAR(2);
VSEC_HOLD CHAR(2);
VMILLISEC_HOLD CHAR(3);




SELECT
T2.ACCYEARMONTHDESCE,
T3.CLCYEARMONTHDESCE,
T4.CLRYEARMONTHDESCE,
T5.OPCYEARMONTHDESCE,
T6.OPRYEARMONTHDESCE,
T8.MTQUARTERNAME,
T7.POLICYINCEPYEARDESCE,
T1.TRANSACTIONPROCESSDT,
T1.TRANSACTIONPROCESSDT,
T1.POLICYEFFDT,
T1.CLAIMLOSSDT,
T1.CLAIMREPORTEDDT,
T1.CLAIMOPENEDDT,
T1.CLAIMCLOSEDDT,
T1.CLAIMREOPENEDDT,
T1.RESERVEOPENEDDT,
T1.RESERVECLOSEDDT,
T1.RESERVEREOPENEDDT,
/*
REPLACE(T1.REPORTING_DATE,'-',''),
REPLACE(T1.POLICYEFFDT,'-',''),
REPLACE(T1.CLAIMLOSSDT,'-',''),
REPLACE(T1.CLAIMREPORTEDDT,'-',''),
REPLACE(T1.CLAIMOPENEDDT,'-',''),
REPLACE(T1.CLAIMCLOSEDDT,'-',''),
REPLACE(T1.CLAIMREOPENEDDT,'-',''),
REPLACE(T1.RESERVEOPENEDDT,'-',''),
REPLACE(T1.RESERVECLOSEDDT,'-',''),
REPLACE(T1.RESERVEREOPENEDDT,'-',''),
REPLACE(T1.TRANSACTIONPROCESSDT,'-',''),
*/
ROW_NUM,
T1.CLAIMNO,
T1.RESERVENO,
T1.TRANSACTIONPROCESSDT,
T1.REPORTING_DATE
INTO
VACCYEARMONTHDESCE ,
VCLCYEARMONTHDESCE ,
VCLRYEARMONTHDESCE ,
VOPCYEARMONTHDESCE ,
VOPRYEARMONTHDESCE ,
VQUARTERNAME ,
VPOLICYINCEPYEARDESCE ,
VTRANPRODTCHAR ,
VTRANPRODTCHAR1 ,
VPOLICYEFFDT ,
VCLAIMLOSSDT ,
VCLAIMREPORTEDDT ,
VCLAIMOPENEDDT ,
VCLAIMCLOSEDDT ,
VCLAIMREOPENEDDT ,
VRESERVEOPENEDDT ,
VRESERVECLOSEDDDT ,
VRESERVEREOPENDT ,
/* VREPORT_DT_NO_DASH ,
VPOLEFF_DT_NO_DASH ,
VCLMLOSS_DT_NO_DASH,
VCLMRPT_DT_NO_DASH ,
VCLMOPEN_DT_NO_DASH ,
VCLMCLS_DT_NO_DASH ,
VCLMREOP_DT_NO_DASH ,
VRESOPN_DT_NO_DASH ,
VRESCLS_DT_NO_DASH ,
VRESREOP_DT_NO_DASH ,
VTRAN_DT_NO_DASH ,
*/
VROW_NUM,
VCLAIMNO ,
VRESERVENO ,
VTRANPRODTCHAR ,
VREPORTING_DATE
FROM IRF_CLAIMS_STAGING1 T1 LEFT OUTER JOIN DIMACCIDENTTIME T2 ON
T1.ACCYEARMONTH = T2.ACCYEARMONTH LEFT OUTER JOIN DIMCLCTIME T3 ON
T1.CLCYEARMONTH = T3.CLCYEARMONTH LEFT OUTER JOIN DIMCLRTIME T4 ON
T1.CLRYEARMONTH = T4.CLRYEARMONTH LEFT OUTER JOIN DIMOPCTIME T5 ON
T1.OPCYEARMONTH = T5.OPCYEARMONTH LEFT OUTER JOIN DIMOPRTIME T6 ON
T1.OPRYEARMONTH = T6.OPRYEARMONTH LEFT OUTER JOIN DIMPOLICYINCEP T7 ON
T1.POLICYEFF_YYYYMM_INTEGER = T7.PIYEARMONTH LEFT OUTER JOIN DIMMONTHLYTIME T8 ON
T1.REPORTING_YYM_INTEGER = T8.MTYEARMONTH
WHERE ROW_NUM = VICURRENTROWID;

Prior to commenting out the sections indicated, I get the following error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYSTEM.P_CLAIMS_ERROR_CHK_TIME_PKG", line 86

When I comment out the sections and re-run the package, i get a communication error, that kicks me out of sqlplus session.

ERROR at line 1:
ORA-03113: end-of-file on communication channel

The research I've done on this error message says things like the machine that is running the oracle instance has more than 1 IP address, or issues with the TNSNAMES file. But since the query works with the REPLACE function in, but does not when it is commented out, i suspect it has something to do with the code.

Any suggestions?

Thanks in advance
Re: replace function [message #222072 is a reply to message #222070] Thu, 01 March 2007 11:02 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
REPLACE(T1.TRANSACTIONPROCESSDT,'-',''),

Is that replacement string an empty string ? thne just take it out.

Srini

Re: replace function [message #222203 is a reply to message #222072] Fri, 02 March 2007 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try moving your packages out of the System account, and into a proper users account.
SYSTEM is not intended to be used for day to day developement work.

I assume your package is called P_CLAIMS_ERROR_CHK_TIME_PKG.

I'm going to take an informed guess here. The following fields are all of type DATE:
T1.POLICYEFFDT, 
T1.CLAIMLOSSDT, 
T1.CLAIMREPORTEDDT, 
T1.CLAIMOPENEDDT, 
T1.CLAIMCLOSEDDT, 
T1.CLAIMREOPENEDDT, 
T1.RESERVEOPENEDDT, 
T1.RESERVECLOSEDDT, 
T1.RESERVEREOPENEDDT

Your default date format is something like 'dd-mm-yyyy' and you are naively using REPLACE to try to convert this into 'ddmmyyyy'

IF this is the case, replace the REPLACE commands with TO_CHAR(<field_name>,<format you want the date in>)

The error you are getting is because the result of your REPLACE statement is longer than the CHAR(10) you are trying to put it into.
Re: replace function [message #222204 is a reply to message #222072] Fri, 02 March 2007 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
REPLACE(T1.TRANSACTIONPROCESSDT,'-',''),

Is that replacement string an empty string ? thne just take it out
Urrr.... no.
It's doing something:
SQL> SELECT REPLACE ('12-34-5678','-','')
  2  FROM dual;

REPLACE(
--------
12345678
Re: replace function [message #222237 is a reply to message #222203] Fri, 02 March 2007 07:31 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Would the fact that I am using a system account cause that sort of error?

Also, I have done more testing and removing the replace function entirely still generates the same error.

ORA-03113: end-of-file on communication channel


and the following fields are not date - they are char

T1.POLICYEFFDT,
T1.CLAIMLOSSDT,
T1.CLAIMREPORTEDDT,
T1.CLAIMOPENEDDT,
T1.CLAIMCLOSEDDT,
T1.CLAIMREOPENEDDT,
T1.RESERVEOPENEDDT,
T1.RESERVECLOSEDDT,
T1.RESERVEREOPENEDDT

The table is a staging file - all the fields are char, with a few exceptions.

Re: replace function [message #222239 is a reply to message #222237] Fri, 02 March 2007 07:38 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
In an effort to determine the problem, I have shortened the select statement, but still get the communication error.

any thoughts
SELECT

ROW_NUM,
T1.CLAIMNO,
T1.RESERVENO,
T1.TRANSACTIONPROCESSDT,
T1.REPORTING_DATE
INTO VROW_NUM,
VCLAIMNO ,
VRESERVENO ,
VTRANPRODTCHAR ,
VREPORTING_DATE
FROM IRF_CLAIMS_STAGING1 t1
WHERE ROW_NUM = VICURRENTROWID;
Re: replace function [message #222251 is a reply to message #222239] Fri, 02 March 2007 09:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ORA-3113 are a Metalink problem I'm afraid. There's not generally a great deal that can be done to fix them.

A couple of things that are worth looking at:

What line in the package is it falling over at? Are you certain it's the SELECT statement - if it is, you should be able to reproduce the problem by running the select from SQL*Plus.

Do you have any function based indexes on this table, and if so, are they valid?

Is there anything in the Alert.log, or any trace file generated?
Re: replace function [message #222299 is a reply to message #222070] Fri, 02 March 2007 13:13 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
There are no indexes on this table.

I have copied and successfully executed the select statement in sqlplus -

Re: replace function [message #222491 is a reply to message #222299] Mon, 05 March 2007 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is it definitely this select statement that the package is falling over on?

Comment out everything else but the select and see if it still fails.
Re: replace function [message #222560 is a reply to message #222491] Mon, 05 March 2007 07:29 Go to previous messageGo to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
you're right - it doesn't seem to be the select statement. I took you advice and commented everything except for the select statement

Now, I get the following error
ORA-01403: no data found
ORA-06512: at "SYSTEM.P_CLAIMS_ERROR_CHK_TIME_PKG", line 376
ORA-06512: at line 1

It appears to be at this statement - How can I display the value of the variable VINEXTROWID?

IF (VINEXTROWID) = 0
THEN
EXIT;
END IF;
-- GET THE NEXT ROW.
SELECT ROW_NUM INTO VICURRENTROWID
FROM IRF_CLAIMS_STAGING1
WHERE ROW_NUM = VINEXTROWID;

Re: replace function [message #222574 is a reply to message #222560] Mon, 05 March 2007 08:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No - that's a completely different error that you're getting.
ORA-3113 was the original error.
This is an ORA-1403, which just means that you've done a SELECT statement, and it has returned no data.

If you just want to see the value of VINEXTROWID, you could add this line in just before the SELECT statement:
RAISE_APPLICATION_ERROR(-20999,'Value of VINEXTROWID is '||VINEXTROWID);
Previous Topic: How to Copy drop table one schma to another schma
Next Topic: How to use UPDATE command in TABLE FUNCTION with PIPELINED
Goto Forum:
  


Current Time: Sun Dec 04 06:54:51 CST 2016

Total time taken to generate the page: 0.10087 seconds