CODEWITHSUNDEEP
plsqloracle-sqldeveloper
Boardman411
Boardman411

Reputation: 531

PL/SQL - Query returns more than one ROW

I'm using plsql to perform return a group of records and then update this records based on the results.. I'm getting stuck...

As my SELECT statements is returnning more than 1 ROW. So how do I go about returning all rows into a VARIABLE.. As I keep getting the following ERROR : PL/SQL: ORA-00947: not enough values

Attached is snippett of code.

BEGIN

-- Set Variables

v_ArchivedDate := '01-JAN-14'; -- Enter Variable
v_Results := '0';

-- This get current date/time minus 31 days ago
SELECT TO_CHAR(systimestamp-31, 'DD-MON-YY HH.MI.SS') INTO v_ArchivedDate 
FROM dual;


dbms_output.put_line('Date to Set Archived_At Column :' || ' ' || v_ArchivedDate);

This is where the script seems to be failing with the above ERROR

          SELECT * INTO v_results
          FROM message
          WHERE from_entity IN
          (SELECT entity_id FROM user_info
          WHERE UPPER(directory_auth_id) = 'USER100');

I'm a novice at plsql, so on a learning curve at the mo..

So do I need to select into some sort of GROUPSET / ARRAY etc.. not sure.

Thanks for looking. Boardman

Upvotes: 0

Views: 3547

Answers (1)

Guneli
Guneli

Reputation: 1731

Assuming that your message table has a column text, you can use the following:

DECLARE
 TYPE t_results IS TABLE OF VARCHAR2(50) INDEX BY pls_integer;
 v_results t_results;
BEGIN
SELECT text BULK COLLECT INTO v_results
          FROM message
          WHERE from_entity IN
          (SELECT entity_id FROM user_info
          WHERE UPPER(directory_auth_id) = 'USER100');
FOR i IN v_results.FIRST .. v_results.LAST
LOOP
   dbms_output.put_line(v_results(i));
END LOOP;
END;

Upvotes: 1

Related Questions