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

1700: Could not connect with username/password 'apps/pwd@VIS' ('ORA-12557: TNS:protocol adapter not loadable').

Oracle Workflow Builder might throw the error

1700: Could not connect with username/password 'apps/pwd@VIS' ('ORA-12557: TNS:protocol adapter not loadable'). 

CAUSE

ORACLE_HOME environment variable statically set in the Windows Users Environment settings.

SOLUTION

To implement the solution, please execute the following steps:

Unset/rename the ORACLE_HOME environment variable in the Windows Users Environment setting so the Oracle WorkFlow Builder sub-components can freely reference libraries or executables from the adjacent Oracle Home locations.

Unix script to extract File Name and Base Path from File Path

$ VAR=/home/me/mydir/file.c
$ DIR=$(dirname "${VAR}")
$ echo "${DIR}"
/home/me/mydir
$ basename "${VAR}"
file.c

PLSQL Table Type

Hi , Some time we may need to process a table of records for which you might need to create a temporary table to process the records.

To avoid SQL Table we can use PLSQL table as given below

DECLARE
   TYPE t_record IS TABLE OF VARCHAR (30);
   TYPE t_tab IS TABLE OF t_record;

   v_tab   t_tab := t_tab ( t_record ('1', '2', '3')
                          , t_record ('A', 'B', 'C')
                          );
BEGIN
   FOR i IN 1 .. v_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line (v_tab (i) (1) || v_tab (i) (2) || v_tab (i) (3));
   END LOOP;
END;
/