Oracle Forms PL/SQL

User_2V78R

(not sure if this is the right subforum to ask this)

I have a legacy Oracle Forms Developer program, which at some places in the PL/SQL code calls the HOST command to call SQLPlus and run an external *.sql file with SQL commands in it.

As in:

HOST('sqlplus username/[email protected] myfile.sql outputfile');

We're not happy with having the password on the command line like this. Is there another way we can execute the code in the 'myfile.sql' file? Can that code be embedded in the PL/SQL code somehow? Or is there a more direct way to run sqlplus?

cormaco

One way is to store the password in a wallet as described here:

https://oracle-base.com/articles/10g/secure-external-password-store-10gr2

Another way is to use DBMS_SCHEDULER to execute the SQL script:

28.3.1.8 Script Jobs

SQL script jobs use the SQL*Plus interpreter to run Scheduler jobs. Therefore, you can now use all SQL*Plus features, including query output formatting.

Michael Ferrante-Oracle

There are probably numerous ways to do it other than HOST. However, which "how" you choose might depend on specifically what your code does (or doesn't do).

For example, take a look in the Form Builder Help for the built-in "FORMS_DDL". Likely you can use a combination of this and TEXT_IO to read your current sql files then execute them. With this method the sql doesn't need to be hard coded in the form. One limitation with this option is that PLSQL variables are going to be limited to 32k characters. So your script would need to be 32k or smaller.

You could also use a far more 21st Century solution than Sql+, like SQLCL (SqlDeveloper Command Line tools) to process the script. Again, you can call it from Forms, but with SQLCL there's lots more flexibility and power. I believe you can create a wallet then retrieve the credentials from it. The details are beyond the scope of Forms nor am I an expert with it, but it should be do-able (I believe). With regard to using a wallet, I believe the similar can be done with SQL+

https://www.oracle.com/database/technologies/appdev/sqlcl.html

https://www.google.com/search?q=oracle+sqlcl+wallet

Hub Tijhuis

1. You can call sqlplus with no user connection (sqlplus /nolog myfile.sql outputfile) , but you will have to do a connect in myfile.sql . You will have a not happy password there, but if the HOST is a secured environment there will be less/no unwanted viewers. This works only with a fixed username.
2. You can create a script on HOST with 3 or 4 arguments and call that script in your HOST-command. In the script call sqlplus passing the arguments. If there is unwanted access to the HOST then probably the arguments (and thus the password) can be seen (for example linux : ps -ef)
3. Wether you can embed the functionality of myfile.sql in PL/SQL-code depends on the functionality within that sql. You will probably need the TEXT_IO functionality to create the outputfile on HOST. And maybe you can avoid that outputfile (where is it used for now?)