| syntax error in stored procedures in mysql 5.0 [message #175993] |
Tue, 06 June 2006 05:34  |
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.
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
