Home » SQL & PL/SQL » SQL & PL/SQL » Problem in execution of a function having clob datatype
Problem in execution of a function having clob datatype [message #361667] Thu, 27 November 2008 06:26 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

I had a Problem in executing a function having clob datatype.

Here below is my function.

create or replace type split_obj as object
  (
    occurence_id number(10,0),
    splitvalue varchar2(1000)
  );



create or replace type split_rec as table of split_obj;


create or replace FUNCTION Split_new1(
                                       p_string IN clob,
                                       p_delimiter IN VARCHAR2 
                                     )  return split_rec  pipelined  is
    v_length NUMBER := dbms_lob.getlength(p_string);
      v_start NUMBER := 1;
     v_index NUMBER;
      V_temp NUMBER(10,0):=0;
   begin
 WHILE(v_start <= v_length)
    LOOP
      v_index := dbms_lob.INSTR(p_string, p_delimiter, v_start);
   
       IF v_index = 0 THEN
         V_temp:=V_temp+1;
         PIPE ROW(split_obj(v_temp,dbms_lob.SUBSTR(p_string, v_length,v_start)));
         v_start := v_length + 1;
       ELSE
         v_temp:=v_temp+1;   
         PIPE ROW(split_obj(v_temp,dbms_lob.SUBSTR(p_string, v_index - v_start,v_start)));
         v_start := v_index + 1;
       END IF;
     END LOOP;
    return;
   end Split_new1;



Here attached the procedure trying to execute the function with huge string more than 500kb.

Could you pls let me know how to do the same in sqlplus and toad.

Thanks in advance.
  • Attachment: Execution.txt
    (Size: 403.07KB, Downloaded 190 times)
Re: Problem in execution of a function having clob datatype [message #361670 is a reply to message #361667] Thu, 27 November 2008 06:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
And what is the problem you are encountering ?

Regards

raj
Re: Problem in execution of a function having clob datatype [message #361682 is a reply to message #361670] Thu, 27 November 2008 07:31 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Here below is the problem. Tried with bind varaible and encountered the same error.

Error(9,18): PLS-00172: string literal too long


Thanks in advance
Re: Problem in execution of a function having clob datatype [message #361683 is a reply to message #361682] Thu, 27 November 2008 07:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is there any difference between this post and your previous post ?

http://www.orafaq.com/forum/m/358503/94420/#msg_356128

Regards

Raj
Re: Problem in execution of a function having clob datatype [message #361764 is a reply to message #361683] Thu, 27 November 2008 23:56 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,


Yes, I agree but i haven't got answer for that. So in the given attachement I am trying to assign a single string greater than 32K to the clob. It's the single string that exceeding 32K and is the problem..


Whilst the CLOB itself can hold more data, the string is actually the equivalent of a VARCHAR2 so cannot exceed VARCHAR2 limits.If it is true how to meet the above scenario.

Tried with below example
create or replace
function  temp return sys_refcursor as
v_temp  SYS_REFCURSOR;
b clob;
--b clob:=dbms_lob.empty();
a varchar2(4000):='1$-1$489$53$Process$$2$$$$$-1$50$70$No$$4$$|3$-1$440$233$-$';
begin
for i in 1.. 1000000000000000
loop
dbms_lob.append(b, a);
end loop;
 open v_temp for select *
      from table(cast(Split_new(b,'$') as split_rec));
      return v_temp;
end;

Encountered with below error

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 366
ORA-06512: at "MIGRATION.TEMP", line 9
ORA-06512: at line 1





Could you pls check and let me know. Where I am going wrong?

Thanks in advance.

[Updated on: Fri, 28 November 2008 07:27]

Report message to a moderator

Re: Problem in execution of a function having clob datatype [message #362212 is a reply to message #361764] Mon, 01 December 2008 05:52 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Tried with much reducing loop.


create or replace
function  temp return sys_refcursor as
v_temp  SYS_REFCURSOR;
b clob;
--b clob:=dbms_lob.empty();
a varchar2(4000):='1$-1$489$53$Process$$2$$$$$-1$50$70$No$$4$$|3$-1$440$233$-$';
begin
for i in 1.. 1000
loop
dbms_lob.append(b, a);
end loop;
 open v_temp for select *
      from table(cast(Split_new(b,'$') as split_rec));
      return v_temp;
end;


Execution



SQL> var x refcursor
SQL> exec :x:=temp;
BEGIN :x:=temp; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 366
ORA-06512: at "MIGRATION.TEMP", line 9
ORA-06512: at line 1





Thanks in advance
Previous Topic: Alternative syntax for Self joining of a table 3 times
Next Topic: Complex query - Time difference within duplicates
Goto Forum:
  


Current Time: Sat Dec 03 18:26:27 CST 2016

Total time taken to generate the page: 0.06190 seconds