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
PSOUG / How to Reset Sequence in Oracle (Single Sequence)
« on: August 12, 2014, 01:08:47 PM »
-- Get the max value of the column where the sequence is being used, max_value
SELECT MAX (column_name) + 1
  FROM table_name;

-- Get the current value of the sequence, l_value
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the current value of the sequence
ALTER SEQUENCE sequence_name INCREMENT BY -l_value MINVALUE 0;

-- To make the above DDL effective, execute the below command and make the sequence value to 0
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to the desired value
ALTER    SEQUENCE sequence_name INCREMENT BY max_value;

-- To make the above DDL effective, execute the below command and make the sequence value to max value of actual column
SELECT sequence_name.NEXTVAL
  FROM DUAL;

-- Alter the sequence to increment by 1 for regular use of the sequence
ALTER  SEQUENCE sequence_name INCREMENT BY 1;

-- To make the above DDL effective, execute the below command and test the sequence that is altered
SELECT sequence_name.NEXTVAL
  FROM DUAL;

2
PSOUG / How to Reset Sequences in Oracle.
« on: August 12, 2014, 12:42:31 PM »
This below SQL script will provide with the sequence names that can be reset that are matching to a table name or a column name

Code: [Select]
SELECT   ao.owner, ao.object_name, t1.table_name, t1.column_name
           FROM all_objects ao,
                (SELECT table_name, column_name
                   FROM all_tab_columns
                  WHERE owner = &owner
                    AND data_type = 'NUMBER'
                    AND column_id = 1
                    AND column_name LIKE '%ID'
                    AND table_name IN (SELECT table_name
                                         FROM all_tables
                                        WHERE owner = &owner)) t1
          WHERE CASE
                   WHEN (   INSTR (ao.object_name, 'SQ') != 0
                         OR INSTR (ao.object_name, 'SEQ') != 0
                         OR INSTR (ao.object_name, 'ID') ! = 0
                        )
                   AND (   t1.table_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                        OR t1.column_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                       )
                      THEN 1
                   WHEN (   INSTR (ao.object_name, 'SQ') = 0
                         OR INSTR (ao.object_name, 'SEQ') = 0
                         OR INSTR (ao.object_name, 'ID') = 0
                        )
                   AND (   t1.table_name = ao.object_name
                        OR t1.column_name = ao.object_name
                       )
                      THEN 1
                   ELSE 0
                END = 1
            AND ao.owner = &owner
            AND ao.object_type = 'SEQUENCE'
       ORDER BY ao.object_name;

Below script can be used to reset sequences on a specific schema on the database.

DECLARE
   max_val   NUMBER;
   l_value   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema = ' || &owner;

   FOR rec_sequence IN
      (SELECT   ao.owner, ao.object_name, t1.table_name, t1.column_name
           FROM all_objects ao,
                (SELECT table_name, column_name
                   FROM all_tab_columns
                  WHERE owner = &owner
                    AND data_type = 'NUMBER'
                    AND column_id = 1
                    AND column_name LIKE '%ID'
                    AND table_name IN (SELECT table_name
                                         FROM all_tables
                                        WHERE owner = &owner)) t1
          WHERE CASE
                   WHEN (   INSTR (ao.object_name, 'SQ') != 0
                         OR INSTR (ao.object_name, 'SEQ') != 0
                         OR INSTR (ao.object_name, 'ID') ! = 0
                        )
                   AND (   t1.table_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                        OR t1.column_name LIKE
                                 SUBSTR (ao.object_name,
                                         1,
                                         LENGTH (ao.object_name) - 4
                                        )
                              || '%'
                       )
                      THEN 1
                   WHEN (   INSTR (ao.object_name, 'SQ') = 0
                         OR INSTR (ao.object_name, 'SEQ') = 0
                         OR INSTR (ao.object_name, 'ID') = 0
                        )
                   AND (   t1.table_name = ao.object_name
                        OR t1.column_name = ao.object_name
                       )
                      THEN 1
                   ELSE 0
                END = 1
            AND ao.owner = &owner
            AND ao.object_type = 'SEQUENCE'
       ORDER BY ao.object_name)
   LOOP
      l_value := 0;
      max_val := 0;

      EXECUTE IMMEDIATE    'select max('
                        || rec_sequence.column_name
                        || ') from '
                        || &owner
                        || '.'
                        || rec_sequence.table_name
                   INTO max_val;

      EXECUTE IMMEDIATE    'select '
                        || &owner
                        || '.'
                        || rec_sequence.object_name
                        || '.nextval from dual'
                   INTO l_value;

      IF max_val > l_value
      THEN
         -- Set a negative increment for the sequence, -- with value = the current value of the sequence
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by -'
                           || l_value
                           || ' minvalue 0';

         -- Select once from the sequence, to -- take its current value back to 0
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;

         -- Set the increment back to max val
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by '
                           || max_val;

         --
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;

         -- Set the increment back to 1
         EXECUTE IMMEDIATE    'alter sequence '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || ' increment by 1';

         --
         EXECUTE IMMEDIATE    'select '
                           || &owner
                           || '.'
                           || rec_sequence.object_name
                           || '.nextval from dual'
                           INTO l_value;
      END IF;
   END LOOP;
END;

3
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.

4
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.

5
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');

6
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;

7
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

8
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;

9
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]