Home » Developer & Programmer » Forms » Calling SQL scripts from Forms in app server environment
Calling SQL scripts from Forms in app server environment [message #634702] Fri, 13 March 2015 09:31 Go to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
I'd like to know how people are calling SQL scripts from Forms on an app server, especially those that require user inputs via the PROMPT command.

We are using a method which I do not like and one day I'd like to change it.

We read in the script, parse for any lines with a PROMPT command, have the user enter the values and then HOST out to call SQL*Plus to execute the script.

I don't like it. I don't like it one bit. It was written way before I got here and never had the time to look at an alternative method.

Any thoughts?

[Updated on: Fri, 13 March 2015 09:32]

Report message to a moderator

Re: Calling SQL scripts from Forms in app server environment [message #634837 is a reply to message #634702] Mon, 16 March 2015 11:21 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
What do the SQL Script do? I don't understand why you would need to run a SQL script outside of Forms. Why not simply execute the process in Forms or better yet, create a database stored procedure/package that performs whatever the scripts are doing and then call those from Forms.

Just my thoughts...

Craig...
Re: Calling SQL scripts from Forms in app server environment [message #634845 is a reply to message #634837] Mon, 16 March 2015 13:07 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, good question Craig, but these are legacy SQL scripts that run a report based on multiple prompt statements, which produces output that the user then sends to a printer. If it just weren't for the output that is needed...

There are hundreds or these little reports and rather than rewrite them all, someone came up with the idea (and it was pretty cool at the time) to read in the script, parse out all the PROMPT statements and have the user enter them as parameters, then call SQL*Plus to run the scripts with the parameters on a command line.

I want to change this, but have the same issue. I do not want to rewrite hundreds of unique SQL scripts.
Re: Calling SQL scripts from Forms in app server environment [message #634882 is a reply to message #634845] Tue, 17 March 2015 08:11 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Well, sometimes we have to do things we really don't want to do. Sad I understand your aversion to rewriting the scripts, but maybe now is the time.
Quote:
...how people are calling SQL scripts from Forms on an app server, especially those that require user inputs via the PROMPT command.

You are the only person I've heard of that is doing this. One of the projects I was working on was using HOST to call Pro-C report scripts, but the parameters were "Hard Coded" so we didn't have to parse anything.

Sorry I couldn't be more helpful.

Craig...
Re: Calling SQL scripts from Forms in app server environment [message #634883 is a reply to message #634882] Tue, 17 March 2015 08:28 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
CraigB wrote on Tue, 17 March 2015 09:11

You are the only person I've heard of that is doing this. One of the projects I was working on was using HOST to call Pro-C report scripts, but the parameters were "Hard Coded" so we didn't have to parse anything.

Hahaha, I know. go figure, I come to this company with a proprietary application that one day is going to blow up because it won't work when "something changes."
Quote:

Sorry I couldn't be more helpful.


To the contrary, it just confirms what i thought has to be done.

I just have to solve how to run SQL scripts that produce output and get it to the printer without having to rewrite them as RDFs.

Thanks.
Re: Calling SQL scripts from Forms in app server environment [message #635170 is a reply to message #634883] Mon, 23 March 2015 10:29 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
I just have to solve how to run SQL scripts that produce output and get it to the printer without having to rewrite them as RDFs.

I wish you luck with this. Were I in your situation, I think I would make the argument with management to do away with the SQL scripts and go to either Oracle Reports or some other reporting tool.

Craig...
Re: Calling SQL scripts from Forms in app server environment [message #635183 is a reply to message #635170] Mon, 23 March 2015 14:25 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Yes Craig, it has been brought up, but at over 800 SQL scripts that produce output, they don't want to wait for the amount of time it would take to do that "if it ain't broken, don't fix it."

Re: Calling SQL scripts from Forms in app server environment [message #635193 is a reply to message #635183] Tue, 24 March 2015 04:58 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Nobody gets sacked for not changing the status quo Wink Of course, once things start going that's a different matter!
Previous Topic: Forms 6i to 11g migration issue
Next Topic: how to create auto alert in forms 6i after a certain time
Goto Forum:
  


Current Time: Sat Aug 15 08:19:18 CDT 2020