| TO CHANGE KEY WORDS TO UPPER CASE [message #48493] |
Wed, 24 November 2004 00:07  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
Dear all,
i want a query or pl/sql block which replaces all oracle keywords like select, and, where, from.. etc. to upper case.
Thanks in advance.
Raj
|
|
|
|
|
|
| Re: TO CHANGE KEY WORDS TO UPPER CASE [message #48500 is a reply to message #48493] |
Wed, 24 November 2004 10:08  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
|
Note that this is not straightforward, even for tools such as TOAD or PL/SQL Developer. Not only is the view V$RESERVED_WORDS is notoriously incomplete, but the formatter should ideally be aware of context. For example, COMMENT is a keyword in COMMENT ON TABLE t IS '...', but if you had a column named COMMENT you would not want it in uppercase.
|
|
|
|
| Re: TO CHANGE KEY WORDS TO UPPER CASE [message #48505 is a reply to message #48497] |
Wed, 24 November 2004 18:50 |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
hi guys,
thanks for yor feedback,
i cant use tools like toad. its a procedure accepting a string of sql stmts. all that has to be done is to change the keywords to upper case and send it back to another procedure.
i hope am clear.
Regards
raj
|
|
|
|
| format sql statements? URGENT [message #48506 is a reply to message #48500] |
Wed, 24 November 2004 19:37 |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
hi all,
anyone worked on formatting sql statements. may be using a procedure or a function.. package...
i will be receiving an sql stmt and have to format the same and send as output.
thanks in advance
raj
|
|
|
|
| Re: TO CHANGE KEY WORDS TO UPPER CASE [message #48519 is a reply to message #48505] |
Thu, 25 November 2004 09:34 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
It's not easy without writing a parser, but you can try something like this. Make sure you test it properly though (try to break it). e.g. "select*from" is valid too, not just the usual "select * from". This code is too dumb to leave strings and comments alone...
FUNCTION std_txt (i_txt IN VARCHAR2)
RETURN VARCHAR2
IS
v_txt VARCHAR2 (1000) := i_txt;
BEGIN
-- 13=CR, 10=LF, 9=TAB
-- table
v_txt := REPLACE (v_txt, chr(10)||'table ', chr(10)||'TABLE ');
v_txt := REPLACE (v_txt, chr(10)||'Table ', chr(10)||'TABLE ');
v_txt := REPLACE (v_txt, chr(10)||'table(', chr(10)||'TABLE(');
v_txt := REPLACE (v_txt, chr(10)||'Table(', chr(10)||'TABLE(');
v_txt := REPLACE (v_txt, ' table'||chr(13), ' TABLE'||chr(13));
v_txt := REPLACE (v_txt, ' Table'||chr(13), ' TABLE'||chr(13));
v_txt := REPLACE (v_txt, ' table'||chr(10), ' TABLE'||chr(10));
v_txt := REPLACE (v_txt, ' Table'||chr(10), ' TABLE'||chr(10));
v_txt := REPLACE (v_txt, chr(9)||'table'||chr(13), chr(9)||'TABLE'||chr(13));
v_txt := REPLACE (v_txt, chr(9)||'Table'||chr(13), chr(9)||'TABLE'||chr(13));
v_txt := REPLACE (v_txt, chr(9)||'table'||chr(10), chr(9)||'TABLE'||chr(10));
v_txt := REPLACE (v_txt, chr(9)||'Table'||chr(10), chr(9)||'TABLE'||chr(10));
-- select
--
-- etc...
RETURN v_txt;
END; |
|
|
|
| Re: format sql statements? URGENT [message #48687 is a reply to message #48506] |
Wed, 08 December 2004 23:22 |
guidomarcel
Messages: 1
Registered: December 2004
|
Junior Member |
|
|
Hi,
I wrote a small Java-applet on www.sqlinform.com . It is a SQL code beautifier. Just paste your unformatted SQL Code and press Format. There are additional options available for upper case, line breaks etc. The applet does not install anything on your PC. The only thing you need is a Java Runtime Environment (which should be the case in W2000 and Windows XP) . Hope this helps.
Regards
GuidoMarcel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
