Execute into dynamic number of columns PL/SQL-CodePudding

I am trying to write a dynamic sql script which queries different columns and execute into them to different variables at different conditions. My tables is similar to default hr.departments table so I give my example from there for privacy issues.

My table is :

And my PL/SQL query script is like below. It returns ORA-01007 variable not in select list error. How can i fix this error? Is it possible to iteratively change execute into variables ?:



SET SERVEROUTPUT ON;
DECLARE
V_DEPARTMENT_ID  NUMBER(4);
V_DEPARTMENT_NAME NUMBER(4);
V_MANAGER_ID NUMBER(6);
V_LOCATION_ID NUMBER(4);
V_ID NUMBER(4) := 1;
V_QUERY VARCHAR2(500);
V_SQL VARCHAR2(500);

BEGIN
FOR V_ID IN 1..4
LOOP
IF V_ID =1
THEN V_QUERY:= 'DEPARTMENT_ID';
ELSIF V_ID =2 THEN V_QUERY:= 'DEPARTMENT_ID,DEPARTMENT_NAME';
ELSIF V_ID =3  THEN V_QUERY:= 'DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID';
ELSE    V_QUERY:= 'DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID';
END IF;
V_SQL := 'SELECT '|| V_QUERY || ' FROM HR.DEPARTMENTS';
EXECUTE IMMEDIATE V_SQL INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME,V_MANAGER_ID,V_LOCATION_ID;
END LOOP;

END;

This query should execute first,

SELECT DEPARTMENT_ID INTO V_DEPARTMENT_IDFROM HR.DEPARTMENTS

Then

SELECT DEPARTMENT_ID,DEPARTMENT_NAME INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME FROM HR.DEPARTMENTS

Then

SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME,V_MANAGER_ID FROM HR.DEPARTMENTS

Then

SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME,V_MANAGER_ID,V_LOCATION_ID FROM HR.DEPARTMENTS

How can I do this ?

CodePudding user response：

Error says that there's mismatch in number of columns in select column list and variables you're selecting these values into. The following code shows how to fix that, but - in vain, it still won't work if table doesn't contain exactly one row (because, if it is empty, you'll get no_data_found; if it has more than a single row, you'll get too_many_rows):

Sample data:

SQL> SELECT * FROM DEPARTMENTS;

DEPARTMENT_ID DEPARTMENT_NAM MANAGER_ID LOCATION_ID
------------- -------------- ---------- -----------
           10 Administration        200        1700
           20 Marketing             201        1800

Your code, fixed:

SQL> DECLARE
  2    V_DEPARTMENT_ID   NUMBER(4);
  3    V_DEPARTMENT_NAME NUMBER(4);
  4    V_MANAGER_ID      NUMBER(6);
  5    V_LOCATION_ID     NUMBER(4);
  6    V_ID              NUMBER(4) := 1;
  7    V_QUERY           VARCHAR2(500);
  8    V_SQL             VARCHAR2(500);
  9  BEGIN
 10    FOR V_ID IN 1..4 LOOP
 11      IF V_ID = 1 THEN
 12         V_QUERY := 'DEPARTMENT_ID';
 13         V_SQL := 'SELECT '|| V_QUERY || ' FROM DEPARTMENTS';
 14         EXECUTE IMMEDIATE V_SQL INTO V_DEPARTMENT_ID;
 15      ELSIF V_ID = 2 THEN
 16         V_QUERY := 'DEPARTMENT_ID, DEPARTMENT_NAME';
 17         V_SQL := 'SELECT '|| V_QUERY || ' FROM DEPARTMENTS';
 18         EXECUTE IMMEDIATE V_SQL INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME;
 19      ELSIF V_ID = 3  THEN
 20         V_QUERY := 'DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID';
 21         V_SQL := 'SELECT '|| V_QUERY || ' FROM DEPARTMENTS';
 22         EXECUTE IMMEDIATE V_SQL INTO V_DEPARTMENT_ID,V_DEPARTMENT_NAME,V_MANAGER_ID;
 23      ELSE
 24         V_QUERY := 'DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID';
 25         V_SQL := 'SELECT '|| V_QUERY || ' FROM DEPARTMENTS';
 26         EXECUTE IMMEDIATE V_SQL INTO V_DEPARTMENT_ID, V_DEPARTMENT_NAME, V_MANAGER_ID, V_LOCATION_ID;
 27      END IF;
 28    END LOOP;
 29  END;
 30  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 14

Shortly, that's probably wrong way to do what you're trying to do. What exactly are you trying to do? What is its purpose? Maybe a function that returns refcursor would be a better idea? Something like this:

SQL> create or replace function f_test (p_id in number)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    if p_id = 1 then
  7       open l_rc for select department_id from departments;
  8    elsif p_id = 2 then
  9       open l_rc for select department_id, department_name from departments;
 10    elsif p_id = 3 then
 11       open l_rc for select department_id, department_name, manager_id from departments;
 12    else
 13       open l_rc for select * from departments;
 14    end if;
 15
 16    return l_rc;
 17  end;
 18  /

Function created.

Testing:

SQL> select f_test(2) from dual;

F_TEST(2)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 Administration
           20 Marketing


SQL>

CodePudding user response：

Though I don't get it (selecting into variables over and over...) here is the code that does it just for one EMPNO (old emp table, sorry - could be looped for others):

SET SERVEROUTPUT ON;
DECLARE
    V_EMPNO   NUMBER(4);
    V_ENAME   VARCHAR2(16);
    V_JOB     VARCHAR2(16);
    V_DEPTNO  VARCHAR2(16);
    V_SQL     VARCHAR2(255);
    V_QUERY   VARCHAR2(255);
BEGIN
    FOR i IN 1..4 LOOP
          IF i =1 THEN V_QUERY:= 'EMPNO, Null, Null, Null';
          ELSIF i =2 THEN V_QUERY:= 'EMPNO, ENAME, Null, Null';
          ELSIF i =3 THEN V_QUERY:= 'EMPNO, ENAME, JOB, Null';
          ELSE V_QUERY:= 'EMPNO, ENAME, JOB, DEPTNO';
          END IF;
          V_SQL := 'SELECT ' || V_QUERY || ' FROM EMP WHERE EMPNO = 7939';
          EXECUTE IMMEDIATE V_SQL INTO V_EMPNO, V_ENAME, V_JOB, V_DEPTNO;
          DBMS_OUTPUT.PUT_LINE(V_EMPNO || ' ' || V_ENAME || ' ' || V_JOB || ' ' || V_DEPTNO);
    END LOOP;
END;
/
--  
--  R e s u l t :
--  anonymous block completed
--  7939   
--  7939 HILLER  
--  7939 HILLER CLERK 
--  7939 HILLER CLERK 10