|
|
| Re: sql query for converting a long data type value into a integer data value [message #373601 is a reply to message #373600] |
Wed, 25 April 2001 20:07  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
you could try something like this...
CREATE TABLE LONGTAB (PK NUMBER,LONGCOL LONG);
insert into longtab values(1, '123');
insert into longtab values(2, 'one long string up to 32k - thats all that pl/sql long can take');
insert into longtab values(3, '456');
insert into longtab values(4, '999 another long one');
CREATE OR REPLACE FUNCTION long_2_num (v_recid IN NUMBER)
RETURN NUMBER
IS
CURSOR datacursor (v_pk longtab.pk%TYPE) IS SELECT longcol FROM longtab WHERE pk = v_pk;
v_long LONG;
v_retval NUMBER;
BEGIN
OPEN datacursor (v_recid);
FETCH datacursor INTO v_long;
v_retval := to_num (v_long);
--DBMS_OUTPUT.PUT_LINE ( 'Converted Long = '||v_retval );
CLOSE datacursor;
RETURN v_retval;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;-- Whoops, cant convert!
END long_2_num;
/
-- Test it...
select pk, long_2_num(pk) from longtab;
You can probably eliminate the cursor using native dynamic sql.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
