Re: temporary tables
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 25 Jul 2010 12:26:27 +0200
Message-ID: <8b2hqjF9fpU1_at_mid.individual.net>
On 07/24/2010 08:06 PM, francogrex 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.
Date: Sun, 25 Jul 2010 12:26:27 +0200
Message-ID: <8b2hqjF9fpU1_at_mid.individual.net>
On 07/24/2010 08:06 PM, francogrex 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.
SELECT INTO writes into a PL/SQL variable. You do not even need to create a procedure of function in the DB for this. A block of PL/SQL submitted through sqlplus is sufficient.
> 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.
Here's another option: create your own instance on a different system and copy the data via Data Pump or a database link. Then you can create indexes etc. in your own copy of the DB. Update efficiency is still an issue. But if this is a one off operation then this approach might be feasible.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Sun Jul 25 2010 - 05:26:27 CDT