Re: length(longstring)

From: Ahmad <amabsout_at_yahoo.com>
Date: Wed, 14 May 2003 11:28:41 +0400
Message-ID: <b9sras$bt27@news-dxb.emirates.net.ae>

Hello,

check this code

Good Luck !

CREATE OR REPLACE FUNCTION l_length(cTabName varchar2,

                                       cColName varchar2,

 cRowid varchar2)

    RETURN NUMBER
    IS

           cur_id integer;
   buff   varchar2(32767);
           len    integer;
    offset integer;
     v_length integer;
           stmt varchar2(500);
           ret    integer;

    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;
/

---

2. You can then use the below given sample PL/SQL block with the

   above function to retrieve the lengths of long column:

   NOTE : Do not forget to replace the TABLE_NAME, LONG_COLUMN_NAME    with your own table and long column names respectively.

   Before running the following block from SQL*PLUS, set the server    output to some big buffer so that the dbms_output package can work    properly.

---

   set serveroutput on size 100000;

   DECLARE

      len number;
      cRowid  varchar2(30);
      num number;
      cursor T_CURSOR is
         SELECT rowid
             FROM TABLE_NAME;
   BEGIN
      open T_CURSOR;
      LOOP
         FETCH  T_CURSOR
          INTO  cRowid;
   EXIT WHEN T_CURSOR%NOTFOUND;
         len := l_length('TABLE_NAME', 'LONG_COLUMN_NAME', cRowid);
         dbms_output.put_line(rowidtochar(cRowid) || '' ||
                           to_char(len, '999999'));
      END LOOP;

   END; "DD" <dd_at_somewhere.com> wrote in message news:WU6wa.2494$0i2.2252_at_news.primus.ca...
> Hi all,
>
> How to get the length of a long datatype? I got ora-00932:inconsistent
> datatypes.
>
> TIA
>
> -s
>
>
Received on Wed May 14 2003 - 02:28:41 CDT