sql - PLS-00357: Table,View Or Sequence reference not allowed in this context when execute immediate inside block - Stack Overfl

admin管理员组

文章数量:1294384

I'm trying to use variables in a ALTER SEQUENCE statement inside a BEGIN END block. When hard coding the Schema name and the sequence name it works perfectly. But when I try to use variables instead it fails with and error. I will be running the same sequence alter code for different tables (200+) so it will be a nightmare to maintain this code.

DEFINE PowerTsmUsername = 'EAA_TRADING_POWERTSM_DEV';
DEFINE TradingUsername = 'TRADING';

DEFINE STARTDATE = '1994-01-01';
DEFINE ENDDATE = '2023-01-01';

DEFINE Tablename = 'BGTRANSFERLOG';
DEFINE SeqName = 'SEQ_EAA_TRANSFER_LOG';

DEFINE TriggerOne = 'BL_EAA_TRANSFERLOG_SEQ';


ALTER SESSION ENABLE PARALLEL DML;

ALTER TRIGGER &&PowerTsmUsername..&&TriggerOne DISABLE;

ALTER SEQUENCE &&PowerTsmUsername..&&SeqName RESTART START WITH 1;
TRUNCATE TABLE &&PowerTsmUsername..&&Tablename DROP STORAGE;

INSERT /*+ parallel(8) nologging */ INTO &&PowerTsmUsername..&&Tablename
SELECT * FROM &&TradingUsername..&&Tablename;
COMMIT;

ALTER SESSION DISABLE PARALLEL DML;

ALTER TRIGGER &&PowerTsmUsername..&&TriggerOne ENABLE;

DECLARE dummy NUMBER;

BEGIN
    SELECT MAX(OBJEKTID) into dummy from &&PowerTsmUsername..&&Tablename;
    dummy := dummy + 1;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE EAA_TRADING_POWERTSM_DEV.SEQ_EAA_TRANSFER_LOG RESTART START WITH ' || dummy;

    -- Causes PLS-00357: Table,View Or Sequence reference 'EAA_TRADING_POWERTSM_DEV.SEQ_EAA_TRANSFER_LOG' not allowed in this context
    --EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || &&PowerTsmUsername..&&SeqName || ' RESTART START WITH ' || dummy;
COMMIT;
END;

I'm trying to use variables in a ALTER SEQUENCE statement inside a BEGIN END block. When hard coding the Schema name and the sequence name it works perfectly. But when I try to use variables instead it fails with and error. I will be running the same sequence alter code for different tables (200+) so it will be a nightmare to maintain this code.

DEFINE PowerTsmUsername = 'EAA_TRADING_POWERTSM_DEV';
DEFINE TradingUsername = 'TRADING';

DEFINE STARTDATE = '1994-01-01';
DEFINE ENDDATE = '2023-01-01';

DEFINE Tablename = 'BGTRANSFERLOG';
DEFINE SeqName = 'SEQ_EAA_TRANSFER_LOG';

DEFINE TriggerOne = 'BL_EAA_TRANSFERLOG_SEQ';


ALTER SESSION ENABLE PARALLEL DML;

ALTER TRIGGER &&PowerTsmUsername..&&TriggerOne DISABLE;

ALTER SEQUENCE &&PowerTsmUsername..&&SeqName RESTART START WITH 1;
TRUNCATE TABLE &&PowerTsmUsername..&&Tablename DROP STORAGE;

INSERT /*+ parallel(8) nologging */ INTO &&PowerTsmUsername..&&Tablename
SELECT * FROM &&TradingUsername..&&Tablename;
COMMIT;

ALTER SESSION DISABLE PARALLEL DML;

ALTER TRIGGER &&PowerTsmUsername..&&TriggerOne ENABLE;

DECLARE dummy NUMBER;

BEGIN
    SELECT MAX(OBJEKTID) into dummy from &&PowerTsmUsername..&&Tablename;
    dummy := dummy + 1;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE EAA_TRADING_POWERTSM_DEV.SEQ_EAA_TRANSFER_LOG RESTART START WITH ' || dummy;

    -- Causes PLS-00357: Table,View Or Sequence reference 'EAA_TRADING_POWERTSM_DEV.SEQ_EAA_TRANSFER_LOG' not allowed in this context
    --EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || &&PowerTsmUsername..&&SeqName || ' RESTART START WITH ' || dummy;
COMMIT;
END;

• sql
• oracle-database
• plsql
• oracle-sqldeveloper
• sqlplus

Share Follow asked Feb 12 at 12:46 gwinnemgwinnem 7644 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Thanks for updating the question with the failing code and error message.

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || &&PowerTsmUsername..&&SeqName || ' RESTART START WITH ' || dummy;

This is treating SQL*Plus substitution variables as if they were PL/SQL variables that need to be spliced in with string concatenation. But actually they are more like pre-compiler substitutions before PL/SQL is even invoked. Thus, you should be able to simply remove the single quotes:

EXECUTE IMMEDIATE 'ALTER SEQUENCE &&PowerTsmUsername..&&SeqName RESTART START WITH ' || dummy;

However, I suggest a different approach. Using PL/SQL and EXECUTE IMMEDIATE, you can string manipulate any part of your DDL, including the table name, without using SQL*Plus substitution variables, which are clunky and hard to work with. Thus you need only write the statement once and use it in a loop to work on all 200 tables. I suggest not using SQL*Plus as a row-by-row controller taking values from the outside (SQL*Plus is a cumbersome tool for any kind of programmatic control and a non-scalable one for data input), but simply to create a session in which to invoke a PL/SQL block that does all the programmatic control: opens a cursor of tables from a control table and loops through it, performing the EXECUTE IMMEDIATE on each one in the loop, both to get the maximum and to reset the sequence.

E.g.

DECLARE
  dummy number;
BEGIN
  FOR rec_table IN (SELECT * FROM controltable)
  LOOP
    EXECUTE IMMEDIATE 'SELECT NVL(MAX("'||rec_table.sequence_column_name||'"),0)+1 from "'||rec_table.table_owner||'"."'||rec_table.table_name||'"' INTO dummy;

    EXECUTE IMMEDIATE 'ALTER SEQUENCE "'||rec_table.sequence_owner||'"."'||rec_table.sequence_name||'" RESTART START WITH ' || dummy;
  END LOOP;
END;

Obviously you have to populate controltable with your metadata, which will include not only the list of tables but also the mapping of sequences and columns they are used to populate. But as you can see, it is quite possible to do this in a clean and maintainable fashion.

本文标签：