Home » SQL & PL/SQL » SQL & PL/SQL » query to find reserved words (10.2.0.4.0)
query to find reserved words [message #618269] Thu, 10 July 2014 03:44 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I would like to find the reserved words like SET, FROM etc in oracle query.I know we can use v$reserved_words view but I don't have privileges to this view from application user.
Please let me know how to find the reserved words from oracle query without v$reserved_words view.
Re: query to find reserved words [message #618270 is a reply to message #618269] Thu, 10 July 2014 03:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
In SQL*Plus use the help utility :

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> help reserved words;

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*
Re: query to find reserved words [message #618272 is a reply to message #618270] Thu, 10 July 2014 03:57 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thanks for your reply.
But I would like to know if there is any code snippet or procedure to identify keywords within a SQL query.
Re: query to find reserved words [message #618280 is a reply to message #618269] Thu, 10 July 2014 05:15 Go to previous message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
know we can use v$reserved_words view but I don't have privileges to this view from application user.


Ask your DBA to grant you (and to all developers) access to this view.
There is no security reason to not do this and you can argue that if you can't know the reserved words you may use them and the application may go into some problems that he, as a DBA, must have to handle later (and other ones for developers).

Previous Topic: help in sql query
Next Topic: Refcursor with returning resultset
Goto Forum:
  


Current Time: Thu Sep 04 22:47:42 CDT 2025