Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL parsing problem
DBMS_SQL parsing problem [message #193797] Tue, 19 September 2006 07:59 Go to next message
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 #193851 is a reply to message #193797] Tue, 19 September 2006 12:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Are you sure that changing to Replace(Et.Exportquery, chr(10), ' ') won't solve the problem?
Re: DBMS_SQL parsing problem [message #193982 is a reply to message #193851] Wed, 20 September 2006 05:07 Go to previous messageGo to next message
agrawal_vaibhao
Messages: 20
Registered: May 2006
Junior Member

No,
It won't solve problem, actully it is giving error while parsing data using dbms_sql's parse procedure for data of length greater than 8000.
Re: DBMS_SQL parsing problem [message #194070 is a reply to message #193797] Wed, 20 September 2006 12:52 Go to previous messageGo to next message
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 #194145 is a reply to message #194070] Wed, 20 September 2006 23:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Find => DBMS_SQL.VARCHAR2S

http://www.unix.org.ua/orelly/oracle/bipack/ch02_03.htm
Re: DBMS_SQL parsing problem [message #194155 is a reply to message #194070] Thu, 21 September 2006 00:49 Go to previous message
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.
Previous Topic: bulk fetch
Next Topic: IN Clause Problem
Goto Forum:
  


Current Time: Sat Dec 03 06:20:50 CST 2016

Total time taken to generate the page: 0.09206 seconds