Home » Developer & Programmer » Forms » How to run a SQL-statement in Forms (Oracle Forms 10.1.2.0.2)
How to run a SQL-statement in Forms [message #310036] Mon, 31 March 2008 00:47 Go to next message
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.



Re: How to run a SQL-statement in Forms [message #310048 is a reply to message #310036] Mon, 31 March 2008 01:22 Go to previous messageGo to next message
azamkhan
Messages: 548
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
Re: How to run a SQL-statement in Forms [message #310050 is a reply to message #310048] Mon, 31 March 2008 01:39 Go to previous messageGo to next message
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
Re: How to run a SQL-statement in Forms [message #310055 is a reply to message #310050] Mon, 31 March 2008 01:55 Go to previous messageGo to next message
azamkhan
Messages: 548
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
Re: How to run a SQL-statement in Forms [message #310064 is a reply to message #310055] Mon, 31 March 2008 02:37 Go to previous messageGo to next message
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
Re: How to run a SQL-statement in Forms [message #310069 is a reply to message #310064] Mon, 31 March 2008 03:11 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
Dear Friend,

Why are you not storing your code in the database as a procedure or function and then call it from FORMS.

From,
Azam Khan
Re: How to run a SQL-statement in Forms [message #310076 is a reply to message #310069] Mon, 31 March 2008 03:37 Go to previous messageGo to next message
rajiv.datta
Messages: 21
Registered: June 2007
Location: London
Junior Member

just check "EXEC_SQL" package it might be useful for you.

this package helps executing sql statements.

Rajiv
Re: How to run a SQL-statement in Forms [message #310087 is a reply to message #310076] Mon, 31 March 2008 04:04 Go to previous messageGo to next message
rajiv.datta
Messages: 21
Registered: June 2007
Location: London
Junior Member

you can also check FORMS_DDL. This is easier to implement than EXEC_SQL

rajiv

[Updated on: Mon, 31 March 2008 04:05]

Report message to a moderator

Re: How to run a SQL-statement in Forms [message #310197 is a reply to message #310087] Mon, 31 March 2008 09:38 Go to previous message
tbrolin
Messages: 4
Registered: March 2008
Junior Member
Forms DDL did the trick!!!

Thank you VERY much!!!


Best regards,

Thommy
Previous Topic: Problem in WHEN-TREE-NODE-SELECTED Trigger
Next Topic: how to enter phone no starting with 0..
Goto Forum:
  


Current Time: Fri Dec 02 14:27:39 CST 2016

Total time taken to generate the page: 0.14179 seconds