Solved

Forms_DDL compile package

Posted on 2002-07-18
6
2,002 Views
Last Modified: 2007-12-19
Hi all,
I'm using Forms 4.5, Oracle7 Server Release 7.3.3.0.0.
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:
FORMS_DDL('@c:\temp\newVers.sql');
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
0
Comment
Question by:horstborscht
[X]
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
6 Comments
 
LVL 27

Expert Comment

by:kretzschmar
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 ;-)

0
 
LVL 5

Expert Comment

by:ser6398
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.

0
 
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Expert Comment

by:konektor
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.
0
 
LVL 6

Accepted Solution

by:
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');
do
forms_ddl('create or replace view ('||
'curr_date)'||
'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:
DECLARE
  AppID   PLS_INTEGER;
BEGIN
  AppID := DDE.App_Begin(my_ora_home||'\bin\plus33w.exe @c:\temp\newVers.sql',  DDE.APP_MODE_MINIMIZED);
END;

good luck.
0
 
LVL 3

Expert Comment

by:patelgokul
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

624 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