RE: Size of a Long Field
Try this, Select table_name,column_name,data_length,data_type from dba_tab_columns where owner = '' and data_type='LONG' ; -- Babu -Original Message-From: Ed Bittel [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 6:53 AMTo: Multiple recipients of list ORACLE-LSubject: Size of a Long Field How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed
RE: Size of a Long Field
Title: Message Man, what a buzz-kill you are. -Original Message-From: Ed Bittel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 6:53 AMTo: Multiple recipients of list ORACLE-LSubject: Size of a Long Field How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed
RE: Size of a Long Field
Ed - Look up the function VSIZE in the documentation. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 8:53 AM To: Multiple recipients of list ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of a Long Field
Ed, Perhaps you could use the DBMS_SQL Define_Column_Long and Column_Value_Long procedures to manipulate the Long? Converting the Long to a LOB using To_LOB function, then manipulating it with the DBMS_LOB package might do, too. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] "Ed Bittel" <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Size of a Long Field 02/05/2003 08:53 AM Please respond to ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of a Long Field
Dennis, Using VSize on a Long results in: ORA-00997: illegal use of LONG datatype Just tried in on Trigger_Body in DBA_Triggers. (8.1.7.0.0 on HP-UX). Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] DENNIS WILLIAMS <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: RE: Size of a Long Field 02/05/2003 01:29 PM Please respond to ORACLE-L Ed - Look up the function VSIZE in the documentation. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 8:53 AM To: Multiple recipients of list ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of a Long Field
Not easy. If your LONG is shorter than 32767, then you can, in a PL/SQL function, fetch it into a VARCHAR2 of this size (allowed in PL/SQL) and aplly LENGTH() to this VARCHAR2. Assuming the suitable Oracle version, I think there is somewhere a function to convert LONGs to CLOBs - to which you can apply a function. With Pro*C or the OCIs, you can derived the length from the error you get when fetching into too small a buffer ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of a Long Field
Title: RE: Size of a Long Field I have this code that is used to search within a trigger body ... you can maybe adapt this to get length ... you have to just change dbms_lob.instr to dbms_lob.getlength() maybe. CREATE OR REPLACE FUNCTION long_contains ( row_id ROWID, VALUE VARCHAR2) RETURN NUMBER IS -- ret_val NUMBER; -- /* Table details */ OWN VARCHAR2(30); tab VARCHAR2(30); col VARCHAR2(30); /* DBMS_SQL variables */ cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; res PLS_INTEGER := 0; pos PLS_INTEGER := 0; len PLS_INTEGER := 0; pce VARCHAR2(32767); /* Temp CLOB to hold long data */ tmp CLOB; -- BEGIN /* Fetch the table name and long column name */ SELECT u.NAME ,o.NAME ,c.NAME INTO OWN ,tab ,col FROM sys.obj$ o ,sys.col$ c ,sys.USER$ u WHERE u.USER# = o.owner# AND c.TYPE# = 8 AND c.obj# = o.obj# AND o.obj# = DBMS_ROWID.ROWID_OBJECT(row_id); -- /* Fetch the long column into a temporary CLOB */ DBMS_LOB.CREATETEMPORARY(tmp,TRUE,DBMS_LOB.CALL); DBMS_SQL.PARSE(cur, ' SELECT '||col|| ' FROM '||OWN||'.'||tab|| ' WHERE ROWID = :row_id',DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(cur,':row_id',row_id); DBMS_SQL.DEFINE_COLUMN_LONG(cur, 1); res := DBMS_SQL.EXECUTE_AND_FETCH(cur); LOOP DBMS_SQL.COLUMN_VALUE_LONG(cur,1,32767,pos,pce,len); EXIT WHEN len = 0; pce := UPPER(pce); DBMS_LOB.WRITE(tmp,len,pos+1,pce); pos := pos + len; END LOOP; -- DBMS_SQL.CLOSE_CURSOR(cur); -- /* Now use DBMS_LOB funcs. to examine */ IF DBMS_LOB.INSTR(tmp,UPPER(VALUE)) > 0 THEN ret_val := 1; ELSE ret_val := 0; END IF; -- RETURN ret_val; END; / HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Ed Bittel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 6:53 AM To: Multiple recipients of list ORACLE-L Subject: Size of a Long Field How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Size of a Long Field
Oops, my bad. Well I have limited experience with longs because I hate them with a passion. Whenever one of the developers says they are going to use long, I say "not on my database". You people are only making me feel better about that philosophy. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Dennis, Using VSize on a Long results in: ORA-00997: illegal use of LONG datatype Just tried in on Trigger_Body in DBA_Triggers. (8.1.7.0.0 on HP-UX). Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] DENNIS WILLIAMS <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: RE: Size of a Long Field 02/05/2003 01:29 PM Please respond to ORACLE-L Ed - Look up the function VSIZE in the documentation. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 8:53 AM To: Multiple recipients of list ORACLE-L How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of a Long Field
This is for LONG. For LONG RAW, you could use utl_raw.length or use a C or Java program to read it and calculate the length. CREATE OR REPLACE FUNCTION l_length(cTabName varchar2, cColName varchar2, cRowid varchar2) RETURN NUMBER IS cur_id integer; buff varchar2(32767); leninteger; offset integer; v_length integer; stmt varchar2(500); retinteger; BEGIN stmt := ' SELECT '|| cColName || ' FROM ' || cTabName || ' WHERE rowid = '||||cRowid||; cur_id := dbms_sql.open_cursor; dbms_sql.parse(cur_id, stmt, dbms_sql.NATIVE); dbms_sql.define_column_long(cur_id, 1); ret := dbms_sql.execute(cur_id); IF (dbms_sql.fetch_rows(cur_id) > 0 ) THEN offset := 0; len := 0; LOOP dbms_sql.column_value_long(cur_id, 1,32767, offset, buff, v_length); len := len + v_length; EXIT WHEN v_length < 32767; offset := offset + v_length; END LOOP; END IF; dbms_sql.close_cursor(cur_id); return( len ); END; / -Original Message- Sent: Wednesday, February 05, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Not easy. If your LONG is shorter than 32767, then you can, in a PL/SQL function, fetch it into a VARCHAR2 of this size (allowed in PL/SQL) and aplly LENGTH() to this VARCHAR2. Assuming the suitable Oracle version, I think there is somewhere a function to convert LONGs to CLOBs - to which you can apply a function. With Pro*C or the OCIs, you can derived the length from the error you get when fetching into too small a buffer ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of a Long Field
Using pl/sql convert long column to clob and then use dbms_lob.get_length(). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 2:30 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Size of a Long Field Try this, Select table_name,column_name,data_length,data_type from dba_tab_columns where owner = '' and data_type='LONG' ; -- Babu -Original Message-From: Ed Bittel [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 6:53 AMTo: Multiple recipients of list ORACLE-LSubject: Size of a Long Field How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
