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)?

On Jul 23, 9:52*am, Francogrex <fra... (AT) grex (DOT) org> wrote:
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/B....htm#sthref771

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

On 23.07.2010 19:42, Ed Prochak wrote:
I think OP did not talk abut Oracle Temp tables but rather accidentally
reused the term. It seems he rather wants some form of external index
to search for. Materialized views come to mind. Alternatively, dump
the whole table to a text file and search there. Feasibility of course
depends on the size of the data set.

If the DBA is willing he could create a materialized view inside the
original DB which then could be queried. That's probably the most
efficient and robust solution.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Jul 23, 1:42*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> 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.

HTH -- Mark D Powell --

In article <8avkflFa88U2 (AT) mid (DOT) individual.net>, shortcutter (AT) googlemail (DOT) com says
....
Thanks for both. Indeed the concept of temporary table I used was coincidental
with the Temp tables. In any case both solutions seem to need the approval and
especially a certain work by the admin. Approval is not the big issue, the big
issue is that the admin is the laziest person on earth who wouldn't move a
finger for anyone. That leaves me with the option of dumping the data into a
text file, well here actually size will probably be an issue. I would have
preferred to keep the data in the original database and query there.

Mark D Powell wrote:

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.

franco:

Complaining about the admin on an internet news group may not be the
most optimal way to approach getting help.

Outlining a couple of alternatives and then selling them to management
might be a better approach.

On 07/24/2010 08:06 PM, francogrex wrote:
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.

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/

On Jul 23, 2:52*pm, Francogrex <fra... (AT) grex (DOT) org> wrote:
Have you tried this approach (dbms_xmlgen.getxml) to let you search a
long column

From: http://jes.blogs.shellprompt.net/201...-and-order-by/

"We can get round this in a number of ways (such as creating a copy of
the DBA_VIEWS or USER_VIEWS in a local table converting the LONG
column to a CLOB etc), or we can use a different approach like this -
JES@dbtest> select
2. *
3. from
4. user_views
5. where
6. dbms_xmlgen.getxml('select text from user_views where view_name
= ''' || view_name || '''') like '%ORDER BY%'

(note unfortunately I can’t remember who to credit with the
DBMS_XMLGEN trick above, I saw it a long time ago and it’s been lost
in the mists of time)."

On Jul 24, 2:06*pm, francogrex <fra... (AT) grex (DOT) org> wrote:
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 --