CODEWITHSUNDEEP
oracle-databaseplsql
Manas
Manas

Reputation: 177

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

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..

Upvotes: 0

Views: 60

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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;

Upvotes: 1

Related Questions