3

I need to check if a string contains a valid Oracle table name using sql/plsql. The criteria I found for a Oracle table name are these:

  1. The table name must begin with a letter.
  2. The table name can not be longer than 30 characters.
  3. The table name must be made up of alphanumeric characters or the following special characters: $, _, and #.
  4. The table name can not be a reserved word.

Criteria 1,2,3 don't seem so hard to tackle. But what about point 4? What are my options without trying to actually create a table with the given name and then see if it succeeds or fails.

Improve this question
1
  • 3
    The rules are a little more complicated than rules 1-3, you can have nearly any string for a table as long as it is enclosed by double quotes (you can create a table with non-ASCII characters). You should use dbms_assert as suggested by Jim Hudson in place of rules 1-3 (in addition to check for reserved words). Commented Aug 21, 2009 at 14:36

4 Answers 4

Reset to default
7

Oracle has a built-in that's useful for checking whether a SQL Name is valid. That's especially useful when building dynamic queries where you need to prevent SQL Injection.

Check out the dbms_assert.simple_sql_name built-in, and see the Oracle white paper at How to Write Injection Proof PL/SQL for more details.

v$reserved_words is also useful, as others have noted.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 For suggesting to use a builtin. Note that dbms_assert is only available in Oracle11.
Hi Rob, apparently the package was backported to all versions of Oracle (down to 8.1.7.4) in a patch in 2005 (to deal with SQL injection threats). It is definetely available on a new 10gR2 instance while at the same time absent from the 10gr2 doc. Cheers.
The link is dead.
6

For SQL reserved words you can check v$reserved_words. Here the link from the documentation

Improve this answer

1 Comment

Sucks that in Oracle 9i this view contains only KEYWORD and LENGTH.
0

I have a SQL_RESERVED_WORDS table that I check against.

EDIT:

(I lied... it was just a SYNONYMN for the table in carpenteri's post)

Improve this answer

Comments

0

It is a fairly large list to check. Can you simply add a default prefix to the tables and avoid these problems all together? user_xxx

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.