Execution of variable throwing error

Question

I am creating a plslq program. In that the query needs to be generated dynamically according to the table names specified. I am able to generate the query in a variable. My question is how to execute the query in the variable using plsql. Execute / Execute Immediate is not working here.

    DECLARE
      f UTL_FILE.FILE_TYPE;
      s VARCHAR2(200);
      c number:=0;
    query varchar(32767);
    BEGIN
--Reading and getting the value from a text file. The text file contains lot of table names
       f := UTL_FILE.FOPEN('DATADIR_EXP1','Table.txt','R');
      LOOP
       UTL_FILE.GET_LINE(f,s);
       DBMS_OUTPUT.PUT_LINE(s);
       IF C <> 0 THEN
       query := query || ' UNION ALL';
       END IF;
--Query is generated here.
       query := query || ' SELECT '''||s||''' AS TABLE_NAME,MIn(Updated_Time) AS MIN_VALUE,MAX(Updated_Time) AS MAX_VALUE,count(*) AS NUMBER_OF_ROWS FROM ' || s ;
       c:=c+1;
      END LOOP;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
             UTL_FILE.FCLOSE(f);
             DBMS_OUTPUT.PUT_LINE('Number of lines: ' || c);
    DBMS_OUTPUT.PUT_LINE(query);
-- The problem is here. Execute / Execute Immediate is not working.
    EXECUTE IMMEDIATE(query);
             UTL_FILE.FCLOSE(f);
    END;
    /

How to accomplish this task. I just have to execute the query.

Well, what error are you getting? Your results need to be returned somehow. — OldProgrammer, Aug 23 '13 at 11:58
There's no point simply "executing" a SELECT statement. You have to want to _do_ something with it. So, do you want to read all the data into a type, return an open cursor etc? — Ben, Aug 23 '13 at 12:11

score 0 · Answer 1 · answered Aug 23 '13 at 12:10

You need to bind the output columns of your SELECT statement to some output variables. Otherwise, you are just executing the statement, and nothing is returned. Here is an example:

DECLARE
   v1   NUMBER (10);
   v2   VARCHAR2 (20);
BEGIN
   EXECUTE IMMEDIATE 'select 1, ''hello'' from dual' INTO v1, v2;

   DBMS_OUTPUT.put_line ('v1 = ' || v1);
   DBMS_OUTPUT.put_line ('v2 = ' || v2);
END;

(output)

v1 = 1
v2 = hello

This will only work if you are returning one row. If the query is returning multiple rows, you need to open the results into a cursor. Example:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 1000;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
   ...
END;

See the oracle documentation

If there is more than one row you could also bulk collect into a type; which is appropriate just depends on how the results of the SELECT are going to be used, which we haven't been told... — Ben, Aug 23 '13 at 12:21

Execution of variable throwing error

1 Answers1