temporary tables
in [Oracle]
|
Prev: Incompetent Oracle Support
Next: Oracle on Android
From: francogrex on 24 Jul 2010 14:06 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.
From: John Hurley on 24 Jul 2010 16:28 franco: > 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. 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.
From: Robert Klemme on 25 Jul 2010 06:26 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/
From: Rob Burton on 26 Jul 2010 04:50 On Jul 23, 2:52 pm, Francogrex <fra...(a)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)? Have you tried this approach (dbms_xmlgen.getxml) to let you search a long column From: http://jes.blogs.shellprompt.net/2010/02/25/views-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(a)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 cant remember who to credit with the DBMS_XMLGEN trick above, I saw it a long time ago and its been lost in the mists of time)."
From: Mark D Powell on 26 Jul 2010 09:02
On Jul 24, 2:06 pm, francogrex <fra...(a)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 -- |