Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic sql (ORA-00900: invalid SQL statement)

904890Feb 2 2012 — edited Feb 2 2012
Hi Gures,

I have written this small procedure and getting error with update section , error getting is syntax error,
Please anybody can identify where is errror?
create or replace procedure pr_mc1 (
				table_name  in varchar2,
				col_name    in varchar2
)

AUTHID CURRENT_USER
 is


TYPE type_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

	m_id type_number;
	m_name DBMS_SQL.VARCHAR2_TABLE;

	sqlst varchar2(1000);
	start_time number;



Begin

	start_time:=dbms_utility.get_time;



	sqlst:=sqlst || '  select empid, ename FROM   '
		     || table_name ';
		     

							

	execute immediate sqlst 
	   bulk collect 
             into m_id, m_name;

		DBMS_OUTPUT.PUT_LINE ( m_id.COUNT ||
                             ' records fetched.' );

	DBMS_OUTPUT.PUT_LINE('time used without cursor '
			||(dbms_utility.get_time-start_time)/100);

	
	
  dynamic
	*sqlst:=		'forall i in  m_id.first..m_id.last  '*
*			||      ' update '||table_name  || '*
*			||	'	set '||col_name || ' = m_name(i)  '*
   
*			||	'		where '||table_name||'.empid = m_id(i);';*			
	dbms_output.put_line(sqlst);


	begin 
			execute immediate sqlst;

	exception 
			when no_data_found then
				dbms_output.put_line('no data found');

			
			when others then
				dbms_output.put_line('fetched error is '||sqlerrm);

	end;

	

			DBMS_OUTPUT.PUT_LINE ( m_idx.COUNT ||
                             ' records updated.' );

			DBMS_OUTPUT.PUT_LINE('time used to update  '
			||(dbms_utility.get_time-start_time)/100);





end;

/
error section is bold

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 1 2012
Added on Feb 2 2012
3 comments
988 views