Re: temporary tables
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 24 Jul 2010 05:17:16 -0700 (PDT)
Message-ID: <7f71aeda-3e45-4583-8686-5c5a722fac6a_at_k19g2000yqc.googlegroups.com>
On Jul 23, 1:42 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Jul 23, 9:52 am, Francogrex <fra..._at_grex.org> wrote:
>
> > I have access to a database as read-only (so, I cannot create tables,
> > insert or update values etc)... But I need to search a col with type
> > LONG and since this is not a possibility to search it in a when
> > clause, they suggested I try to convert it to CLOB first. Is there a
> > way that I create a "temporary table" (ex would reside only in PC
> > memory or a local DB on my PC) that is not in the read-only database
> > and do the work on that table (which will not commit anything and will
> > disappear upon closing the session)?
>
> I think you misunderstand Oracle Temporary tables. here's a quote from
> the concepts guide
>
> "In addition to permanent tables, Oracle can create temporary tables
> to hold session-private data that exists only for the duration of a
> transaction or session."
>
> from:http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema...
>
> So talk to your DBA about creating a temporary table for you. You may
> need some other things as well to search the CLOB. I suggest you talk
> to your DBA for help.
>
> Ed
Date: Sat, 24 Jul 2010 05:17:16 -0700 (PDT)
Message-ID: <7f71aeda-3e45-4583-8686-5c5a722fac6a_at_k19g2000yqc.googlegroups.com>
On Jul 23, 1:42 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Jul 23, 9:52 am, Francogrex <fra..._at_grex.org> wrote:
>
> > I have access to a database as read-only (so, I cannot create tables,
> > insert or update values etc)... But I need to search a col with type
> > LONG and since this is not a possibility to search it in a when
> > clause, they suggested I try to convert it to CLOB first. Is there a
> > way that I create a "temporary table" (ex would reside only in PC
> > memory or a local DB on my PC) that is not in the read-only database
> > and do the work on that table (which will not commit anything and will
> > disappear upon closing the session)?
>
> I think you misunderstand Oracle Temporary tables. here's a quote from
> the concepts guide
>
> "In addition to permanent tables, Oracle can create temporary tables
> to hold session-private data that exists only for the duration of a
> transaction or session."
>
> from:http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema...
>
> So talk to your DBA about creating a temporary table for you. You may
> need some other things as well to search the CLOB. I suggest you talk
> to your DBA for help.
>
> Ed
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.
HTH -- Mark D Powell -- Received on Sat Jul 24 2010 - 07:17:16 CDT