Home » Developer & Programmer » Reports & Discoverer » problem with execute immediate in Report 6i
problem with execute immediate in Report 6i [message #160850] Tue, 28 February 2006 12:43 Go to next message
charles_jess
Messages: 3
Registered: February 2006
Junior Member
Hi,

I am having problems with execute immediate statement its giving me an error in oracle report 6i

function BeforeReport return boolean is
sqlcode varchar2(1000);
begin
sqlcode := 'create global temporary table temp as select * from sample';
execute immediate (sqlcode);
end;

Could you let me know my mistake.

Thanks
Re: problem with execute immediate in Report 6i [message #160911 is a reply to message #160850] Wed, 01 March 2006 00:30 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You cannot use EXECUTE IMMEDIATE in Reports (or Forms, for all that matter). Oracle Developer Suite has its own PL/SQL engine for executing client side PL/SQL. Why would you recreate the global temporary table over and over? A GTT is created once and reused. The only difference with a regular table is that its content is volatile: either on the end of your transaction or at the end of your session your data is removed from it. But the table itself continues to exist.

MHE
Re: problem with execute immediate in Report 6i [message #566398 is a reply to message #160850] Fri, 14 September 2012 01:21 Go to previous messageGo to next message
deepak.apps
Messages: 1
Registered: September 2008
Location: Gurgoan Indian
Junior Member

SRW.DO_SQL will help to write dynamic sql in report.

e.g

SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULLPRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');
Re: problem with execute immediate in Report 6i [message #566418 is a reply to message #566398] Fri, 14 September 2012 06:28 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
@deepak.apps - Welcome to the forum. Can you please read and follow the orafaq forum guide - especially the part on how to format your post.

If you want to help people out, and help is appreciated, may I suggest you pick a thread to answer that's recent? This one is from 2006.

In addition - while srw.do_sql will allow you to do dynamic sql, I can't think of a single scenario in which creating a table in a report is a good idea. Maaher's original suggestion that the table be created once is correct.

EDIT: typo

[Updated on: Fri, 14 September 2012 06:28]

Report message to a moderator

Previous Topic: sorting on different columns
Next Topic: very strange problem on pdf file
Goto Forum:
  


Current Time: Fri Mar 29 10:33:40 CDT 2024