long||varchar
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: long||varchar

  1. #1
    Join Date
    Aug 2001
    Posts
    134
    Select long||varchar from tablename;
    when I select the error occurs, how can I select this data ?
    Thanx

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    What is the error you are receiving?

    anyway, try using 'set long 32768' and 'set arraysize 1' in your login.sql file or SQL*PLUS file. Oversized varchars and longs tend to be unretrievable, but if they are small enough, these two could do it.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Mar 2001
    Posts
    314
    Can you concat a long with a varchar2?

    I think you will receive an inconsistent datatype error message from oracle

    -amar

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by jrpm
    What is the error you are receiving?

    anyway, try using 'set long 32768' and 'set arraysize 1' in your login.sql file or SQL*PLUS file. Oversized varchars and longs tend to be unretrievable, but if they are small enough, these two could do it.
    No, this won't do. There is no way you can perform concatenation or any other operation/function on LONG column in SQL. You'll get ORA-932 "inconsistent datatypes" error as amar has allready pointed out.

    But if values stored in LONG column are smaller than 32K then you can do it in PL/SQL, because in PL/SQL VARCHAR2 can be 32K in size and you can read long datatype in VARCHAR2 variable. Once you have your long column datata in a VARCHAR2 variable you can do any operation/transformation on it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    note that you can also use a long variable :


    declare
    var long;
    begin
    select long_column
    into var
    from table
    where ...;
    dbms_output.put_line(var || ' concatened with text');
    end;

  6. #6
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Sorry, misread the original.

    For some reason my brain interpreted the pipe as an 'or', not as concatenate. Implication: problem retrieving large fields.

    Fifty lashes with a wet noodle.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pipo
    note that you can also use a long variable :


    declare
    var long;
    begin
    select long_column
    into var
    from table
    where ...;
    dbms_output.put_line(var || ' concatened with text');
    end;
    Yes, but the question is why would you want to use LONG instead of VARCHAR2 type in PL/SQL. Namely, VARCHAR2 variable can store longer strings than LONG in PL/SQL!!! Actualy, in PL/SQL LONG datatype barely deserves its name - its maximum size is 32760 bytes, while VARCHAR2 has limit of 32767!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width