Forms_DDL compile package

Posted on 2002-07-18
Last Modified: 2007-12-19
Hi all,
I'm using Forms 4.5, Oracle7 Server Release
I have a Forms application that is supposed to switch
a package in the database from an old version to a new Version and back(if necessary).
There are two files say in directory C:\temp named like
"newVers.sql" and "oldVers.sql"
containing the proper code to create or replace the packages.
I created a Canvas with two buttons each with something like the following code in the WHEN-BUTTON-PRESSED trigger:
If I issue the command directly in SQL+, everything works fine so the content of the .sql files is not the problem.
If I press the button in the Forms application I get the error ORA-00900, invalid SQL statement.
Can anybody tell me where I'm going wrong?

thanks in advance
horst borscht
Question by:horstborscht
LVL 27

Expert Comment

ID: 7162015
simple, you can't run a file-script in forms,
but (depending on the content of the file)
you could load it via text_io into a varchr2 variable,
and then try the forms_ddl-call

meikl ;-)


Expert Comment

ID: 7162022
@ is a SQL*Plus command.  It will not work in Forms.  Also, most PL/SQL that you execute through the database will run from the server, so it would be looking for the c: drive on the server.  You need to understand that the SQL*Plus environment is different than the Forms/Server environment.  

I recommend that you store these packages in a database table rather than a file on the c: drive.  When ready to run it, load it from the database table into a variable and then run FORMS_DDL (:variable);

Furthermore, why are you changing a package dynamically?  Wouldn't it be easier to create 2 different packages and then call the one you need rather than having to constantly change the packages out?  You can dynamically call the correct package using FORMS_DDL or EXECUTE IMMEDIATE.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 7163354
Another possibility: use a "host" command from Forms to launch SQL*Plus and pass it the appropriate *.SQL file.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Expert Comment

ID: 7164091
call 'host sqlplus user/password@connect_string @c:\temp\newVers.sql'
NOTE : there are functions to obtain username, password and connect_string you are connected in forms.

Accepted Solution

venkotch earned 50 total points
ID: 7164555
Forms_DDL is a build-in procedeure to execute any DLL statetement. You can have Updates, Deletes, Inserts, Alters, Create objects, Execute etc. in Forms_DDL. With one word Forms_DDL expects SQL language inside - hence '@c:\temp\newVers.sql' doesn't mean anything to forms_ddl. You have several options here to solve your problem.
1) Get the sql you have in the file and put it into the forms_ddl.
Example: if you have in the file:
create or replace view (
curr_date )
as select sysdate from dual;
instead of forms_ddl('@c:\temp\newVers.sql');
forms_ddl('create or replace view ('||
'as select sysdate from dual');

Pay attention that in forms_ddl the semicolumn is missing.
If you need to exec a procedure from Forms_DDL, use begin - end structure - Forms_DDL('begin my_proc; end');

2) the second option is simply to use sqlPlus instead of Forms_DDL. Then you can execute for example plus33w.exe with parameter '@c:\.....'. To do this you can use host() command or dde.App_Begin. Read more about this in forms help. Here is an example:
  AppID := DDE.App_Begin(my_ora_home||'\bin\plus33w.exe @c:\temp\newVers.sql',  DDE.APP_MODE_MINIMIZED);

good luck.

Expert Comment

ID: 9392350
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
accept venkotch's comment as an answer
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
querying by the sum of a column in decimal 7 57
SQL Query 34 82
How to free up undo space? 3 31
Can anyone please tell me what does below Stored Procedure does? 4 22
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now