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 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #664465 is a reply to message #664464] Wed, 19 July 2017 06:35 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's no way the code you posted, that is throwing a compilation error, is also throwing the run time error mentioned in the thread title.
So you need to explain more what is going on.
You also need to post your code in code tags as described here: How to use [code] tags and make your code easier to read?
And you need to respond to your previous thread.
Re: ORA-1555 during pl/sql pocedure [message #664469 is a reply to message #664465] Wed, 19 July 2017 12:41 Go to previous messageGo to next message
joy_division
Messages: 4823
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Wed, 19 July 2017 07:35
There'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 Go to previous messageGo to next message
Bill B
Messages: 1718
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 #664471 is a reply to message #664463] Wed, 19 July 2017 14:37 Go to previous messageGo to next message
Alien
Messages: 276
Registered: June 1999
Senior Member
Hi,
Joy_division and Bill_B already gave the reason for the PLS-410.
I'd just like to point out some obvious bugs in your code. (Looks like Oracle eBS to me)

1) Remove the 'WHEN OTHERS'. It will not do anything useful, other than give the wrong error message and truncate the error stack.
2) You probably realize the select is bound to return more than one row and throw an exception. You should handle that exception explicitly in your exception clause, or change your select.
3) The trunc around fcr.request_date is meaningless. It is the date the request was submitted. This will always be equal or less than sysdate. Truncating it does not make a difference, other than confusing the optimizer.

Regards,

Arian
Re: ORA-1555 during pl/sql pocedure [message #664494 is a reply to message #664469] Thu, 20 July 2017 03:19 Go to previous message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Wed, 19 July 2017 18:41
cookiemonster wrote on Wed, 19 July 2017 07:35
There'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.
Previous Topic: DBMS_JOB (NEXT_DATE / INTERVAL)
Next Topic: select from two tables
Goto Forum:
  


Current Time: Sat Feb 24 14:55:39 CST 2018

Total time taken to generate the page: 0.06818 seconds