Home » SQL & PL/SQL » SQL & PL/SQL » ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 (ORACLE10 g)
ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584463] Tue, 14 May 2013 09:21 Go to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
Hi All,
i am facing ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 error in below line

SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI') INTO currentTime FROM DUAL;

Can anyone help..

Regards,
Deepak
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584465 is a reply to message #584463] Tue, 14 May 2013 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
deepak416 wrote on Tue, 14 May 2013 07:21
Hi All,
i am facing ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 error in below line

SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI') INTO currentTime FROM DUAL;

Can anyone help..

Regards,
Deepak


reported line does NOT throw error
SQL> declare
  2  currenttime varchar2(63);
  3  begin
  4  SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI') INTO currentTime FROM DUAL;
  5  end;
  6  /

PL/SQL procedure successfully completed.




Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[oracle@localhost ~]$ oerr ora 6512
06512, 00000, "at %sline %s"
// *Cause:   Backtrace message as the stack is unwound by unhandled
//           exceptions.
// *Action:  Fix the problem causing the exception or write an exception
//           handler for this condition. Or you may need to contact your
//           application administrator or DBA.
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584466 is a reply to message #584465] Tue, 14 May 2013 09:30 Go to previous messageGo to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
i can not do this because

after this statement i am using below statement
SELECT TO_CHAR(currentTime - INTERVAL '1' MINUTE,'DD-MON-RR HH24.MI') INTO lastProcessTime FROM DUAL;

Format of currentTime
currentTime TIMESTAMP := NULL;
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584467 is a reply to message #584466] Tue, 14 May 2013 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584468 is a reply to message #584467] Tue, 14 May 2013 09:35 Go to previous messageGo to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
CREATE OR REPLACE PROCEDURE GetUeSlidRegTime (
lastSuccessfullProcessTime IN OUT TIMESTAMP,
resultSet OUT SYS_REFCURSOR,
currentProcessTime OUT TIMESTAMP,
returnStatus OUT NUMBER) AS
--

lastProcessTime TIMESTAMP := NULL;
currentTime TIMESTAMP := NULL;
timeDifferenceInMinutes NUMBER;
maxAllowedMinsData NUMBER;
--
BEGIN
--
write_log('GetUeSlidRegTime',4,'Starting of GetUeSlidRegTime procedure');
--
-- set returnStatus to 0
--
returnStatus := 0;
--
-- Get current timestamp value from dual
--
write_log('GetSlidData',4,'Current DB Time Start: ' || currentTime);
BEGIN
SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI.SS') INTO currentTime FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
write_log('GetSlidData',4,'EXCEPTION IN THIS');
--
END;
write_log('GetSlidData',4,'Current DB Time : ' || currentTime);
--
-- Check lastSuccessfullProcessTime value, if it is null then fetch LAST_PROCESS_TIME_PER_MINUTE
-- value from BLM_CONFIGURATIONS table
--
IF (lastSuccessfullProcessTime IS NULL) THEN
--
BEGIN
--
-- Get LAST_PROCESS_TIME_PER_MINUTE value from BLM_CONFIGURATIONS table
--
SELECT PARAM_VALUE INTO lastProcessTime FROM BLM_CONFIGURATIONS WHERE PARAM_NAME='LAST_PROCESS_TIME_PER_MINUTE';
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
--
-- Insert NULL into BLM_CONFIGURATIONS as LAST_PROCESS_TIME_PER_MINUTE time
--
INSERT INTO BLM_CONFIGURATIONS(PARAM_NAME, PARAM_VALUE) VALUES ('LAST_PROCESS_TIME_PER_MINUTE', NULL);
--
END;
--
-- Check lastProcessTime value, if it is null then set lastProcessTime value as currentTime minus 1 minute
-- Else get the difference in minutes between lastProcessTime and currentTime
--
IF (lastProcessTime IS NULL) THEN
--
SELECT TO_CHAR(currentTime - INTERVAL '1' MINUTE,'DD-MON-RR HH24.MI') INTO lastProcessTime FROM DUAL;
--
ELSIF (lastProcessTime IS NOT NULL) THEN
--
timeDifferenceInMinutes := timestampDiff (lastProcessTime, currentTime);
--
END IF;
--
--
-- Check lastSuccessfullProcessTime value, if it is not null then get the time difference value
-- between currentTime and lastProcessTime in minutes
--
ELSIF (lastSuccessfullProcessTime IS NOT NULL) THEN
--
-- Set lastProcessTime to lastSuccessfullProcessTime
--
lastProcessTime := lastSuccessfullProcessTime;
--
timeDifferenceInMinutes := timestampDiff (lastProcessTime, currentTime);
--
END IF;
--
write_log('GetSlidData',4,'lastProcessTime : ' || lastProcessTime);
write_log('GetSlidData',4,'timeDifferenceInMinutes : ' || timeDifferenceInMinutes);
--
--
-- Fetch 'MAX_ALLOWED_MINUTES_DATA_FTP_PER_MINUTE' from BLM_CONFIGURATIONS table
--
BEGIN
--
maxAllowedMinsData := getBLMConfig('MAX_ALLOWED_MINUTES_DATA_FTP_PER_MINUTE',30);
--
EXCEPTION
WHEN OTHERS THEN
maxAllowedMinsData := 30;
--
END;
--
-- Check the maxAllowedMinsData value, if it 0 or NULL then set it to 30
--
IF (maxAllowedMinsData = 0 OR maxAllowedMinsData IS NULL) THEN
--
maxAllowedMinsData := 30;
--
END IF;
--
write_log('GetSlidData',4,'maxAllowedMinsData : ' || maxAllowedMinsData);
--
--
-- Check timeDifferenceInMinutes value, if it is less than 1 minute then return from procedure without doing any
-- furture processing, If it is greater than configured 'MAX_ALLOWED_MINUTES_DATA_FTP_PER_MINUTE' value then set
-- currentTime value as lastProcessTime plus timeDifferenceInMinutes minutes
--
IF (timeDifferenceInMinutes < 1) THEN
--
OPEN resultSet FOR SELECT 1 from dual;
returnStatus := 1;
RETURN;
--
ELSIF (timeDifferenceInMinutes > maxAllowedMinsData) THEN
--
BEGIN
SELECT TO_CHAR(lastProcessTime + numtodsinterval(maxAllowedMinsData, 'MINUTE') ,'DD-MON-RR HH24.MI') INTO currentTime FROM DUAL;
--
END;
END IF;
--
-- Fetch records from SBRUEREGISTER table and corresponding SLID of the matching FAPID from SBRFAPSLID table
-- between lastProcessTime and currentTime time period
-- Note: Including lastProcessTime but excluding currentTime
--
OPEN resultSet FOR SELECT SBRUEREGISTER.UEIMSI, SBRUEREGISTER.REGISTEREDAT, SBRFAPSLID.SLID FROM SBRUEREGISTER, SBRFAPSLID
WHERE SBRUEREGISTER.FAPID = SBRFAPSLID.FAPID AND SBRUEREGISTER.REGISTEREDAT >= TO_TIMESTAMP(lastProcessTime) AND
SBRUEREGISTER.REGISTEREDAT < TO_TIMESTAMP(currentTime) AND SBRFAPSLID.DEACTIVATION_TIMESTAMP IS NULL ORDER BY SBRUEREGISTER.REGISTEREDAT;
--
-- Set currentTime as currentProcessTime
--
currentProcessTime := currentTime;
--
--
-- Check lastSuccessfullProcessTime value, if it is null then set lastProcessTime value as lastSuccessfullProcessTime
--
IF (lastSuccessfullProcessTime IS NULL) THEN
--
lastSuccessfullProcessTime := lastProcessTime;
--
END IF;
--
-- Update 'LAST_PROCESS_TIME_PER_MINUTE' value with lastProcessTime
--
UPDATE BLM_CONFIGURATIONS SET PARAM_VALUE = lastProcessTime WHERE PARAM_NAME = 'LAST_PROCESS_TIME_PER_MINUTE';
--
--
write_log('GetUeSlidRegTime',4,'End of GetUeSlidRegTime procedure');
--
EXCEPTION
WHEN OTHERS THEN
--
write_log('GetUeSlidRegTime',4,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
--
END GetUeSlidRegTime;
/
show errors;




GetUeSlidRegTime : ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584470 is a reply to message #584468] Tue, 14 May 2013 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Understand Reality!
We don't have your tables or data, so we can't run, test, debug, improve posted code.
ORA-06512 is a RUNTIME error & you did not show us how procedure was invoked & with which parameter values.

When was last time procedure ran without error?
What changed since then?

>SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI.SS') INTO currentTime FROM DUAL;
above is silly & wasteful since same can be done by below

CURRENTTIME := CURRENT_TIMESTAMP;
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584472 is a reply to message #584468] Tue, 14 May 2013 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

cookiemonster wrote on Tue, 26 February 2013 14:05
Please read and follow How to use [code] tags and make your code easier to read?
...


Use SQL*Plus and copy and paste your session, the WHOLE session including the error.

Once again, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel

Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584473 is a reply to message #584470] Tue, 14 May 2013 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

currentTime and lastProcessTime are both timestamps;

So this:
SELECT TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI.SS') INTO currentTime FROM DUAL;

Is in reality doing this:
SELECT to_timestamp(TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI.SS'), <current nls_timestamp format>) INTO currentTime FROM DUAL;
Because oracle has to convert the string back to a timestamp to store it in the timestamp variable.

Why have you got to_char there? What do you think it's doing?
If you're working with timestamps, why are you converting them to other datatypes and back again.

And as Blackswan already pointed out 6512 is a backtrace message. It is not the initial error that caused the problem.
So your error handling is a mess.
I suggest you read when others and remove all your exception handlers

Oh and this:
Quote:

Format of currentTime
currentTime TIMESTAMP := NULL;

shows a datatype, not a format, do not confuse the two.
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584512 is a reply to message #584473] Tue, 14 May 2013 23:27 Go to previous messageGo to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
Now its working fine without any changes. what's going wrong with this.
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584519 is a reply to message #584512] Wed, 15 May 2013 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 14 May 2013 17:27
From your previous topic:

cookiemonster wrote on Tue, 26 February 2013 14:05
Please read and follow How to use [code] tags and make your code easier to read?
...


Use SQL*Plus and copy and paste your session, the WHOLE session including the error.

Once again, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel

Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584523 is a reply to message #584473] Wed, 15 May 2013 00:53 Go to previous messageGo to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
still same error
when i used SELECT to_timestamp(TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI')) INTO currentTime FROM DUAL;
Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584529 is a reply to message #584523] Wed, 15 May 2013 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 15 May 2013 07:22
Michel Cadot wrote on Tue, 14 May 2013 17:27
From your previous topic:

cookiemonster wrote on Tue, 26 February 2013 14:05
Please read and follow How to use [code] tags and make your code easier to read?
...


Use SQL*Plus and copy and paste your session, the WHOLE session including the error.

Once again, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel


Re: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25 [message #584535 is a reply to message #584523] Wed, 15 May 2013 02:37 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
deepak416 wrote on Wed, 15 May 2013 06:53
still same error
when i used SELECT to_timestamp(TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-RR HH24.MI')) INTO currentTime FROM DUAL;


That's probably because your default nls_timestamp_format isn't DD-MON-RR HH24.MI.
Why are you changing the datatype at all?
Why are not doing simple this:
currentTime := CURRENT_TIMESTAMP;
Previous Topic: PL/SQL: numeric or value error(How to use date as an date as input parameter)
Next Topic: Using Bind Variable
Goto Forum:
  


Current Time: Sun Apr 20 04:31:07 CDT 2014

Total time taken to generate the page: 0.06494 seconds