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:
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;