OraFAQ Forum: Forms » How to run a SQL-statement in Forms

Home » Developer & Programmer » Forms » How to run a SQL-statement in Forms (Oracle Forms 10.1.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to run a SQL-statement in Forms [message #310036]

Mon, 31 March 2008 00:47

tbrolin
Messages: 4
Registered: March 2008

Junior Member

I have 'generic' SQL-statements stored in my database, and read those statements into my form. I then add more code around my original statement, and want to run the newly composed SQL from Forms. How do I do that? My first idea was to use 'execute immediate', but that fails with the 591 error 'not supported in client-side programs'.

My trigger:

declare
sql_str varchar2(4000);
begin
sql_str :=
'insert into campaign_hits
(campid, id, cdate, cemp, udate, uemp)
select
:CAMPAIGN.CAMPID, compid, sysdate, :PARAMETER.emp, sysdate, :PARAMETER.emp
from
company
where compid in
('||:CAMPAIGN.SQL_STMT||');';
-- execute immediate sql_str;
end;

All input is much appreciated.

Report message to a moderator

Re: How to run a SQL-statement in Forms [message #310048 is a reply to message #310036]

Mon, 31 March 2008 01:22

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Dear Friend,

Why dont you put your code in a SQL file and make it a script and call it through forms using HOST command.

Regards,
Azam Khan

Report message to a moderator

Re: How to run a SQL-statement in Forms [message #310050 is a reply to message #310048]

Mon, 31 March 2008 01:39

tbrolin
Messages: 4
Registered: March 2008

Junior Member

Thanks for your answer.

Putting everything in a script-file, and run it using a HOST command is one option, but I really would like to do it without having to generate files on the server.

Isn't it possible to do this from within Forms?

Regards,

Thommy

Report message to a moderator

Re: How to run a SQL-statement in Forms [message #310055 is a reply to message #310050]

Mon, 31 March 2008 01:55

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Ok then dont generate it on server generate it on the Pc where it is being called. See TEXT_IO package.

Regards,
Azam Khan

Report message to a moderator

Re: How to run a SQL-statement in Forms [message #310064 is a reply to message #310055]

Mon, 31 March 2008 02:37

tbrolin
Messages: 4
Registered: March 2008

Junior Member

Thanks for your second answer.

I look into your suggestion!

But do you happen to know if it is at all possible to achieve what I'm trying to do from within Forms, i.e. not having to use external files/scripts?

Best regards,

Thommy

Report message to a moderator