Home » SQL & PL/SQL » SQL & PL/SQL » complex procedure to count spaces (10g , XP SP2)
icon5.gif  complex procedure to count spaces [message #308426] Mon, 24 March 2008 05:02 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I made that code :-

SQL> CREATE OR REPLACE PROCEDURE count_space (x1 VARCHAR2)
  2  IS
  3     var   VARCHAR2 (999);
  4  BEGIN
  5     EXECUTE IMMEDIATE '  SELECT   LENGTH (x1)
  6          - LENGTH (REPLACE (x1, '' '' , '''' ))
  7  into var
  8     FROM DUAL ';
  9  
 10     DBMS_OUTPUT.put_line ('the Spaces count is : ' || var);
 11  END count_space;
 12  /

Procedure created.

SQL> show error
No errors.


and when I tried to perform it ..... that's happen :-

SQL> execute count_space ('aaa bbb ccc')
BEGIN count_space ('aaa bbb ccc'); END;

*
ERROR at line 1:
ORA-00904: "X1": invalid identifier
ORA-06512: at "SYSTEM.COUNT_SPACE", line 5
ORA-06512: at line 1


what's the wrong here Rolling Eyes Question Exclamation
Re: complex procedure to count spaces [message #308429 is a reply to message #308426] Mon, 24 March 2008 05:22 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
You don't need to use a Dynamic sql for that or you can try using a FUNCTION.
FUNTION fun_name () return number
begin
return length() - length(replace(txt,' '));
end;


regards,
Re: complex procedure to count spaces [message #308431 is a reply to message #308429] Mon, 24 March 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@dhananjay
If you give pseudo-code don't put it in code tags or give correct syntax.

@Hany Freedom
When you want to use a feature at least read the documentation about it.
EXECUTE IMMEDIATE

Regards
Michel
Re: complex procedure to count spaces [message #308435 is a reply to message #308431] Mon, 24 March 2008 05:32 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Michel, Point noted Smile


regards,
Re: complex procedure to count spaces [message #308447 is a reply to message #308426] Mon, 24 March 2008 06:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't use user SYSTEM. It is not meant to be used as some test-and-play-around-with user.
Previous Topic: column format
Next Topic: DBMS_OUTPUT.PUT_LINE in Trigger
Goto Forum:
  


Current Time: Thu Dec 08 10:32:52 CST 2016

Total time taken to generate the page: 0.09965 seconds