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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Require data to appear on a single line 2 79
PL SQL Developer 7 73
Oracle DBLINKS From 11g to 8i 3 67
capture vmstat info and insert it into an oracle table 31 61
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

733 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