Home » SQL & PL/SQL » SQL & PL/SQL » Getting LONG columns out of Oracle 8i and into Access (eek!)
icon5.gif  Getting LONG columns out of Oracle 8i and into Access (eek!) [message #187642] Mon, 14 August 2006 15:36 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Hi all,

I'm trying to export some data out of 8i, which will then be loaded into (gasp) a MS Access database. I'm primarily doing this just through SQL Plus, and not with a PL/SQL procedure or anything like that.

For the most part, this is going fine. However, there is one table in the database that has two columns:

ID, which is an integer
TEXT, which is LONG

I'm having very little luck getting the LONG data out of Oracle and into Access. Although the column in the database is LONG, the application on top of the DB limits values in the LONG column to <4000 characters. So, the values in the LONG column should fit into an Access "memo" field.

I've tried exporting this data in XML and text formats, and connecting directly through ODBC, and all have run into various kinds of failure.

So, questions:

1. Does anyone have SQL handy that could count the # of characters in each LONG field in the table?

2. Does anyone have any suggestions for how to approach this?

Please bear in mind that I'm not terribly skilled with SQL; in fact, I wavered on whether to post this here or in "beginners," but I figured this was a slightly more advanced topic...

Thanks!

Steve
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #187687 is a reply to message #187642] Mon, 14 August 2006 21:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check this out.

Modify this function to return a VARCHAR2 instead of a CLOB, and then create a view:

CREATE VIEW my_tab_v AS
SELECT num, get_long_value(rowid)
FROM my_tab

Now your other techniques should work OK.

Ross Leishman
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #187754 is a reply to message #187687] Tue, 15 August 2006 06:58 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
rleishman wrote on Mon, 14 August 2006 21:59

Check this out.

Modify this function to return a VARCHAR2 instead of a CLOB, and then create a view:

CREATE VIEW my_tab_v AS
SELECT num, get_long_value(rowid)
FROM my_tab

Now your other techniques should work OK.

Ross Leishman


Thanks, this is great information. I'll try to play around with this and see what I can come up with!

Steve
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #187755 is a reply to message #187754] Tue, 15 August 2006 07:03 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Which reminds me, any thoughts on how to count the # of characters in a LONG column?
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #187757 is a reply to message #187755] Tue, 15 August 2006 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Convert the long to a CLOB with Ross' code, or something like Long_to_Clob and use LENGTH or DBMS_LOB.LENGTH
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #188265 is a reply to message #187757] Thu, 17 August 2006 12:45 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Hrm, jrowbottom, I'm trying your code from the other page. I tried this code, and ended up with an error:

CREATE OR REPLACE function long_to_clob(p_id rowid) return clob is
    l_long long;
  begin
    SELECT contents
    INTO   l_long
    FROM   ascii_docs
    WHERE  rowid = p_id;
    return l_long;
  exception
    when no_data_found then return null;
    when too_many_rows then return null;
 end;


Specifically, I get "PLS-00382: expression is of wrong type" on the "return l_long" row.

In this case, "ascii_docs" is the table that has the long column, and "contents" is the name of the long column.

I apologize, this stuff is way over my head. I'm neither a PL/SQL programmer nor a DBA! If I recall, the rowid thing is an internal Oracle thing, so it should work regardless, right?

Thanks,

Steve
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #188266 is a reply to message #187642] Thu, 17 August 2006 12:46 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
And, Ross, I wasn't ignoring your code, it's just a little too complicated for me. Sad
Re: Getting LONG columns out of Oracle 8i and into Access (eek!) [message #188806 is a reply to message #187642] Mon, 21 August 2006 14:05 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Check out the following link.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:510622111991
Previous Topic: SQL capture the output statement
Next Topic: conditional where clause question
Goto Forum:
  


Current Time: Sun Dec 04 14:33:23 CST 2016

Total time taken to generate the page: 0.16761 seconds