SQL & PL/SQL

Hello,

I have a relatively simple PL/SQL program and I'm pretty desparate finding out what the error is. To me it is really strange.

Here's the program:

DECLARE

-- for Oracle version
strFullVersion v$version.banner%TYPE;
majorVersion INTEGER;
minorVersion INTEGER;

-- each keyword
strKeyword VARCHAR(30);
isReserved BOOLEAN;

-- define cursor for all keywords in the view (starting with an alphanumeric character)
CURSOR curViewKeywords IS
SELECT keyword
FROM v$reserved_words
WHERE SUBSTR(keyword, 0, 1) >= 'A' AND SUBSTR(keyword, 0, 1) <= 'B'
ORDER BY keyword;

/*
* Drops a table. Doesn't produce an error if the table doesn't exist.
*/
PROCEDURE dropTable(strTableName VARCHAR) IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || strTableName;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Table ' || strTableName || ' not dropped (doesn''t exist)!');
END dropTable;

/*
* Tests whether a keyword is reserved or non-reserved.
*/
FUNCTION isReservedKeyword(strKeyword VARCHAR) RETURN BOOLEAN IS
BEGIN
--DDL statements not allowed in procedures, must use dynamnic SQL: 'EXECUTE IMMEDIATE ...'
EXECUTE IMMEDIATE 'CREATE TABLE ' || strKeyword || ' (x INTEGER)';
dropTable(strKeyword);
--if no exception occurred, table creation succeeded
RETURN FALSE; --FALSE means non-reserved
EXCEPTION
WHEN OTHERS THEN
--if exception occurred, table creation failed
RETURN TRUE; --TRUE means reserved
END isReservedKeyword;


BEGIN

-- unlimited output
dbms_output.enable(NULL);

-- assign version to variable
SELECT SUBSTR(banner, INSTR(banner, '.') - 2, 4)
INTO strFullVersion
FROM v$version
WHERE banner LIKE 'Oracle%';

dbms_output.put_line('Oracle version is ' || strFullVersion || '!');

majorVersion := SUBSTR(strFullVersion, INSTR(strFullVersion, '.') - 2, 2);
minorVersion := SUBSTR(strFullVersion, INSTR(strFullVersion, '.') + 1, 1);

-- create result tables for reserved and non-reserved keywords
dropTable('ReservedKeywords');
dropTable('NonReservedKeywords');
EXECUTE IMMEDIATE 'CREATE TABLE ReservedKeywords (keyword VARCHAR(30) PRIMARY KEY)';
EXECUTE IMMEDIATE 'CREATE TABLE NonReservedKeywords (keyword VARCHAR(30) PRIMARY KEY)';

--SELECT * FROM ReservedKeywords;

-- iterate over all keywords from the view
FOR tuple IN curViewKeywords LOOP

strKeyword := tuple.keyword;
isReserved := isReservedKeyword(strKeyword);

--dbms_output.put_line(' ' || strKeyword || ' is reserved = ' || isReserved);

IF isReserved THEN
dbms_output.put_line(strKeyword || ' is reserved');
INSERT INTO ReservedKeywords (keyword) VALUES (strKeyword); -- fails
ELSE
dbms_output.put_line(strKeyword || ' is non-reserved');
INSERT INTO NonReservedKeywords (keyword) VALUES (strKeyword); -- fails
END IF;
END LOOP;

dbms_output.put_line('-----------------------------------------------------------------------------------------');

dropTable('ReservedKeywords');
dropTable('NonReservedKeywords');

END;
/

I can't really see as to why the table doesn't exist. I'm logged in as system and I do have full rights on my local install.

I'd be glad if anybody could copy this script and run it. Should work for any Oracle version.

TIA
Karsten

Edited by: Karsten Wutzke on Feb 19, 2009 4:26 PM