I have changed subject line

1. Pre-10g use "translate" to change all numbers to a character which would
normally not be seen in your source string, then "instr" for first
occurrence of that character

eg in pl/sql

v_origstr := 'ABC F1 78HGTFSB NMKO 34'
v_rstr := translate (v_origstr, '1234567890','##########')

-- answer will be c

v_firstpos := instr(v_rstr,'#',1)

-- answer will be 6

or you could wrap it all up in one line as in this sql example

select
instr(translate
('ABC F1 78HGTFSB NMKO 34', '1234567890','##########'),'#',1)
from dual;

In 10g you should look up new features incl regular expressions. You may be
able to use REGEXP_REPLACE or others here


2. Again, a neat solution for 10g would be found in regexp

otherwise

DECLARE
x number;

FUNCTION get_numspaces(v_origstr varchar)
RETURN number
IS
v_numspaces number :=0;
BEGIN
dbms_output.put_line ('num charcters is '||LENGTH(v_origstr));
FOR i IN 1..LENGTH(v_origstr)
LOOP
dbms_output.put_line (I||' -- '''|| SUBSTR(v_origstr,I,1)||'''');
IF (SUBSTR(v_origstr,I,1)=' ') then
v_numspaces := v_numspaces +1 ;
END IF;
END LOOP;
-- dbms_output.put_line (v_numspaces);
RETURN v_numspaces;
END;

BEGIN
X := get_numspaces('ABC F1 78HGTFSB NMKO 34') ;
dbms_output.put_line ('num spaces is '||x);
END;
/



-----Original Message-----
From: martin
[mailto:oracledba-ezmlmshield-x56489495.[Email address protected]
Sent: 27 June 2007 21:23
To: LazyDBA Discussion
Subject: RE: Problems with dbms_stats


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