Home » SQL & PL/SQL » SQL & PL/SQL » Extracting data from clob gives ora-06502 (9.2.0.8)
Extracting data from clob gives ora-06502 [message #333396] Fri, 11 July 2008 09:22 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi ,

I have statspack report stored as clob and need to write a function to extract the value of Redo size parameter from the clob.

I have written the below function but it gives ora-06502, can you please help about this:


QL> desc stats_data
Name                                      Null?    Type
----------------------------------------- -------- -------------------------
SP_FILE_NAME                              NOT NULL VARCHAR2(30)
INSTANCE_NAME                                      VARCHAR2(12)
SNAPSHOT_START_TIME                       NOT NULL DATE
SNAPSHOT_END_TIME                         NOT NULL DATE
STATSPACK_DATA                                     CLOB

The clob column has the statspack report in it, from which I ahve to extract the values.

so assume that it has records like:

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            161,173.24             21,005.68
              Logical reads:             12,858.56              1,675.85
              Block changes:                598.90                 78.05
             Physical reads:                619.25                 80.71
            Physical writes:                 45.52                  5.93
                 User calls:              2,965.86                386.54
                     


and I need to retrive the Redo size (per second) so I wrote a procedure:


 create or replace procedure
 sp_extract_metric(p_metric_name varchar2,p_sp_file_name varchar2) is
 v_metric_value varchar2(100);
 v1_metric_value number;
 v_clob_data1 varchar2(100);
 v_instance_name varchar2(12);
 v_snapshot_starttime date;
 v_snapshot_endtime date;
 v_stmt varchar2(100);
 begin
v_stmt:= 'select instance_name,
 ltrim(rtrim(substr(statspack_data,dbms_lob.instr(statspack_data,'':''
      ,dbms_lob.instr(statspack_data,:1),1)+1, 23)))
  from stats_data where sp_file_name=p_sp_file_name;';
execute immediate v_stmt
into v_instance_name, v_clob_data1
using p_metric_name;
 dbms_output.put_line(v_clob_data1);
 exception
 when others then
 dbms_output.put_line(sqlerrm);
 end;

when I execute this procedure, it gives error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small



Any pointers will be very helpful to me.

thanks
Nirav




Re: Extracting data from clob gives ora-06502 [message #333398 is a reply to message #333396] Fri, 11 July 2008 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Almost 600 post & still don't know how to use SEARCH on this forum or GOOGLE!.
How SAD!
Re: Extracting data from clob gives ora-06502 [message #333407 is a reply to message #333396] Fri, 11 July 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You also don't know how to post.
The error comes from which line?
Why do you post only part of the error message?
Oh yes! Because you use this stupide "when others" clause.
Do you see now why it is stupid?

Regards
Michel
Re: Extracting data from clob gives ora-06502 [message #333410 is a reply to message #333396] Fri, 11 July 2008 10:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nirav_hyd wrote on Fri, 11 July 2008 16:22

Any pointers will be very helpful to me.


You registered in December 2005, which means that you have been a developer for over 2.5 years now.
Sorry, but if you are still not able to trace the cause of this error in a 15 lines piece of code, you either didn't learn anything the last few years, or you are just being lazy and rather ask here than think for yourself.
The error is SO obvious.
Re: Extracting data from clob gives ora-06502 [message #333707 is a reply to message #333396] Mon, 14 July 2008 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why are you using Execute Immediate for this?

Convert the sql to a normal SELECT... INTO, get rid of the WHEN OTHERS, and (if you're on 10g or higher) consider using an easier to read way of getting the data out of the CLOB.

This should work:
to_char(regexp_substr(col_1,'^.*Redo.*$',1,1,'m'))


As to the problem that you've posted - did you do any work on this? Hint: Check the length of your select statement.
Re: Extracting data from clob gives ora-06502 [message #333804 is a reply to message #333707] Mon, 14 July 2008 08:33 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you JRowbottom.

We are on Oracle 9i and I used query like:

--to get redo size per transaction
select
ltrim(rtrim(substr(stats_data,dbms_lob.instr(stats_data,':',
dbms_lob.instr(stats_data,'Redo size'),1)+24, 22))) from
statspack_lk
/


this query I put in a pl/sql (by selecing it into a variable in pl/sql block) and it is getting the data. I did not check the approach u suggested in hint, the length is 140916 for one of the statspack reports.

But this approach is resulting into very large pl/sql block as there are more than 30 parameters for which I have to repeat similar pl/sql block but this is what is working for me now.

Regards
Nirav

[Updated on: Mon, 14 July 2008 08:35]

Report message to a moderator

Re: Extracting data from clob gives ora-06502 [message #333809 is a reply to message #333804] Mon, 14 July 2008 08:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your select statement has a length of over 32767 chrs then you are unable to use Execute Immediate until you upgrade to 11g, at which point it will allow you to use a CLOB.

Options:
1) Look into Dbms_Sql instead of Execute Immediate
2) Use static sql
3) rewrite your query to take up less space
Re: Extracting data from clob gives ora-06502 [message #333812 is a reply to message #333809] Mon, 14 July 2008 08:51 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I used the second option of Static SQL, with query like the one earlier, repeating 30 times or so, all in one pl/sql block, in one large sql statement, but my doubt is whether this approach is correct or should it be improved...

Thanks
Nirav

Re: Extracting data from clob gives ora-06502 [message #333814 is a reply to message #333396] Mon, 14 July 2008 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
All the data contained within the statpack report already exists within the database, so exactly why you are struggling to extract it from a flat file leaves me to wonder WHY?

Why not obtain this data from tables in the DB where it originates?
Re: Extracting data from clob gives ora-06502 [message #333817 is a reply to message #333814] Mon, 14 July 2008 08:59 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
It is the production data which we want to analyze in our Dev environment, so Prod. DBAs have scheduled a weekly job send this prod. Statspack reports to us, we don’t access the Prod. Database directly but want to keep record of this statspack reports and so we are storing it in our Dev DB.
Re: Extracting data from clob gives ora-06502 [message #333826 is a reply to message #333817] Mon, 14 July 2008 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why don't you ask for an export of PERFSTAT schema instead of reports? You can then query PERFSTAT tables and can get more information than from Statspack reports.

Regards
Michel
Re: Extracting data from clob gives ora-06502 [message #333832 is a reply to message #333826] Mon, 14 July 2008 09:28 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thanks, I will suggest that approach.

Regards,
Nirav
Re: Extracting data from clob gives ora-06502 [message #333840 is a reply to message #333396] Mon, 14 July 2008 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
A DBLINK to a read-only schema in production would be a painless solution.
Re: Extracting data from clob gives ora-06502 [message #333841 is a reply to message #333840] Mon, 14 July 2008 09:54 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the environment.
I have from time to time to audit some databases, I always ask for an export of PERFSTAT and then import it on my laptop and run my scripts.
Then:
- I don't need and have access to production so my customers are happy for confidentiality and security reasons
- I don't induce any load on production system
- I can run as many as scripts I want, when I want
It is most often sufficient to diagnose the problems.

Regards
Michel
Previous Topic: New Features 8i/9i
Next Topic: What are the possible alternatives for rule hint
Goto Forum:
  


Current Time: Thu Dec 08 10:28:39 CST 2016

Total time taken to generate the page: 0.09657 seconds