Using Execute immediate along with variable

Question

I have a statement which inserts into table with difference count between original table and backup data

EXECUTE IMMEDIATE 'INSERT INTO LOG_table (TABLE_NAME,TYPE,COUNT,QUARTER) 
SELECT NAME,TYPE,COUNT,QUARTER FROM (SELECT COUNT(*) COUNT,''table_name'' NAME,''A-B'' TYPE,'''||SUPP_QUARTER||''' QUARTER FROM(
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM TABLE
MINUS
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM BACKUP_TABLE)
UNION ALL
SELECT COUNT(*),''TABLE_NAME'',''B-A'','''||SUPP_QUARTER||''' QUARTER FROM
(SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_'||TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') ||'
MINUS
SELECT COL1 ,COL2 ... TO_CHAR(TO_DATE(SOMENUMBER,''YYYYMM''),''YYYY"-Q"Q'') AS QT FROM FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G)' INTO SUPP_QUARTER using QT
;
END;

when i run this i get : PLS-00201: identifier 'QT' must be declared

So my question is how do i insert QT INTO LOG_TABLE.

If I don't do this and pick quarter from select statement itself i would get "not group by statement " which is correct.

Any way i can achieve this ?

Also table only has 60 records

If you mean the `QT` column alias you assign within your query, that name isn't visible outside the dynamic statement; you don't have any bind variables so you don't need a `using` clause, and the insert doesn't return anything so you don't need the `into` clause at the end of the `execute` either; and the insert will use the value from the third column in the query whatever it's called - you don't need an alias, and if you have one it isn't used by the insert. — Alex Poole, Feb 23 '23 at 15:33
You're also embedding `SUPP_QUARTER` in the dynamic string, which is a SQL injection risk and that *should* be a bind variable; and the names, data types and positions within the dynamic query don't seem to match, even ignoring the `...` parts. At the moment it's hard to see what else needs to be corrected. If you have to use dynamic SQL it's usually better to get a working static statement first and then modify it to be dynamic. — Alex Poole, Feb 23 '23 at 16:01
hi @AlexPoole, this does work, i have edited out lof of code(table names , column names ). So that you have easier time reading, please let me know what clarity you need. — Leverage, Feb 23 '23 at 16:04
I'm not sure what you're asking then. You want the outer select to use `QT` from the inner one instead of the embedded `SUPP_QUARTER` value? Why is adding a group-by clause a problem? — Alex Poole, Feb 23 '23 at 16:10

score 2 · Accepted Answer · answered Feb 23 '23 at 16:45

You don't need the INTO SUPP_QUARTER using QT, even if you have defined those variables - and the error suggests you haven't defined QT at least.

The statement doesn't appear to have any bind variables, so there is nothing to supply via using. But you are embedding SUPP_QUARTER in the statement, which is potentially a SQL injection risk, so that should be a bind variable if it's needed, but you seem to be trying not to. And it's an insert statement that doesn't return anything, so there is nothing to put into a variable either; as it's the same variable you're embedding it looks like that might just be confused.

The insert ... select ... syntax doesn't care what the columns are called in the select part, it will use the value that is in the matching position for each column it's inserting. (Using keywords or function names like TYPE and COUNT as column names isn't ideal and is likely to cause confusion at some point...)

When you are forced to use dynamic SQL it's often helpful get a working static version first, then convert it. At the moment

As a static statement with fixed values 'Q1' for SUPP_QUARTER and '230222' for the dynamic part of the table name, and removing ... and an extra from, it would look like:

INSERT INTO LOG_table (TABLE_NAME, TYPE, COUNT, QUARTER) 
SELECT NAME,TYPE,COUNT,QUARTER
FROM (
  SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, 'Q1' QUARTER
  FROM (
    SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
    FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
    MINUS
    SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
    FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
  )
  UNION ALL
  SELECT COUNT(*), 'TABLE_NAME', 'B-A', 'Q1' QUARTER
  FROM (
    SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
    FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
    MINUS
    SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT 
    FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
  )
)

The two levels of subquery aren't really needed, and if you want to use the QT value from the inner query then refer to that instead of SUPP_QUARTER in the select list, and add a group-by clause for it, in each branch:

INSERT INTO LOG_table (COUNT, TABLE_NAME, TYPE, QUARTER)
SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, QT QUARTER
FROM (
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
  MINUS
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
)
GROUP BY QT
UNION ALL
SELECT COUNT(*), 'TABLE_NAME', 'B-A', QT
FROM (
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_230222
  MINUS
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT 
  FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
)
GROUP BY QT

That no longer uses SUPP_QUARTER, but that seems to be what you want. You aren't using COL1 or COL2 but I've left them in - you might be in your real query, and if they're projected then you would need to group by those too.

You don't need the column aliases in the outer query, the insert doesn't need them, but you might prefer to leave them there to make it easier to run the query stand-alone.

That's can then be converted to dynamic SQL to add the variable backup table suffix back in; and that would be easier to read and maintain with the alternative quoting syntax:

execute immediate q'^INSERT INTO LOG_table (COUNT, TABLE_NAME, TYPE, QUARTER)
SELECT COUNT(*) COUNT, 'table_name' NAME, 'A-B' TYPE, QT QUARTER
FROM (
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
  MINUS
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_^' || TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') || q'^
)
GROUP BY QT
UNION ALL
SELECT COUNT(*), 'TABLE_NAME', 'B-A', QT
FROM (
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT
  FROM SCRATCH.W_SUPPL_SNAPSHOT_CONTROL_G_DLY_BKP_^' || TO_CHAR(LATEST_DATE_VALUE,'YYMMDD') || q'^
  MINUS
  SELECT COL1, COL2, TO_CHAR(TO_DATE(SOMENUMBER, 'YYYYMM'), 'YYYY"-Q"Q') AS QT 
  FROM DW.W_SUPPL_SNAPSHOT_CONTROL_G
)
GROUP BY QT^';

fiddle

Using Execute immediate along with variable

1 Answers1