Re: SQL Question

What about creating a view and hiding 'unreadable SQL' in view definition, and granting 'select on' view instead of table.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 29, 2002 4:00 PM

> Dennis,
>
> Thanks for the ideas, but...
>
> 1. Not going to happen. It's a production system already in place (Vendor
> designed? But, of course!!)
>
> 2. See above.
>
> This is what happens when someone decides to write their own reports
against
> tables that were not designed by themselves. Damagement thinks it is just
a
> simple SELECT !!!
>
> Looks like they just will have to live with unreadable SQL !
>
> Thanks.
> - Kirti
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 2:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Kirti - We have a denormalized table like this in one database. An
excellent
> moral lesson for those who doubt the wisdom of normalization.
> My first choice would be to lobby to redesign this table. The longer
> it remains and the more programs are built around this design, the more
> painful the eventual redesign will be.
> My second choice would be something along the lines of a shadow
> table to move all the records periodically for reporting. If you need to
> search on these fields, you could create a function-based index on each
> column. One idea that occurs to me that I haven't tested would be whether
> you could write a stored procedure that would parse the field and return
the
> columns. This would be usable by any utility that could execute a PL/SQL
> procedure.
> Kirti - you have my sympathy.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Thanks.
> Substr/instr was rejected because it was a bit difficult to read the code.
>
> Also, they wanted to extract the fields in their own column headings (new
> requirement). So 'replace' may not fly much !!
>
> - Kirti
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What about
>
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
>
> which would work if going to a tab separated file for something like
excel.
>
>
> Whats wrong with substr/instr?
>
> Iain Nicoll
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I need some help...
>
> The database table has following structure.
> commision_id number
> com_text_msg varchar2(500)
>
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in the
> column.
>
> Is there a way in SQL, other than substr/instr combinations, to extract
each
> data field to report?
>
> Thanks.
>
> - Kirti
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Nicoll, Iain (Calanais)
> INET: iain.nicoll_at_calanais.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).