'substring' a LONG column
Richelle Hutchinson
first 80 characters and the substring function won't work. Changing this
column to a LOB datatype on the database is not an option at this time.
Thanks in advance.
-Richelle Hutchinson
Frank
The LONG datatype has many constraints. Try to send the LONG column into a
PLSQL function with a parameter
VARCHAR2(max 32000 characthers I think) and use substr inside the function,
and return the 80 characthers from
the function. Such a function can be made possible to be callef from a SQL
statement.
(not tested :-)
Frank
Richelle Hutchinson <rihu...@columbus.rr.com> wrote in message
news:u37q7.55438$Iq4.29...@typhoon.columbus.rr.com...
Sybrand Bakker
"Richelle Hutchinson" <rihu...@columbus.rr.com> wrote in message
news:u37q7.55438$Iq4.29...@typhoon.columbus.rr.com...
Apart from writing a pro*c program, there are no other options.
So regrettably, you are stuck.
Could you please refrain from excessive crossposting next time.
I have deleted the irrelevant groups from my reply in order to save
bandwith.
Regards,
Sybrand Bakker, Senior Oracle DBA
Mark D Powell
to resort to using either pl/sql or a pro* language. If the total
length of the long column in under 32K you can declase a pl/sql
varchar2 variable of 32K in size and select long into v_varchar2 ...
as Frank mentioned though I think his post was a little unclear. If
the long column data exceeds 32k in size then I believe you have to
resort to using piece-wise fetching even though you only want the
first piece.
Here is sql posted in the past by Thomas Kyte that will do just that:
piecewise fetching will do it... here is an example. You send
showlong a query that selects 1 column (a long) and fetches 1 row
(it'll only fetch the first row):
create or replace procedure showlong( p_query in varchar2 )
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos
,
l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
dbms_output.put_line( l_long_val );
exit when l_long_len = 0;
end loop;
end if;
dbms_output.put_line( '====================' );
dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length'
);
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end showlong;
/
If you create it, then you can test it with:
SQL> begin
2 showlong( 'select text from all_views where rownum = 1'
);
3 end;
4 /
select OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,
PCT_FREE, PCT_USED,
INI_TRANS, MAX_TRANS,
INITIAL_EXTENT, NEXT_EXTENT,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
FREELISTS, FREELIST_GROUPS, LOGGING,
BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
DEGREE, INSTANCES, CACHE, TABLE_LOCK,
SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
IOT_TYPE, NULL, NULL, TEMPORARY, NESTED,
BUFFER_POOL
from all_tables
union all
select
"OWNER","TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE
","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","
PARTITIONED","IOT_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","NESTED","BUFFER_POOL"
from all_object_tables
====================
Long was 1114 bytes in length
PL/SQL procedure successfully completed.
Thomas Kyte
tk...@us.oracle.com
------------------
-- Mark D Powell --
Martin
above you can piece-wise traverse a LONG.
Good luck.
"Richelle Hutchinson" <rihu...@columbus.rr.com> wrote in message
news:u37q7.55438$Iq4.29...@typhoon.columbus.rr.com...
Thomas Kyte
see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:55212348054
for various methods.
--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Michael C Smith
may need to have another field in the SELECT statement after the LONG
field.
larry.o...@gmail.com
SELECT DISTINCT PAR.SETID
, PAR.EMPLID
, PAR.FISCAL_YEAR
, substr(PAR.GBGM_PAY_PERIOD,5,2) GBGB_CAL_MONTH
, CASE WHEN substr(PAR.FUND_SOURCE,1,1) <> to_char(0) THEN PAR.FUND_SOURCE ELSE NULL END AS PROJECT_ID
, to_char(Substr(PRJDESCR.DESCRLONG,1,254))
, CASE WHEN substr(PAR.FUND_SOURCE,1,1) = TO_CHAR(0) THEN SUBSTR(PAR.FUND_SOURCE,2,5) ELSE NULL END AS FUND_DEPTID
, PAR.EARNED_AMT
, PAR.EARNINGS_CATEGORY
, PAR.GBGM_ERNG_PERIOD
, PAR.ACCOUNTING_PERIOD
, PAR.GBGM_PAY_PERIOD
, PAR.GBGM_ERNG_PERIOD
, PAR.DEPTID
, PAR.ACCOUNT2
FROM PS_GBGM_CRT_PAR PAR, PS_GBFS_CRITVAL VAL, (SELECT BUSINESS_UNIT, PROJECT_ID, DESCR254, Substr(DESCRLONG,1,254) from PS_PROJECT_DESCR) DSC, PS_PROJECT_DESCR PRJDESCR
WHERE VAL.GBFS_CRITERIA_ID = 'EARNINGS_CODES'
AND PAR.EARNINGS_CATEGORY = VAL.GBFS_CRITERIA_VAL
AND VAL.GBFS_PROG_NAME = 'GBGM_TE_MRPT'
AND GBFS_CRITERIA_SVAL = 'Y'
AND PAR.FISCAL_YEAR = '2012'
AND PAR.GBGM_PAY_PERIOD = PAR.GBGM_ERNG_PERIOD
AND PAR.SETID = DSC.BUSINESS_UNIT
AND PAR.FUND_SOURCE = DSC.PROJECT_ID
AND PAR.SETID = PRJDESCR.BUSINESS_UNIT
AND PAR.FUND_SOURCE = PRJDESCR.PROJECT_ID
ORDER BY PAR.FISCAL_YEAR, PAR.ACCOUNTING_PERIOD, PAR.EMPLID;
joel garry
> On Wednesday, September 19, 2001 3:17:31 PM UTC-5, Richelle Hutchinson wrote:
jg
--
@home.com is bogus.
http://www.theregister.co.uk/2013/03/13/oracle_cloud/
Gerard H. Pille
John Hurley
# You do realize you are responding to a dozen year old thread?
... it's alive!