CODEWITHSUNDEEP
stringoracle-databaseoracle11gsubstr
Radi Soufan
Radi Soufan

Reputation: 89

oracle: inter string by parameter and process it

I want to read a string from user then separate words every space character in the string I have this 

    declare
--counter1 number:=1;
--counter2 number:=1;
--instruction varchar(100);
str varchar2(100):= &inter;
begin
--while true
--loop
--instruction:=substr(str,counter,count2);
--counter2:=counter2+1;
--counter1:=counter1+1;
--exit when instruction = '% ';
--end loop;
SYS.DBMS_OUTPUT.PUT_LINE(str);
end;

but it gives me

    Error report -
ORA-06550: line 5, column 21:
PLS-00201: identifier 'TEST_STRING' must be declared
ORA-06550: line 5, column 5:
PL/SQL: Item ignored
ORA-06550: line 14, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

and also the exit when instruction = '% '; seems too wrong to me how can I stop on space character

Upvotes: 0

Views: 214

Answers (2)

APC
APC

Reputation: 146239

So this error ...

ORA-06550: line 5, column 21:
PLS-00201: identifier 'TEST_STRING' must be declared

... is probably down to this declaration:

str varchar2(100):= &inter;

Even though you are passing a substitution variable you still need to declare the assignment properly, as a string. Like this

str varchar2(100):= '&inter';

Anyway, here is a pure SQL solution.

with cte as 
   ( select 'oracle: inter string by parameter and process it' as str
     from dual )
select regexp_substr(str, '[^ ]+', 1, level) as word
from cte
connect by level <= regexp_count(str, chr(32))+1
/

The CONNECT BY is a useful trick to generate a rowset on the fly. Here is the output:

SQL> with cte as ( select 'oracle: inter string by parameter and process it' as str
  2            from dual )
  3  select regexp_substr(str, '[^ ]+', 1, level) as word
  4  from cte
  5  connect by level <= regexp_count(str, chr(32))+1
  6  /  

WORD
------------------------------------
oracle:
inter
string
by
parameter
and
process
it

8 rows selected.

SQL>

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You can do this using code similar to:

declare
  counter1 number:=1;
  counter2 number:=1;
  instruction varchar(100);
  str varchar2(100):= 'declaration of the type of this expression is incomplete';
begin
while counter1<length(str) loop
  while true loop
    instruction := substr(str, counter1, counter2);
    counter2 := counter2 + 1;
    exit when instruction like '% ' or counter1+counter2-1>length(str);
  end loop;
  dbms_output.put_line(trim(instruction));
  counter1 := counter1 + counter2-1;
  counter2 := 1;
end loop;
end;

... which gives this output:

declaration
of
the
type
of
this
expression
is
incomplete

It can also be done in several other ways, especially with using function instr() or with regular expressions or with connect by in sql. You were close, but in your code you did: when instruction = '% ' and should be like '% ' and you unnecessarily increased counter1 in each iteration.

Upvotes: 0

Related Questions