| DBMS_SQL parsing problem [message #193797] |
Tue, 19 September 2006 07:59  |
agrawal_vaibhao
Messages: 20
Registered: May 2006
|
Junior Member |
|
|
Hi to All,
I am going to execute the plsql script by using dbms_Sql package.
While using that i got problem that, if my script exceeds 8000 characters then it gives me parsing related error.
I have check that dbms_sql package's parse procedure is unable to parse data greater that 32767 bytes.
So how can i solve by this problem.
My code is like as follows
Procedure TempProc As
l_cur Pls_Integer := DBMS_SQL.open_cursor;
l_dummy Pls_Integer;
Cursor ExportT Is
Select Replace(Et.Exportquery, chr(10)) Exportquery,
Et.ExportTemplateId
from TempTable Et
where Et.Processed_Date is null;
BEGIN
For v in ExportT Loop
Begin
DBMS_SQL.parse(l_cur, v.exportquery, DBMS_SQL.native);
l_dummy := DBMS_SQL.EXECUTE(l_cur);
Update TempTable Et
Set Et.Processed_Date = Sysdate, Status = 'Success'
Where Et.ExportTemplateId = v.ExportTemplateId;
Exception
When Others then
Update TempTable Et
Set Et.Processed_Date = Sysdate, Status = 'Failed'
Where Et.ExportTemplateId = v.ExportTemplateId;
End;
End Loop;
Commit;
End TempProc ;
Here i m retriving data into cursor and then passing that cursor's column to parse procedure.
So Plz Help me.
Thanks in Advanced!
|
|
|
|
|
|
|
|
| Re: DBMS_SQL parsing problem [message #194070 is a reply to message #193797] |
Wed, 20 September 2006 12:52  |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
What is a version of Oracle?
Large SQLs worked i my case
Connected to:
Oracle9i Release 9.2.0.7.0 - Production
JServer Release 9.2.0.7.0 - Production
MARK_MALAKANOV@TEST>declare
2 l_cur Pls_Integer := DBMS_SQL.open_cursor;
3 l_dummy Pls_Integer;
4 c char;
5 exportquery string(32565) := 'select * from dual';
6 BEGIN
7 for i in 1..1000 loop
8 exportquery := exportquery || ' union all select * from dual';
9 end loop;
10
11 dbms_output.put_line('sql len='||length(exportquery));
12
13 --l_cur := DBMS_SQL.open_cursor;
14 DBMS_SQL.parse(l_cur, exportquery, DBMS_SQL.native);
15 l_dummy := DBMS_SQL.EXECUTE(l_cur);
16 dbms_output.put_line('l_dummy='||l_dummy);
17
18 DBMS_SQL.close_cursor(l_cur);
19
20 END;
21 /
sql len=29018
l_dummy=0
PL/SQL procedure successfully completed.
|
|
|
|
|
|
| Re: DBMS_SQL parsing problem [message #194155 is a reply to message #194070] |
Thu, 21 September 2006 00:49 |
agrawal_vaibhao
Messages: 20
Registered: May 2006
|
Junior Member |
|
|
The DBMS_SQL work upto 32767 data size and in my case data size exceed 32767, it may be upto 50000 or more, so while parsing it gives me error.
Is it possible to parse clob type column using DBMS_SQL.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
