Home » SQL & PL/SQL » SQL & PL/SQL » ORA-1555 during pl/sql pocedure (PL/SQL Release 11.2.0.3.0 - Production,windows 10)
ORA-1555 during pl/sql pocedure [message #664463] |
Wed, 19 July 2017 06:23 |
|
priya_t
Messages: 2 Registered: July 2017
|
Junior Member |
|
|
hi,
I have created following procedure and i am getting one error. please give solution.
CREATE OR REPLACE PROCEDURE One_time (
--v_language IN VARCHAR2,
--user_name IN VARCHAR2,
--req_date IN VARCHAR2,
V_request_id OUT NUMBER,
V_user_concurrent_program_name OUT VARCHAR2,
V_responsibility_name OUT VARCHAR2,
V_user_name OUT VARCHAR2,
V_request_date OUT VARCHAR2,
V_actual_completion_date OUT VARCHAR2,
V_actual_start_date OUT VARCHAR2,
Err_msg OUT VARCHAR2)
AS
V_request_id NUMBER;
V_user_concurrent_program_name Fnd_concurrent_programs_tl.User_concurrent_program_name%TYPE;
V_responsibility_name Fnd_responsibility_tl.Responsibility_name%TYPE;
V_user_name Fnd_user.User_name%TYPE;
V_request_date Fnd_concurrent_requests.Request_date%TYPE;
V_actual_completion_date Fnd_concurrent_requests.Actual_completion_date%TYPE;
V_actual_start_date Fnd_concurrent_requests.Actual_start_date%TYPE;
V_exp EXCEPTION;
Err_msg VARCHAR2 (2000);
BEGIN
SELECT Fcr.Request_id,
Fcp.User_concurrent_program_name,
Frt.Responsibility_name,
Fu.User_name,
TO_CHAR (Fcr.Request_date, 'dd-mm-yy hh24:mi:ss'),
TO_CHAR (Fcr.Actual_completion_date, 'dd-mm-yy hh24:mi:ss')
Completion_time,
TO_CHAR (Fcr.Actual_start_date, 'dd-mm-yy hh24:mi:ss')
Start_time
INTO V_request_id,
V_user_concurrent_program_name,
V_responsibility_name,
V_user_name,
V_request_date,
V_actual_completion_date,
V_actual_start_date
FROM Fnd_concurrent_programs_tl Fcp,
Fnd_concurrent_requests Fcr,
Fnd_responsibility_tl Frt,
Apps.Fnd_user Fu
WHERE Fcp.User_concurrent_program_name IN
('RevPro Data Collection',
'RevPro Create and Collect Batch',
'RevPro Events Collection from Staging',
'RevPro Background Contingency Release',
'RevPro Cash Receipt',
'RevPro Events Collection from Staging',
'RevPro Background Contingency Release',
'Revenue Recognition Master Program',
'EQX SalesRep Update Program')
AND Fcp.Language = 'US' --input PARAMETER
AND Fcr.Concurrent_program_id = Fcp.Concurrent_program_id
AND Frt.Responsibility_id = Fcr.Responsibility_id
AND Fcr.Responsibility_application_id = Frt.Application_id
AND Fcp.Language = Frt.Language
AND Fcr.Requested_by = Fu.User_id
AND Fu.User_name LIKE 'SRAMYA' --input PARAMETER
AND TRUNC (Fcr.Request_date) >= TRUNC (SYSDATE) -- change the value 1 and replace it with the required value --input
ORDER BY Fcr.Request_id DESC;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Err_msg := 'DATA NOT EXIST';
RAISE V_exp;
WHEN OTHERS
THEN
Err_msg :=
'DATA NOT EXIST FOR THESE RECORDS'
|| SUBSTR (SQLERRM,
1,
200);
RAISE V_exp;
END;
I formatted it for you. Next time please do it yourself
error:
PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted
[Updated on: Thu, 20 July 2017 08:15] by Moderator Report message to a moderator
|
|
|
Re: ORA-1555 during pl/sql pocedure [message #664464 is a reply to message #664463] |
Wed, 19 July 2017 06:25 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You have not given any feedback regarding the assistance you were given for your previous question.
You have ignored requests to use [code] tags to format your post.
Are you someone who deserves help, or are you a useless lazy slug?
|
|
|
|
Re: ORA-1555 during pl/sql pocedure [message #664469 is a reply to message #664465] |
Wed, 19 July 2017 12:41 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Wed, 19 July 2017 07:35There's no way the code you posted
Actually, I think it will happen if you declare variables that are already OUT parameters, of which it seems OP defined them all again.
|
|
|
Re: ORA-1555 during pl/sql pocedure [message #664470 is a reply to message #664469] |
Wed, 19 July 2017 13:50 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
THAT ARE OUT OR IN PARAMETERS.
CREATE OR REPLACE
PROCEDURE ONE_TIME(
--v_language IN VARCHAR2,
--user_name IN VARCHAR2,
--req_date IN VARCHAR2,
v_request_id OUT NUMBER,
v_user_concurrent_program_name OUT VARCHAR2,
v_responsibility_name OUT VARCHAR2,
v_user_name OUT VARCHAR2,
v_request_date OUT VARCHAR2,
v_ACTUAL_COMPLETION_DATE OUT VARCHAR2,
v_ACTUAL_START_DATE OUT VARCHAR2,
ERR_MSG OUT VARCHAR2
)
AS
V_EXP EXCEPTION;
BEGIN
[Updated on: Wed, 19 July 2017 13:51] Report message to a moderator
|
|
|
|
Re: ORA-1555 during pl/sql pocedure [message #664494 is a reply to message #664469] |
Thu, 20 July 2017 03:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
joy_division wrote on Wed, 19 July 2017 18:41cookiemonster wrote on Wed, 19 July 2017 07:35There's no way the code you posted
Actually, I think it will happen if you declare variables that are already OUT parameters, of which it seems OP defined them all again.
I know it'll throw PLS-00410, for the reasons you stated. I was waiting for the OP to post the code formatted before pointing that out.
But given it can't compile there is no way it can throw the ORA-1555 error from the thread title.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:44:46 CDT 2024
|