Home » Open Source » MySQL » syntax error in stored procedures in mysql 5.0
syntax error in stored procedures in mysql 5.0 [message #175993] Tue, 06 June 2006 05:34 Go to next message
ksrk
Messages: 6
Registered: June 2006
Junior Member
Hello Techies,
I am very new to stored procedures and xperimenting on stored procedures
I am using mysql 5.0 and trying to create stored procedure. when i excute my procedure i am getting syntax error.


DELIMITER |
create procedure X
( xmlstr clob := null,
xmlstr_xml XMLTYPE,
fileHandle utl_file file_type,
charString varchar2(80) )
begin
--for getting the output on the screen
dbms_output.enable(1000000);
-- open the XML document in read only mode
fileHandle:=utl_file.fopen('C:\Documents and Settings\user\Desktop\','BACKUP.XML','r');
loop
begin
utl_file.get_line(fileHandle,charString);
exception
when no_data_found then
utl_file.fclose(fileHandle);
exit;
end |
xmlstr:= xmlstr||charString;
end loop;
-- Convert CLOB data into XMLTYPE instance
xmlstr_xml := XMLTYPE(xmlstr);
-- Extract all the rows and loop for each row
for i in ( select EXTRACTVALUE(VALUE(t), '/EMP/EMPNO') empno,
EXTRACTVALUE(VALUE(t), '/EMP/ENAME') ename,
EXTRACTVALUE(VALUE(t), '/EMP/JOB') job,
EXTRACTVALUE(VALUE(t), '/EMP/MGR') mgr,
EXTRACTVALUE(VALUE(t), '/EMP/HIREDATE') hiredate,
EXTRACTVALUE(VALUE(t), '/EMP/SAL') sal,
EXTRACTVALUE(VALUE(t), '/EMP/COMM') comm
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/EMPLOYEES/EMP'))) t )
loop
insert into emp values(i.empno,i.ename........);
end loop;
exception
when no_data_found then
dbms_output.put_line('no data found');
end |


Here is the error my message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clob:= null,
xmlstr_xml XMLTYPE,
fileHandle utl_file file_type,
' at line 2

Am i missing some thing??

regards,
Krish.
Re: syntax error in stored procedures in mysql 5.0 [message #175997 is a reply to message #175993] Tue, 06 June 2006 05:44 Go to previous message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
This is an Oracle site and maybe not the best place to ask MySQL questions.
Previous Topic: Will MySQL kill Oracle?
Next Topic: Migration From MySQL to Oracle
Goto Forum:
  


Current Time: Thu Oct 29 06:40:20 CDT 2020