Skip to Main Content

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Forms PL/SQL

User_2V78RJul 26 2021

(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/password@database 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?

Comments

cormaco

One way is to store the password in a wallet as described here:
Secure External Password Store (0 Bytes)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.

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

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

1 - 3

Post Details

Added on Jul 26 2021
3 comments
1,581 views