asked on

LONG to VARCHAR2 Function

I have a table that contains a LONG datatype. I need to create a view based on that table with the DISTINCT function, which is not allowed on LONG datatypes. I figured if I can put a function to convert the LONG into a VARCHAR2 on the fly, I can get the DISTINCT working. Does anybody have a function to do that? Here is an example of the table I'm working with:

CREATE TABLE employee_dups
(emp_id NUMBER,
lname VARCHAR2,
fname VARCHAR2,
job_descr LONG
);

The view I'm trying to create is

CREATE OR REPLACE VIEW emp_nodups_vu AS
SELECT distinct *
FROM employee_dups;

If I could put a function on the job_descr column to convert it to a VARCHAR2 from within the view, I believe I can get it to work, like this:

CREATE OR REPLACE VIEW emp_nodups_vu AS
SELECT distinct
emp_id,
lname,
fname,
LONG_TO_VARCHAR2(job_descr)
FROM employee_dups;

Anybody have any ideas?

DrSQL - Scott Anderson

Since you're just asking for ideas, I'd suggest that you create a different kind of column. A checksum on the long data could be used to determine when you have different values. You would need a function that would calculate a numeric value based on a scan of the entire text (CRC, progressive clumps - sum(ASCII * Position mod 20), etc.). This would either be stored in your table as a new column(especially if it takes few seconds to run), or called from a view at run-time (with the O8i restrictions on LONGs, this would be difficult to write in PL/SQL).

The calculated value would then be used to determine uniqueness (especially for counts like: How many job_descr are there in the company?).

Good luck!

asimkovsky

ASKER

Well, here's the real issue. The base tables for this are a PeopleSoft system, where I cannot touch the base tables. I'm limited to just creating a view on the tables to get the data that I need.

You had mentioned putting it in a view that can be called at runtime. Can you expand on that? I didn't really catch the idea from your description.

DrSQL - Scott Anderson

Just because you can't change the application base table, doesn't mean you can't store the information in the database. You could create your own table, with the primary key from the Peoplesoft table, and put your computed value in that table. Then you can either use a view that joins the two tables on the primary key, or just use your table (depending on the query).

But, if you wanted to use a view, then you'd create a function that gets the primary key from its parameters, fetches the text from the Peoplesoft table, computes the checksum, and returns the value. I was also suggesting that since you can't easily refer to substrings of LONGs you might have to write this code in Java, or in a user-exit. However you write the code, the structure would be:

create or replace function Calc_People_Text(keyval number) return number is
...
select longvalue into buffer
from PeopleTable where ID = keyval;
... /* pseudocode to calc a value based on "clumps" of 20 chars would be */

calc :=0;
for i = 1, len(buffer) do
calc := calc + ascii(buffer(i)) * (i mod 20 + 1);
return(calc);
end;

then, to get the # distinct values:

select count(distinct Calc_People_Text(ID)) from PeopleTable;

But, you'll need to decide what algorithm you'll use to calculate a value and what language you'll use to write it. If you use the "shadow" table as I described above, you could probably use a C++ routine (CRC [cyclical redundancy checks] and checksum examples are easy to find on the web).

Good luck!

asimkovsky

ASKER

Isn't there an easier way? What about the COLUMN_VALUE_LONG procedure in DBMS_SQL?

ASKER CERTIFIED SOLUTION

DrSQL - Scott Anderson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

asimkovsky

ASKER

I really only need the DISTINCT to resolve an issue I'm having in a view that inadvertantly returns multiple rows because of its definition. I know the rows are unique, but because of the way the view is built, there are duplicate rows in there. I also know that there are no columns with more than 4000 characters in it, so having VARCHAR2(4000) in the view is fine. I wrote a function that works pretty good. I guess I answered my own question! :-) Here it is:

CREATE OR REPLACE FUNCTION SYSADM.long_to_varchar2 (caseId NUMBER)
RETURN VARCHAR2 IS

varcharVal VARCHAR2(4000);
varcharLength NUMBER;
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fetchIt PLS_INTEGER;

BEGIN

DBMS_SQL.PARSE(cur,'SELECT rc_descrlong FROM ps_rc_case WHERE case_id = '||caseId, DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN_LONG(cur,1);

fetchIt := DBMS_SQL.EXECUTE_AND_FETCH(cur);

DBMS_SQL.column_value_long(cur,1,4000,0,varcharVal,varcharLength);

DBMS_SQL.CLOSE_CURSOR(cur);

RETURN varcharVal;
END;
/

DrSQL - Scott Anderson

Whether or not you award is up to you, but it didn't seem to me that you'd figured out how to get the long into a function (passing the key and requerying) until I suggested it. And sometimes a sounding board is just as valuable as a piece of code. In future, you might want to post more about your specific requirements, rather than post something general when that's not what you want.

Good luck.

asimkovsky

ASKER

Actually, I was already thinking of that, but I didn't know the COLUMN_VALUE_LONG procedure existed until I started digging around in the data dictionary. But, if it means that much to you, I'll give you the points.

DrSQL - Scott Anderson

I didn't mean to sound rude or petty. I do appreciate the points, but I was seeking clarity and closure as much as reward. There are many individuals who post their generic "question" in a manner that is really biased towards their pet solution rather than give the full details of their requirement. I've seen you posting trying to help some of these people, so I think you know what I mean and can appreciate the frustration this can lead to.

I'm sure we'll see each other posting on many other threads. I truly do wish you good luck.

asimkovsky

ASKER

Well, thank you. I do have to say that I've seen your other answers to many questions. You usually hit the nail on the head, and encourage others to do some of their own research, which many junior people need. I'm sure we'll cross again....