Re: temporary tables
Date: Mon, 26 Jul 2010 06:02:52 -0700 (PDT)
Message-ID: <527ea88b-d524-4f1e-b1a1-bcaf579e67a6_at_r27g2000yqb.googlegroups.com>
On Jul 24, 2:06 pm, francogrex <fra..._at_grex.org> wrote:
> Mark D Powell wrote:
> >Another option if the maximum length of all
>
> the long column values is>32K or less then you can use pl/sql to select
>
> the long into a pl/sql>varchar2 variable wihich can hold up to 32K
>
> of data and then use the>pl/.sql string functions like instr to search
>
> through the long data.
>
> It's a good idea, but wouldn't that require a
> write-access to the database (the "SELECT
> INTO" statement)? I don't have that.
> Eventually I could SELECT INTO a table in an
> external database. But I am using ODBC to
> connect to the ORACLE server and I don't have
> any local ORACLE database I can select into.
Robert has already responded that my suggestion requires only the necessary privileges to connect to the database and submit anonymous pl/sql code. If you have select privilege on the target table then you should be good to go. However, my suggestion is best suited for a one-time or very infrequent activity.
If you are writing an application where the user will need to search for contents in the long columns repeatedly and where performance is an issue then you probably need a better solution such as prescanning the long column on input and indexing certain values from within the contents and/or saving a indicator value to be used in finding rows of interest may be necessary.
Conversion of the long data type to CLOB should also be considered. Then you could search the columns online using standard SQL string fuctions or perhaps use the Oracle Text product to index the columns.
HTH -- Mark D Powell -- Received on Mon Jul 26 2010 - 08:02:52 CDT