Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Exclamation Unanswered: Replace Function!

    Hi the Masters

    I have a problem with REPLACE function it can only work in a field that is 4k bytes long... So the filed which I have is about 32k long and I need to replace the contents to certain values which will be inserted by the user....

    How can I safely do this do I break the field into chunks of 4000 bytes and the do the replace ? but now the problem if I split it won't I messup the field which I need to replace maybe cut it up in half....

    How can I do the split function so safely split the filed into chunks of 4000 bytes!

    Please help Masters!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    What datatype is the column?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    substr(column,1,4000) ??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Feb 2009
    Posts
    6
    The data type is varchar2(32000)

  5. #5
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by gmakinana
    The data type is varchar2(32000)
    Well the manual says that this is impossible:
    Quote Originally Posted by Oracle Manual
    The maximum length of VARCHAR2 data is 4000 bytes
    How did you define this column?
    Can you post the DDL for the table?

  6. #6
    Join Date
    Feb 2009
    Posts
    6
    Quote Originally Posted by The_Duck
    substr(column,1,4000) ??
    Hi there Duck..
    If I do a straight substring I might cut the parameter I need to replace...The parameter can be anywhere in the string....

  7. #7
    Join Date
    Feb 2009
    Posts
    6
    Quote Originally Posted by shammat
    Well the manual says that this is impossible:

    How did you define this column?
    Can you post the DDL for the table?
    Hi Shammat

    I do this in a plsql the varchar datatype there is 32k...But the replace function can only cater for 4000 bytes only...If you try and execute a replace command to a field that is more than 4000 bytes you get an error... So now what I want to do is to break this variable of 32k into chunks of 4000 bytes each and then individually do a replace command in them... But now my worry is that when I break this 32k variable I might truncate some of the parameters inside....

  8. #8
    Join Date
    Feb 2009
    Posts
    6
    Quote Originally Posted by gmakinana
    Hi Shammat

    I do this in a plsql the varchar datatype there is 32k...But the replace function can only cater for 4000 bytes only...If you try and execute a replace command to a field that is more than 4000 bytes you get an error... So now what I want to do is to break this variable of 32k into chunks of 4000 bytes each and then individually do a replace command in them... But now my worry is that when I break this 32k variable I might truncate some of the parameters inside....
    The column in the database is a long field but then I assign it on a 32k variable in the pl sql procedure...

  9. #9
    Join Date
    Feb 2009
    Posts
    6
    Quote Originally Posted by shammat
    Well the manual says that this is impossible:

    How did you define this column?
    Can you post the DDL for the table?
    It's a pittyt I am not at work to send you the actual pl sql... but this should give you an idea of what I'm talking about...

    ----------------------------
    declare

    varlong varchar2(32000);
    var1 varchar2(4000);
    var2 varchar2(4000);
    var3 varchar2(4000);
    var4 varchar2(4000);
    v_concat varchar2(32000);

    begin

    select long_field into varlong from database;

    split varlong into var1, var2, var3, var4;

    then
    select replace (var1,'v_para1', '0988273344') into var1;
    select replace (var2,'v_para2', '0988273344') into var2;
    select replace (var3,'v_para3', '0988273344') into var3;
    select replace (var4,'v_para4', '0988273344') into var4;

    v_concat := var1||var2||var3||var4;

    execute immediate v_concat;

    end;

  10. #10
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by gmakinana
    It's a pittyt I am not at work to send you the actual pl sql... but this should give you an idea of what I'm talking about...
    You should have told us from the start because the VARCHAR handling in PL/SQL is different to a column defined as VARCHAR

    I have no idea what you are trying to do. Your example does not make any sense, but the following works for me:
    Code:
    declare 
      varlong VARCHAR(32000);
    begin 
      select long_field into varlong from some_table;
      varlong := replace(varlong,'v_para1', '0988273344');
      varlong := replace(varlong,'v_para2', '0988273344');
      varlong := replace(varlong,'v_para3', '0988273344');
    end;
    /
    What data type is long_field?
    I hope you are not using the LONG data type

  11. #11
    Join Date
    Feb 2009
    Posts
    62
    then
    select replace (var1,'v_para1', '0988273344') into var1;
    select replace (var2,'v_para2', '0988273344') into var2;
    select replace (var3,'v_para3', '0988273344') into var3;
    select replace (var4,'v_para4', '0988273344') into var4;
    This is your problem.
    In Pl/Sql, a varchar2 has a limit of 32k.chrs
    In SQL, it has a limit of 4000 chrs

    By using REPLACE in a SELECT statement, you are forcing it to use the SQL restriction (and making your code run much more slowly).

    I bet you did SQL Server and T-Sql before you did Oracle and Pl/Sql.

    In fact, looking at it, that IS T-Sql - that code wouldn't work in Oracle unless you add a 'FROM DUAL' onto the end of each select statement.

    Replace This:
    Code:
    select replace (var1,'v_para1', '0988273344') into var1;
    with this:
    Code:
    var1 := replace (var1,'v_para1', '0988273344');

Posting Permissions

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