Pl/SQL Error: ORA-06550

[Exie]

Hi,
Trying to run some code like this:

--/
CREATE OR REPLACE PROCEDURE UTDELETE is
DECLARE
	mystr varchar2(20);
BEGIN
 	for c1 IN (      	
		  		select table_name as tb 
		       	from sys.all_tables 
		        	where owner = 'MTMS'
		         	order by table_name
		          )  LOOP

             mystr := 'TEST';
/*
		for c2 in (select column_name from all_tab_cols where owner= 'MTMS' and table_name = c1.table_name and rownum = 1) 
		loop
			dbms_output.put_line( 'Processing: ' || c2.column_name || ' for ' || c1.table_name );
		end loop;
*/
 	END LOOP;
end;
/

I keep getting this error:
[small]

Executing: 

CREATE OR REPLACE PROCEDURE UTDELETE is
DECLARE
	mystr varchar2(20)

Procedure created.

Execution time:  0:00:00.244

Executing: 

BEGIN
 	for c1 IN (      	
		  		select table_name as tb 
		       	from sys.all_tables 
		        	where owner = 'MTMS'
		         	order by table_name
		          )  LOOP
             mystr := 'TEST'

ORA-06550: line 8, column 28:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
[Error Code: 6550]
[SQL State: 65000]


Execution time:  0:00:00.124

Executing: 

END LOOP

ORA-00900: invalid SQL statement
[Error Code: 900]
[SQL State: 42000]


Execution time:  0:00:00.116

Executing: 

end

ORA-00900: invalid SQL statement
[Error Code: 900]
[SQL State: 42000]


Execution time:  0:00:00.115

[/small]

Can anyone help ?

 

[LKBrwnDBA]

You have the LOOP statement inside the /*.. ..*/ comments,



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops, no...
Its the "DECLARE" (or missing semi-colon after mystr := 'TEST':

 1  CREATE OR REPLACE PROCEDURE UTDELETE is
  2  --DECLARE
  3      mystr varchar2(20);
  4  BEGIN
  5       for c1 IN (
  6                    select table_name as tb
  7                     from sys.all_tables
  8                      where owner = 'MTMS'
  9                       order by table_name
 10                    )  LOOP
 11               mystr := 'TEST';
 12  /*
 13          for c2 in (select column_name from all_tab_cols where owner= 'MTMS' and table_name = c1.table_name and rownum = 1)
 14          loop
 15              dbms_output.put_line( 'Processing: ' || c2.column_name || ' for ' || c1.table_name );
 16          end loop;
 17  */
 18       END LOOP;
 19* end;
 20  /

Procedure created.

SQL> show error
No errors.
SQL> exec UTDELETE;

PL/SQL procedure successfully completed.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

To clarify, Exie, think of the word "IS" as the eqivalent of the word "DECLARE" in PL/SQL Procedures and Functions. Therefore, you do not use "DECLARE" when you "CREATE OR REPLACE {PROCEDURE | FUNCTION}..."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”