Tuesday, January 9, 2024

Query to get years as list of values


WITH years
     AS (    SELECT ADD_MONTHS (TO_DATE ('01-01-2020', 'DD-MM-YYYY'), LEVEL * 12) year
               FROM DUAL
         CONNECT BY LEVEL <= 30)
  SELECT TO_CHAR (year, 'YYYY') year
    FROM years
   WHERE year < TO_DATE ('01-01-' || TO_CHAR (SYSDATE, 'YYYY'), 'DD-MM-YYYY')
ORDER BY 1 DESC;

Script Output

Year
2023
2022
2021

Thursday, October 25, 2018

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

Hi,

Today I found a new thing in PLSQL. So far i thought when an INTO clause fails due to "ORA-01422: exact fetch returns more than requested number of rows" exception, it wont assign value to the variable.

But its not true. 

Even it throws the exception , the first value is captured in the variable.

Below is a small example for it. 
I tried to query the table with a condition which returns more than one row. 

CREATE TABLE dummy ( vname VARCHAR2 (20), vmark NUMBER);

INSERT INTO dummy VALUES ('Bala', 90);
INSERT INTO dummy VALUES ('Kaarhtik', 100);
INSERT INTO dummy VALUES ('Achu', 100);
INSERT INTO dummy VALUES ('Mithu', 100);

SELECT * FROM dummy;

SET SERVEROUTPUT ON

DECLARE
  v_name   VARCHAR2 (20);
BEGIN
  SELECT vname
    INTO v_name
    FROM dummy
   WHERE vmark = 100
   ORDER BY vname
   ;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Exception occurred : ' || SQLERRM);
    DBMS_OUTPUT.put_line ('Value of v_name is : ' || v_name);
END;
/


Script Output

Exception occurred  : ORA-01422: exact fetch returns more than requested number of rows
Value of v_name is : Achu