2

Questions have been asked in the past that seems to handle pieces of my full question, but I'm not finding a totally good answer.

Here is the situation:

  • I'm importing data from an old, but operational and production, Oracle server.
  • One of the columns is created as LONG RAW.
  • I will not be able to convert the table to a BLOB.
  • I would like to use a global temporary table to pull out data each time I call to the server.

This feels like a good answer, from here: How to create a temporary table in Oracle

CREATE GLOBAL TEMPORARY TABLE newtable ON COMMIT PRESERVE ROWS AS SELECT MYID, TO_LOB("LONGRAWDATA") "BLOBDATA" FROM oldtable WHERE .....;

I do not want the table hanging around, and I'd only do a chunk of rows at a time, to pull out the old table in pieces, each time killing the table. Is it acceptable behavior to do the CREATE, then do the SELECT, then DROP?

Thanks..

--- EDIT ---

Just as a follow up, I decided to take an even different approach to this.

Branching the strong-oracle package, I was able to do what I originally hoped to do, which was to pull the data directly from the table without doing a conversion.

Here is the issue I've posted. If I am allowed to publish my code to a branch, I'll post a follow up here for completeness.

Oracle ODBC Driver Release 11.2.0.1.0 says that Prefetch for LONG RAW data types is supported, which is true.

One caveat is that LONG RAW can technically be up to 2GB in size. I had to set a hard max size of 10MB in the code, which is adequate for my use, so far at least. This probably could be a variable sent in to the connection.

This fix is a bit off original topic now however, but it might be useful to someone else.

Community
  • 1
  • 1
Isaac Kehle
  • 101
  • 1
  • 9
  • For reference: [Oracle ODBC Driver Release 11.2.0.1.0](https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#BABBFBBG) – Isaac Kehle Oct 28 '15 at 18:19

1 Answers1

1

With Oracle GTTs, it is not be necessary to drop and create each time, and you don't need to worry about data "hanging around." In fact, it's inadvisable to drop and re-create. The structure itself persists, but the data in it does not. The data only persists within each session. You can test this by opening up two separate clients, loading data with one, and you will notice it's not there in the second client.

In effect, each time you open a session, it's like you are reading a completely different table, which was just truncated.

If you want to empty the table within your stored procedure, you can always truncate it. Within a stored proc, you will need to execute immediate if you do this.

This is really handy, but it also can make debugging a bear if you are implementing GTTs through code.

Out of curiosity, why a chunk at a time and not the entire dataset? What kind of data volumes are you talking about?

-- EDIT --

Per our comments conversation, this is very raw and untested, but I hope it will give you an idea what I mean:

CREATE OR REPLACE PROCEDURE LOAD_DATA()
AS
  TOTAL_ROWS number;
  TABLE_ROWS number := 1;
  ROWS_AT_A_TIME number := 100;
BEGIN

  select count (*)
  into TOTAL_ROWS
  from oldtable;

  WHILE TABLE_ROWS <= TOTAL_ROWS
  LOOP
    execute immediate 'truncate table MY_TEMP_TABLE';  

    insert into MY_TEMP_TABLE
    SELECT 
      MYID, TO_LOB(LONGRAWDATA) as BLOBDATA
    FROM oldtable
    WHERE ROWNUM between TABLE_ROWS and TABLE_ROWS + ROWS_AT_A_TIME;

    insert into MY_REMOTE_TABLE@MY_REMOTE_SERVER
    select * from MY_TEMP_TABLE;

    commit;

    TABLE_ROWS := TABLE_ROWS + ROWS_AT_A_TIME;
  END LOOP;

  commit;

end LOAD_DATA;
Hambone
  • 15,600
  • 8
  • 46
  • 69
  • The dataset is a bunch of stored images basically. There are approximately 100k records. I want to do a chunk at a time because I want to avoid using multiple methods for import, and to avoid having to store to a server and read from a network share. I want to chunk at a time because for import purposes I want to do a bit at a time to spread out server load over time (historical) and grab only new images on a periodic update interval. – Isaac Kehle Oct 28 '15 at 00:58
  • (Apparently you can't hit enter on the iPhone Stack Overflow App?) Actually, it's 500k records. – Isaac Kehle Oct 28 '15 at 01:07
  • Sorry for the multiple comments. If the table is empty but structurally there, how do you move data into it once you do the next call? I am currently running simple SELECT statements to fetch the data needed. When you use a proc, does it live on the server as well? Thanks for the help. (yes, a bit of an oracle newbie here). – Isaac Kehle Oct 28 '15 at 01:17
  • 500k records with LOBs... okay, maybe I see your point there. You lost me with your last question on how to move the data once it's there. Any chance you can post the code from your stored procedure? I assumed you were using a DB link to get the data from your old server to the new one and then copying the data from the GTT to the real table via `TO_LOB`. Is this not the case? – Hambone Oct 28 '15 at 01:29
  • My general methodology is on the node server that is importing the data to bring in a chunk at a time, like ~100 records. So I would normally do a (pseudocode) 'SELECT col1,col2 from TABLE where row > 0 and row <= 100'. So in this case, if I'm using a GTT, then the approach would be to first create the temp table, fetch, then delete the table. ie the table then has only 100 records in it each time (max). – Isaac Kehle Oct 28 '15 at 01:36
  • I create the table by a 'CREATE GLOBAL TEMPORARY TABLE newtable AS SELECT WHERE row > 0 AND row <= 100', then that implies the data is copied into the table on the CREATE. Or, is it such that I should still do that CREATE statement each time, and it's no big deal? ie: SELECT * FROM (CREATE GTT....) Does that make sense? – Isaac Kehle Oct 28 '15 at 01:36
  • I really wish I could just pull the data out as LONG RAW, since that is how it is natively stored. But, since it is a depreciated type, the node packages written for oracle do not support those types. Grr.. – Isaac Kehle Oct 28 '15 at 01:40
  • I'm beginning to understand. I think you could still use this methodology, but instead of `create table ... as` you would instead do `truncate table newtable; insert into newtable select col1, col2 from table where row...` If this gets more involved than this, maybe update your question with "pacifics," like what your code looks like. Again, I'm gaining an understanding, but it would help to solidify it and give a better answer. – Hambone Oct 28 '15 at 01:51
  • Oh, and since you are new to Oracle, you can't really truncate within a stored proc, as such. You have to `execute immediate 'trucate table newtable';` – Hambone Oct 28 '15 at 01:52
  • I'd post some code if I had some -- I've not yet written any for a stored proc yet. Again for mysql, mssql and mongo, all of these have essentially been sql statements I create on the fly to fetch what I need. This is quite a different thing. If the GTT is cleared out on each session, then would it not make sense to specifically close then reopen the connection to the server, which should 'flush' out the table automatically? – Isaac Kehle Oct 28 '15 at 01:57
  • You **can** do that, but I don't think it's necessary. Especially with that many rows -- there is overhead associated with opening and closing connections, and it can add up. I'll see if I can mock up some fake code as an example, and maybe you clairfy from there. – Hambone Oct 28 '15 at 02:02
  • Thanks for the help. I guess I have just basic questions still. hah. I found this [demo](http://www.morganslibrary.org/reference/longraw2clob.html), which looks like a great place to start. I'd loop around the 'Parent Stored Procedure' section. – Isaac Kehle Oct 28 '15 at 02:12
  • See edits and let me know if that clears the mud at all, in terms of how the GTT could work in a single session. Regarding the LOB conversion... I'm going to take that dude's word for it -- the example you linked looks like a good start as far as that goes. – Hambone Oct 28 '15 at 02:16
  • Thanks for the snippet. The main difference is I do not want to do the while loop in the proc. I want to convert a chunk on the server, pull the chunk to the client (ie different node server), then process on the node server (store in mongo). This allows me to do a little bit at a time in the background, when convenient. That's why I am ok with deleting the rows on commit and when the session is over. Lastly before putting it to rest for the night, does the proc LOAD_DATA get saved on the server as my own function? I think it'd be good protocol to ensure the client is OK with us doing that. – Isaac Kehle Oct 28 '15 at 02:25
  • I gotcha... in that case, you could simply pass the row number range as parameters and omit the loop. Otherwise, it would remain essentially the same. And yes, the procedure resides on the server. Good luck! – Hambone Oct 28 '15 at 02:31
  • Thanks a bunch for the help. I've been looking at it from the standpoint of leaving the server alone as much as possible. So I'll know more after I get permission from the client. Thanks again. Time for a beer and the Talking Dead. Hah. – Isaac Kehle Oct 28 '15 at 02:35