How to create a view in a FOR loop in Oracle SQL

Question

What I'm trying to do is create views based off a condition between two tables, and I want it to go through all tables that meet this condition.

I've been doing some research and I found that cursors would be helpful for this sort of thing, but I've been running into a "cursor out of scope" at line 15.

DECLARE

  query_str  VARCHAR2(32000);
  CURSOR all_syn IS
    SELECT SYNONYM_NAME, TABLE_NAME
    FROM ALL_SYNONYMS
    WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
  CURSOR our_tables IS
    SELECT TABLE_NAME
    FROM   ALL_TABLES
    WHERE  TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';

BEGIN

  query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(all_syn.SYNONYM_NAME, 'S_') || 'AS
        SELECT TO_CHAR(itemnum) itemnum,
               TO_CHAR(keywordnum) keywordnum,
               TO_CHAR(keysetnum) keysetnum,
               MOD_BY_EMPLOYEE,
               MOD_BY_PROCESS,
               MOD_DATE_EMPLOYEE,
               MOD_DATE_PROCESS
        FROM   all_syn.SYNONYM_NAME,
               our_tables.TABLE_NAME
        WHERE  our_tables.TABLE_NAME = ' || LTRIM(all_syn.SYNONYM_NAME, 'S_');
  FOR v_rec IN all_syn LOOP
IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
 EXECUTE IMMEDIATE query_str;

    END IF;
  END LOOP;
END;

The reason I am doing this is because my company has tables that aren't directly connected to a certain 3rd party DB link, so they had me change the table names by putting a 1 at the end of the affected tables, creating synonyms for these tables with the DB link, and then make views of these synonyms with the original table name so that they now have the DB link and act as the original table so that we don't have to change any code. I have to join the synonym tables with the changed tables, because we added some attributes that the 3rd party tables don't have.

If anyone has any suggestions or advice, it would be greatly appreciated! I'm new to using dynamic sql and PL/SQL, so bear with me please.

EDIT:

So I've improved my code, and I feel like I'm getting closer to my desired results, however I'm getting this weird error:

line 28, column 52: PLS-00357: Table,View Or Sequence reference 'ALL_TABLES.TABLE_NAME' not allowed in this context

Which doesn't make sense to me as I'm declaring it in the query.

BEGIN
  FOR v_rec IN all_syn LOOP
    IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
      query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || ' AS
          SELECT itemnum AS item_num,
                 keywordnum AS key_word_num,
                 keysetnum AS key_set_num,
                 MOD_BY_EMPLOYEE,
                 MOD_BY_PROCESS,
                 MOD_DATE_EMPLOYEE,
                 MOD_DATE_PROCESS,
          FROM   (    SELECT TABLE_NAME
                      FROM   ALL_TABLES
                      WHERE  TABLE_NAME LIKE ' || '''AG!_%1''' || ' ESCAPE ' || '''!''' || '
                         AND ' || RTRIM(ALL_TABLES.TABLE_NAME, '1') ||' = ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || ') our_tables,   
                 ' || v_rec.SYNONYM_NAME;
     -- EXECUTE IMMEDIATE query_str;
    END IF;
              dbms_output.put_line(query_str);
  END LOOP;
END;

Ok so I'm definitely not an Oracle expert, but if your query is outside the loop, is it ever going to change? (it makes sense to me that this would generate a scoping error) — Jacob H
– Jacob H, Commented May 24, 2018 at 15:34

butchyyyy · Accepted Answer · 2018-05-24 17:13:43Z

1

You cannot reference cursor like that. Move the query_str creation inside the FOR LOOP and reference the record variable.

EDIT: I've tried to fix the FROM/WHERE clause, but you might be missing a join condition there.

DECLARE
  query_str  VARCHAR2(32000);
  CURSOR all_syn IS
    SELECT SYNONYM_NAME, TABLE_NAME
    FROM ALL_SYNONYMS
    WHERE SYNONYM_NAME LIKE 'S!_AG!_%' ESCAPE '!';
  CURSOR our_tables IS
    SELECT TABLE_NAME
    FROM   ALL_TABLES
    WHERE  TABLE_NAME LIKE 'AG!_%1' ESCAPE '!';

BEGIN
  FOR v_rec IN all_syn LOOP
    IF (v_rec.TABLE_NAME LIKE 'KEYXITEM%') THEN
      query_str := 'CREATE OR REPLACE VIEW ' || LTRIM(v_rec.SYNONYM_NAME, 'S_') || 'AS
          SELECT TO_CHAR(itemnum) itemnum,
                 TO_CHAR(keywordnum) keywordnum,
                 TO_CHAR(keysetnum) keysetnum,
                 MOD_BY_EMPLOYEE,
                 MOD_BY_PROCESS,
                 MOD_DATE_EMPLOYEE,
                 MOD_DATE_PROCESS
          FROM   ' || v_rec.SYNONYM_NAME || ',
                 ' || v_rec.TABLE_NAME || '
          WHERE  ' || v_rec.TABLE_NAME = ' || LTRIM(v_rec.SYNONYM_NAME, 'S_');
      EXECUTE IMMEDIATE query_str;
    END IF;
  END LOOP;
END;

edited May 24, 2018 at 17:13

answered May 24, 2018 at 15:40

butchyyyy

3551 silver badge10 bronze badges

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

2 Comments

Logan Over a year ago

Thank you for your quick response! Sorry, first time using cursors and loops in sql, trying to get used to it. However, I get a "missing keyword" error on the EXECUTE IMMEDIATE statement, I'm doing some research to figure out why.

butchyyyy Over a year ago

If your query_str is not correct add a dbms_output.put_line(query_str) line into your for loop so you can tune the DDL command itself. The FROM clause seems to be the problem. You have to concatenate the SYNONYM_NAME and TABLE_NAME. I have adjusted the original answer. One more thing you're missing tho is some kind of join condition between the original and 3rd party table or you will end up with a cartesian join.

Collectives™ on Stack Overflow

How to create a view in a FOR loop in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related