Re: Problems with dbms

Here is for your 2.

CREATE OR REPLACE FUNCTION num_occurs (p_str varchar2,p_substr varchar2)
RETURN number IS
BEGIN
IF p_str IS null OR p_substr IS null
THEN
RETURN null;
END IF;
RETURN
(length(p_str)-length(replace(p_str,p_substr,null)))/length(p_substr);
EXCEPTION
WHEN others THEN
RETURN null;
END;
/

select num_occurs('Miss, please press thiss!','ss') from dual;
--> 2

martin wrote:
>
> Hi All,
>
> Two things.
>
> 1) I would like some assistance in writing a function that
> Should return the first instance of a number found in that string i.e.
> ABC F1 78HGTFSB NMKO would return 6.
>
> 2) I would also like to find out how many time a space occurs in a
> string. I.e.. ABC F1 78HGTFSB NMKO 34 would return 4.
>
> Any help would be really appreciated.
>
> Thanks
>
> Martin Thomas
>
>
> -----Original Message-----
> From: karen
> [mailto:oracledba-ezmlmshield-x28005400.[Email address protected]
> Sent: Wednesday, June 27, 2007 3:10 PM
> To: LazyDBA Discussion
> Subject: Problems with dbms_stats
>
> I hope someone can help with a problem I'm having generating statistics
> in Oracle 9i. This problem started suddenly with no known changes to
> the database. The Oracle instance that this database is on also
> contains another database which is not having this problem.
>
> When executing the following:
> exec dbms_stats.gather_schema_stats(ownname=>'ORACLE',
> estimate_percent=>30, block_sample=>TRUE, cascade=>TRUE, method_opt=>
> 'for all indexes for all indexed columns size auto'); I get this error:
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at "SYS.DBMS_STATS", line 9136
> ORA-06512: at "SYS.DBMS_STATS", line 9616
> ORA-06512: at "SYS.DBMS_STATS", line 9800
> ORA-06512: at "SYS.DBMS_STATS", line 9854
> ORA-06512: at "SYS.DBMS_STATS", line 9831
> ORA-06512: at line 1
>
> If I execute an analyze on an individual table, I get the following
> error:
> dbms_stats.gather_table_stats(ownname=> 'ORACLE', tabname=> 'table_x',
> partname=> NULL);
> ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 1
> ORA-06512: at sys.dbms_stats, line .....
>
> If I execute an analyze on an individual index, it works correctly.
> dbms_stats.gather_index_stats(ownname=> 'ORACLE', indname=> 'index_y',
> partname=> NULL);
>
> And if I issue an analyze without using the dbms_stats package, it works
> correctly.
> ANALYZE TABLE "ORACLE"."table_x" COMPUTE STATISTICS;
>
> Thanks for your help.
> Karen Coulter
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
> dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>

Oracle LazyDBA home page

Re: Problems with dbms_stats

Re: Problems with dbms_stats