Re: Help: Creating View for substrings of LONG data type

bandired_at_myhost.subdomain.domain (Madhu Bandireddi) writes:
> I read that we cannot use 'substr' function on LONG data types.

I don't know if this is any help, but you can do limited substr-function for LONG-type in PL/SQL or Forms.

At least at V6.0.36.7.1, PL/SQL V1.0.36.2.2

Problem is function's numerical parameters, which are limited to under 256.

Example:
_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

CREATE TABLE foo (l LONG);
CREATE TABLE bar (s CHAR(255));

DECLARE
  i NUMBER;
  a CHAR(32000);
BEGIN   a := '';
  FOR i in 1..20 LOOP /* 1000 characters */     a := a || 'abcdefghijklmnopqrstuvxyzABCDEFGHIJKLMNOPQRSTUVXYZ';   END LOOP;   INSERT INTO foo VALUES (a);

END;
/

commit;

DECLARE
  a CHAR(32000);
  b CHAR(255);
BEGIN   SELECT l
    INTO a
    FROM foo;   

  LOOP
    b := substr(a,1,254);
    a := substr(a,255);
    INSERT INTO bar values(b);

    IF (a is null)
    THEN exit;
    END IF;
  END LOOP; END;
/

commit;

select * from bar;

DROP TABLE foo;
DROP TABLE bar;

_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

                                                olli R Received on Wed Aug 17 1994 - 16:23:04 CEST