Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - Prakash

Pages: [1]
1
In addition to above details to this error/exception:

ORA-01422: exact fetch returns more than requested number of rows

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT  or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

2
Perform the following to mitigate this error.

1] Compile the package again with alter command.
2] Drop and recreate the package
3] Disconnect and connect the session again.
4] Stop and start the services on the application server/web server.
5] Check if any objects have changed in the signature of the procedure being called.

3
PSOUG / Re: Date
« on: October 07, 2013, 09:57:27 AM »
This is just an idea - it need to be tried on your schema and system. Thinking that you want a grand total (sum) for certain months.

Code: [Select]
SELECT "Grand Total - June 2009 - September 2009", SUM(plan_total)
FROM (SELECT TO_CHAR(c.END_DATE,'MM-YYYY') years,
   SUM (b.plan_total_lcl) plan_total,
   SUM (b.actual_total_LCL) actual_total
   FROM KCST_BUDGET_PERIOD_SUM a,
   KCST_CURRENCY_LINES b,
   KNTA_PERIODS c
   WHERE a.BUDGET_ID = (SELECT z.PRJ_BUDGET_ID FROM KCRT_FG_PFM_PROJECT z WHERE z.REQUEST_ID = 43985)
   AND a.currency_line_id = b.currency_line_id
   AND c.period_id = a.period_id
   GROUP BY TO_CHAR(c.END_DATE,'MM YYYY')
   ORDER BY 1) X
WHERE to_char(Years,'MM-YYYY') between to_char('01-06-2009', 'MM-YYYY') to to_char('01-09-2009','MM-YYYY');

4
PSOUG / Re: Date Question
« on: October 04, 2013, 02:04:41 PM »
/* Formatted on 10/4/2013 4:02:34 PM (QP5 v5.240.12305.39476) */
SELECT CASE
          WHEN TO_CHAR (TO_DATE (SYSDATE, 'dd/mm/yyyy'), 'DAY') = 'MONDAY'
          THEN
             SYSDATE + 4
          WHEN TO_CHAR (TO_DATE (SYSDATE, 'dd/mm/yyyy'), 'DAY') <> 'MONDAY'
          THEN
             SYSDATE - 1
       END
          expected_date
  FROM DUAL;

5
PSOUG / exact fetch returns more than requested number of rows
« on: October 03, 2013, 01:49:11 PM »

-- Example to show error message - "exact fetch returns more than requested number of rows"
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL, (SELECT * FROM all_users);
END;

--Solution to correct this is by fetching rows that can be accommodated into the catching variable.
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL;
   -- SELECT * FROM all_users; --Can't do this here
END;

-- This error occurs when more number of rows are retrieved and being assigned to a variable
-- that can hold only one single value. You need to retrieve only row(s) that can be held in the
-- catching variable

6
PSOUG / Date Diff Function Not provided by Oracle- Here is one
« on: September 25, 2013, 03:49:31 PM »
Code: [Select]
CREATE OR REPLACE FUNCTION datediff (p_d1 IN DATE, p_d2 IN DATE)
   RETURN NUMBER
AS
   l_result   NUMBER;
BEGIN
   SELECT (p_d2 - p_d1) + 1 INTO l_result FROM DUAL;

   RETURN l_result;
END;

7
PSOUG / sf_week_day_order
« on: September 25, 2013, 03:48:01 PM »
Is there an automated function or procedure that gives the day of the week based on day and sorts the results by week days like SUN- MON - TUE - WED - THU - FRI - SAT.

I wrote this below function that may help return an order for the passed in day

Code: [Select]
CREATE OR REPLACE FUNCTION sf_week_day_order (weekday IN VARCHAR2)
RETURN NUMBER
AUTHID current_user
IS
/******************************************************************************
   NAME:       sf_week_day_order
   PURPOSE:    Function to return the order of the day in a week SUN - WED - SAT

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/12/2013   prakash.gumudavelly       1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     sf_week_day_order
      Sysdate:         9/12/2013
      Date and Time:   9/12/2013, 10:57:46 AM, and 9/12/2013 10:57:46 AM
      Username:        prakash.gumudavelly (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   IF weekday = 'SUNDAY'
   THEN
      RETURN 1;
   ELSIF weekday = 'MONDAY'
   THEN
      RETURN 2;
   ELSIF weekday = 'TUESDAY'
   THEN
      RETURN 3;
   ELSIF weekday = 'WEDNESDAY'
   THEN
      RETURN 4;
   ELSIF weekday = 'THURSDAY'
   THEN
      RETURN 5;
   ELSIF weekday = 'FRIDAY'
   THEN
      RETURN 6;
   ELSIF weekday = 'SATURDAY'
   THEN
      RETURN 7;
   END IF;
END sf_week_day_order;

Pages: [1]