ORA-00928(Missing Select Keyword)
551038Dec 11 2006 — edited Dec 11 2006Hi All. This procedure searches for a given keyword in all the tables of a schema.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Results CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE GLOBAL TEMPORARY TABLE Results
(
ColumnName nvarchar2 (370) ,
ColumnValue nvarchar2 (3630)
)
ON COMMIT PRESERVE ROWS
/
CREATE OR REPLACE PROCEDURE SearchAllTables
(
SearchStr IN NVARCHAR2 DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1
)
AS
TableName NVARCHAR2(256);
ColumnName NVARCHAR2(128);
SearchStr2 NVARCHAR2(110);
ASSIGNMENTVARIABLE0 NUMBER := 0;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE Results';
TableName := '';
SearchStr2 := '%' || SearchStr || '%';
<< LABEL1 >>
WHILE TableName IS NOT NULL
LOOP
BEGIN
ColumnName := '';
SELECT MIN(TABLE_SCHEMA || '.' || TABLE_NAME) INTO TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA || '.' || TABLE_NAME > SearchAllTables.TableName
AND OBJECTPROPERTY(ASSIGNMENTVARIABLE0, 'IsMSShipped') = 0;
<< LABEL2 >>
WHILE ( TableName IS NOT NULL ) AND ( ColumnName IS NOT NULL )
LOOP
BEGIN
SELECT MIN(COLUMN_NAME) INTO ColumnName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(SearchAllTables.TableName, 2)
AND TABLE_NAME = PARSENAME(SearchAllTables.TableName, 1)
AND DATA_TYPE IN ( 'char' , 'varchar' , 'nchar' , 'nvarchar' )
AND COLUMN_NAME > SearchAllTables.ColumnName;
IF ColumnName IS NOT NULL THEN
BEGIN
INSERT INTO Results EXEC
( 'SELECT ''' || SearchAllTables.TableName || '.' || SearchAllTables.ColumnName || ''', LEFT(' || SearchAllTables.ColumnName || ', 3630)
FROM ' || SearchAllTables.TableName || ' (NOLOCK) ' || ' WHERE ' || SearchAllTables.ColumnName || ' LIKE ' || SearchAllTables.SearchStr2 );
END;
END IF;
END;
END LOOP;
END;
END LOOP;
OPEN RCT1 FOR
SELECT
ColumnName,
ColumnValue
FROM Results;
END;
/