Solved

Forms_DDL compile package

Posted on 2002-07-18
6
1,889 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
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 34

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

 
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

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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

16 Experts available now in Live!

Get 1:1 Help Now