Forms_DDL compile package

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
Who is Participating?
venkotchConnect With a Mentor Commented:
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.
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 ;-)

@ 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.

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Mark GeerlingsDatabase AdministratorCommented:
Another possibility: use a "host" command from Forms to launch SQL*Plus and pass it the appropriate *.SQL file.
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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.