Forum Stats

  • 3,876,115 Users
  • 2,267,061 Discussions
  • 7,912,434 Comments

Discussions

contains/like in pl/sql variable of type clob

managed BEAN
managed BEAN Member Posts: 948 Silver Badge
edited Mar 29, 2017 8:01AM in SQL & PL/SQL

Hello everyone,

I have a clob variable in a pl/sql procedure where it is assigned a value.

I would like to know if the clob contains a 'substring' without the select statement, something alike to : clob.contains("%myText%") as in JAVA.

Is it possible? If so, how?

Thanks in advance.

Regards,

Carlos

Tagged:
SaubhikDejan T.Frank KulashBluShadowSven W.

Best Answer

«1

Answers

  • Dejan T.
    Dejan T. Member Posts: 438 Gold Badge
    edited Mar 28, 2017 12:16PM

    Use DBMS_LOB.INSTR:

    IF DBMS_LOB.INSTR(YourVariable, 'myText') > 0 THEN -- do somethingEND IF;
    managed BEAN
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 28, 2017 12:17PM Answer ✓
  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Mar 28, 2017 12:23PM

    Thanks for fast response.

    Are you sure that will work?

    As mentioned in post: this is a clob variable, not a column in a table.

    procedure myProc (   inParameter                 IN    number,  outParameter                OUT   number) is  clobVar                     clob;  existsVar              clob;begin(...)    clob  := 'some value';    select *    into existsVar    from clobVar    where DBMS_LOB.INSTR(*,'myText' ) > 0;if null != existsVar then    --do stuffend if(...)end;/

    Regards,

    Carlos

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 28, 2017 12:27PM

    DBMS_LOB.INSTR (

      lob_loc IN CLOB CHARACTER SET ANY_CS,

      pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET,

      offset IN INTEGER := 1,

      nth IN INTEGER := 1)

      RETURN INTEGER;

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Mar 28, 2017 12:57PM

    Carlos, to add to what John posted the SQL Single row functions are available in PL/SQL and the character functions were changed versions ago to work on CLOB's so you do not even have to make the reference to DBMS_LOB but can code like the following

    - -

    set serveroutput on

    [email protected]> l

      1  declare

      2  l_clob  clob;

      3  begin

      4  l_clob := rpad(l_clob,1000000,'X');

      5  l_clob := substr(l_clob,1,400000) || 'Y' || substr(l_clob,400001,1000000);

      6  if instr(l_clob,'Y') > 0 then

      7    dbms_output.put_line('Found Y');

      8  else

      9    dbms_output.put_line('Did not find Y');

    10  end if;

    11* end;

    [email protected]> /

    Found Y

    PL/SQL procedure successfully completed.

    - -

    If you change the character Y to anything other than X or Y you will see the 'Did not find' branch is taken.

    - -

    HTH -- Mark D Powell --

    ed - remove blank lines between PL/SQL code lines

    Saubhik
  • mathguy
    mathguy Member Posts: 10,920 Black Diamond
    edited Mar 28, 2017 1:49PM

    I don't understand (well, perhaps I do, but if my understanding is correct, then I don't like it).

    You asked a question. Dejan proposed a solution. You have your code, not Dejan. You have your doubts. You are asking Dejan if it will work? What is stopping you from testing it yourself?

    Asking for help is one thing. Not doing something you can obviously do yourself is not the same thing.

    SaubhikDejan T.Frank KulashBluShadow
  • Adric
    Adric Member Posts: 78 Blue Ribbon
    edited Mar 28, 2017 2:30PM

    What's your Oracle version? In 11.2.0.4, for example, the normal text functions should work for values already assigned to PL/SQL variables.

    [[email protected]_anorris ] SQL> declare  2     var1 clob;  3  begin  4     var1 := rpad( lpad( 'MyText', 4000, '*' ), 8006, '*' );  5     if instr( var1, 'MyText' ) != 0 then  6        dbms_output.put_line( var1 );  7     else  8        dbms_output.put_line( 'No match found' );  9     end if; 10  end; 11  /**********************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************MyText**********************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
  • Dejan T.
    Dejan T. Member Posts: 438 Gold Badge
    edited Mar 28, 2017 3:05PM

    Hi Carlos,

    have you tried the solution with the DBMS_LOB.INSTR ?

    procedure myProc (   
      inParameter             INnumber, 
      outParameter            OUT   number 
    ) is 
      clobVar                 clob; 
      existsVar   clob;
    begin 
    (...) 
        clobVar := 'some value myText lorem ipsum'; 
      
    if DBMS_LOB.INSTR(clobVar, 'myText') > 0 then 
        --do stuff 
    end if; 
    (...) 
    end; 
  • JPDominator
    JPDominator Member Posts: 60
    edited Mar 28, 2017 6:29PM

    11.2.0.4 you can just do a simple like statement.

    set serveroutput on;declarev_clob1 clob := 'hereissome clobdata';v_clob2 clob := 'hereisa little more clobdata';begin  if v_clob1 like '%some%' then    dbms_output.put_line('v_clob1 match');  end if;  if v_clob2 like '%some%' then    dbms_output.put_line('v_clob2 match');  end if;end;/
    Sven W.
  • managed BEAN
    managed BEAN Member Posts: 948 Silver Badge
    edited Mar 29, 2017 6:56AM

    Hello @mathguy

    Indeed, you didn´t understand it!

    I asked a question: yes;

    I was given a response: yes;

    Did the answer had something to do with the question: +/- ;

    Was i going to test in my code: yes;

    Did i get other answers before test it: yes (yours included);

    I mark 'helpful' and/or 'correct' some replies: yes;

    And to explain you why you didn´t understand well, Dejan first reply was not the one that now you see, he edited it after (to the reply you all see now), cause before he had something like:

    select col1

    from tab1

    where DBMS_LOB.INSTR(col1,'myText' ) > 0;

    that is why my response to him was:

    Capture.PNG

    and i was referring that his answer as if my case was in a table (which was not - it´s in a procedure), that´s why i was surprised and asked that.

    If you see in my answer (above pic) i put his suggestion adapted to my code (lines 12-15) which is refer to as if it was a table.

    The other answers gave me a lot more wide solution by providing me links with examples from which i learned how to work with the DBMS_LOB.INSTR and adapt my code to it to work.

    When people ask and give a use case it´s because they don´t know the exact answer, or have doubts(like you mentioned), but might know a little bit to see that a code applied to a select from a table will not work in a select from a CLOB.

    Understand now? (unfortunatelly i don´t have a screenschot from Deejan first response, before editing, to show you).

    Regards,

    Carlos

This discussion has been closed.