PLSQL to search a database for a value

Question

I've recently shifted from knowing MSSQL very well to using PLSQL. One of the really useful sets of scripts I have in MSSQL is for understanding the structures of databases where there is no documentation or advice available (only too often, sadly). One of these tools is a script to search a database for a specific value (e.g. an ID). I'm trying to replicate that script for PLSQL. The script I'm trying to develop here is to search for an integer value.

The approach I'm taking is to create a temp table of schema/table/columns of numeric columns and then to create dynamic SQL to count how many rows there are in each instance that contain the value that I'm searching for. Finally I select the rows where the number of matches are greater than zero. I'm having difficulty with declaring a temp table within a script. I'm using SQL Developer. The script I have so far is below.

DECLARE cSQL VARCHAR2(1000);
        iSearchValue INT;

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_COL_MATCHES
(
    SCHEMA_NAME VARCHAR2(100),
    TABLE_NAME VARCHAR2(100),
    COLUMN_NAME VARCHAR2(100),
    DATA_TYPE VARCHAR2(100),
    MATCH_COUNT INT
)
ON COMMIT PRESERVE DEFINITION;

BEGIN
    iSearchValue := 237001;
    
    
    INSERT INTO ORA$PTT_COL_MATCHES
            (SCHEMA_NAME,
            TABLE_NAME,
            COLUMN_NAME,
            DATA_TYPE,
            MATCH_COUNT)
    SELECT  col.owner as schema_name,
            col.table_name, 
            col.column_name,
            col.data_type,
            0
    FROM    sys.all_tab_columns col
            INNER JOIN sys.all_tables t     ON      col.owner = t.owner 
                                            AND     col.table_name = t.table_name
    WHERE   col.owner NOT IN ('SYS', 'SYSTEM');
    --AND     col.DATA_TYPE IN ('INT', 'NUMBER', 'FLOAT', 'LONG')
            
            
    FOR rec IN
        (SELECT  *
        FROM    ORA$PTT_COL_MATCHES)
    LOOP
        cSQL = 'UPDATE  ' || ORA$PTT_COL_MATCHES || '
                SET     MATCH_COUNT = (SELECT COUNT(*) FROM ' || rec.SCHEMA_NAME || '.' || rec.TABLE_NAME || ' WHERE ' || rec.COLUMN_NAME || ' = ' || TO_CHAR(iSearchValue) || ')
                WHERE   SCHEMA_NAME = ''' || rec.SCHEMA_NAME || '''
                AND     TABLE_NAME = ''' || rec.TABLE_NAME || '''';
        EXECUTE IMMEDIATE cSQL;
    END LOOP;
    
    
    SELECT  *
    FROM    ORA$PTT_COL_MATCHES
    WHERE   MATCH_COUNT > 0;
    
    
END;

The error I'm receiving is:

Error report -
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

In SQL Developer I'm manually selecting the entire script to execute. I understand that the above error indicates that I cannot using CREATE at that point in the script. My questions are:

1. Why can't I create a temp table there, and
2. Where can I create it as inside the BEGIN-END block doesn't work either?
3. Bonus question: Is there a better way to do this in PLSQL?

Answer 1

CREATE TABLE is a DDL statement and you cannot use DDL statements in PL/SQL. You either need to do it before the PL/SQL block or using EXECUTE IMMEDIATE (which will create an SQL scope within the PL/SQL block). Similarly, you cannot use SELECT statement on its own in a PL/SQL block; you either want SELECT ... [BULK COLLECT] INTO ... or to use a cursor with the SELECT.

You can (and should) also use bind variables wherever possible in dynamic SQL statements to minimise the risk of SQL injection attacks.

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_COL_MATCHES
(
    SCHEMA_NAME VARCHAR2(100),
    TABLE_NAME VARCHAR2(100),
    COLUMN_NAME VARCHAR2(100),
    DATA_TYPE VARCHAR2(100),
    MATCH_COUNT INT
)
ON COMMIT PRESERVE DEFINITION;

DECLARE
  cSQL         VARCHAR2(1000);
  iSearchValue INT;
BEGIN
  iSearchValue := 237001;

  INSERT INTO ORA$PTT_COL_MATCHES(
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    MATCH_COUNT
  )
  SELECT  col.owner as schema_name,
          col.table_name, 
          col.column_name,
          col.data_type,
          0
  FROM    all_tab_columns col
  WHERE   col.owner IN (USER) -- NOT IN ('SYS', 'SYSTEM')
  AND     col.DATA_TYPE IN ('INT', 'NUMBER', 'FLOAT');

  FOR rec IN (
    SELECT  *
    FROM    ORA$PTT_COL_MATCHES
  )
  LOOP
      cSQL := 'UPDATE ORA$PTT_COL_MATCHES
               SET    MATCH_COUNT = (SELECT COUNT(*)
                                     FROM   "' || rec.SCHEMA_NAME || '"."' || rec.TABLE_NAME || '"
                                     WHERE  "' || rec.COLUMN_NAME || '" = :search_value
                                    )
               WHERE  SCHEMA_NAME = :schema_name
               AND    TABLE_NAME  = :table_name
               AND    COLUMN_NAME = :column_name';
      EXECUTE IMMEDIATE cSQL
        USING TO_CHAR(iSearchValue), rec.SCHEMA_NAME, rec.TABLE_NAME, rec.COLUMN_NAME;
  END LOOP;
END;
/

SELECT  *
FROM    ORA$PTT_COL_MATCHES
WHERE   MATCH_COUNT > 0;

fiddle