temporary tables
in [Oracle]
|
Prev: Incompetent Oracle Support
Next: Oracle on Android
From: Francogrex on 23 Jul 2010 09:52 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)?
From: Ed Prochak on 23 Jul 2010 13:42 On Jul 23, 9:52 am, 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)? 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.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
From: Robert Klemme on 24 Jul 2010 03:53 On 23.07.2010 19:42, Ed Prochak wrote: > On Jul 23, 9:52 am, 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)? > > I think you misunderstand Oracle Temporary tables. here's a quote from > the concepts guide 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. > 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. 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/
From: Mark D Powell on 24 Jul 2010 08:17 On Jul 23, 1:42 pm, Ed Prochak <edproc...(a)gmail.com> wrote: > On Jul 23, 9:52 am, 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)? > > 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 --
From: francogrex on 24 Jul 2010 08:19
In article <8avkflFa88U2(a)mid.individual.net>, shortcutter(a)googlemail.com says .... >On 23.07.2010 19:42, Ed Prochak wrote: >> On Jul 23, 9:52 am, 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)? >> >> I think you misunderstand Oracle Temporary tables. here's a quote from >> the concepts guide > >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. > >> 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. > >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. 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. |