Error while executing query stored in a variable within FOR loop in stored procedure

Question

While running this prepared statement

DECLARE

BEGIN
-- Get the list of dependent tables and store it in a variable.
FOR cons IN (SELECT A.TABLE_NAME 
    FROM ALL_CONSTRAINTS A, ALL_CONSTRAINTS B
    WHERE A.CONSTRAINT_TYPE = 'xxx'
    AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND A.R_OWNER  = B.OWNER
    AND B.TABLE_NAME = 'MY_TABLE'
    AND B.OWNER = 'DBA')

LOOP
    SET @querytext = CONCAT('SELECT * FROM ',cons.TABLE_NAME);

        PREPARE stamquery FROM @querytext;

        EXECUTE stamquery;

        DEALLOCATE PREPARE stamquery;

END LOOP;
END;

I am getting the error stating:

Encountered the symbol "STAMQUERY" when expecting one of the following:

   := . ( @ % ;

I am new with procedures but looks like this is the way to do it based on my research on the internet.

Please let me know what am I doing wrong..

SET @querytext = ... is invalid PL/SQL. Where in the manual did you find tha? — user330315
– user330315, Commented Oct 30, 2017 at 22:36

Kaushik Nayak · Accepted Answer · 2017-10-31 11:41:48Z

1

The Syntax you are using for PL/SQL is not correct. It should be something like this.

DECLARE
  querytext   VARCHAR2(1000); --declare the variable to store query.
  v_tab_count NUMBER;
BEGIN
  -- Get the list of dependent tables and store it in a variable.
  FOR cons IN
  (
    SELECT
      A.TABLE_NAME
    FROM
      ALL_CONSTRAINTS A,
      ALL_CONSTRAINTS B
    WHERE
      A.CONSTRAINT_TYPE     = 'xxx'
    AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND A.R_OWNER           = B.OWNER
    AND B.TABLE_NAME        = 'MY_TABLE'
    AND B.OWNER             = 'DBA'
  )
  LOOP
    querytext := CONCAT('SELECT COUNT(*) FROM ',cons.TABLE_NAME);
    EXECUTE IMMEDIATE querytext INTO v_tab_count ; --dynamicall execute the
    -- select statement.
    DBMS_OUTPUT.PUT_LINE( 'TABLE ' ||cons.TABLE_NAME||' COUNT '||v_tab_count);
    -- Display the table name and its count of rows.
  END LOOP;
END;

answered Oct 31, 2017 at 11:41

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manas Over a year ago

Awesome Thanks :)

Collectives™ on Stack Overflow

Error while executing query stored in a variable within FOR loop in stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related