Oracle select into variable, ERROR ORA-00947 not enough values

Question

I want to grab a count of distinct ASN_NO's from my query to later check in my stored procedure if i find more than one and throw an error.

Instead of trying to open the cursor(which i was also failing at doing properly), i thought maybe i could do it with temp table selects and store the value while i'm populating the cursor. Maybe this isn't possible, but my error makes no sense to me.

Here is my simplified code, i broke the count statement out to identify better where exactly the error was.

V_ASN_COUNT           NUMBER;

OPEN O_CURSOR FOR  
  WITH O_LIST AS(
     SELECT *
       FROM AN_ORDER_INFO OI, AN_SHIPMENT_INFO SI
       -- where bunch of stuff
  ),   
  COUNT_ASN_NO AS  (
          SELECT COUNT(DISTINCT ASN_NO) AS "ASN_COUNT"
          FROM O_LIST 
  ),
  SAVE_ASN_COUNT AS (
   SELECT ASN_COUNT
   INTO V_ASN_COUNT
   FROM COUNT_ASN_NO -- error on this line, not enough values, its just 1:1, i dont get it?
  ) 
  SELECT * FROM O_LIST;   

IF(V_ASN_COUNT > 1) THEN
    RAISE MULTIPLE_ASNS;
END IF;   

Or perhaps i need to open the cursor afterwards and do something like this, except i know this is wrong, i get "expecting BULK INTO" error:

  OPEN O_CURSOR; 
      LOOP
          FETCH COUNT(DISTINCT ASN_NO) INTO V_ASN_COUNT;
          EXIT WHEN ASN_NO%NOTFOUND;
      END LOOP;
  CLOSE O_CURSOR;

Answer 1

You can't have an into in the middle of a cursor statement; it's that which is throwing the exception. If your o_list CTE was only selecting a single value then this would run, but v_asn_count would still be null afterwards. With multiple columns selected in o_list it gets the ORA-00947. (This is possibly a parser bug; arguably it should either error just because there is an into clause, or use the select list from the correct CTE query).

It isn't really clear if you need the cursor at all and are trying to reduce code duplication, but it looks like you really just want to do:

SELECT COUNT(DISTINCT ASN_NO)
INTO V_ASN_COUNT
FROM AN_ORDER_INFO OI, AN_SHIPMENT_INFO SI
-- where bunch of stuff
;

IF(V_ASN_COUNT > 1) THEN
  RAISE MULTIPLE_ASNS;
END IF;

(Your where bunch of stuff presumably includes the join conditions; it's off-topic but you might want to think about using ANSI join syntax).

If you have an existing cursor and you want to count the distinct values separately from (and before) actually consuming the cursor you could open it, iterate over it to examine the asn_no values, and then raise an exception if needed; and then for the actual consumption close and reopen the cursor. But that would still execute the cursor query twice.

Or if your processing, particularly the fetch, can accommodate it, you could add an analytic count to the existing cursor query:

COUNT(DISTINCT dummy) OVER (PARTITION BY NULL) AS ASN_COUNT

... which would give you the number of distinct asn_no values across the entire result set as an extra column on every row of that result set. You could then check that number after your first fetch before doing anything else, and raise the exception at that point.

That isn't going to work if you have to count in this procedure but return the cursor to another procedure/caller; the caller would have to check the results and raise the exception, which probably isn't how you see this working.