Are there any native ways of connecting to an Oracle 9i database and executing an internal Oracle package without having to use LEI?
Using LEI is fine and dandy, however we want users to be able execute the command and they are not allowed access to the LEI environment direct due to risk and security policies.
The type of command line I’m wanting to run is something like “begin finance_public_utils.run_notification_process; end;”
Subject: Executing Oracle Package from Notes without using LEI
Short answer - you should probably talk to your Oracle folks and find out what options are supported on their end.
Shot in the dark - do you know if the Oracle SQL package can execute stored procedures? If so and your users have the SQL package installed, you can launch an OS level SQL script that will run the stored proc. This assumes that the script either doesn’t need parameters or that parameters can be passed into the script.
I do understand why your Oracle folks don’t want you mucking about via LEI or LCLSX - ours are the same. They’re comfortable letting me do data reads but won’t let me into the business system at all (even to run stored procs). When you talk to the db tables directly, outside of the Oracle application environment, you risk not updating all of the right stuff and playing havoc with the system (least that’s what they tells me…).
Best of luck
Doug
Subject: RE: Executing Oracle Package from Notes without using LEI
Hmmm! We consider LEI to be a coded environment and therefore require change controls to run anything outside of the norm. However with Oracle we can setup a specific username against the schema and allow users to connect to it and run commands, however we don’t really want to have to explain to users that have they have to run SQL Net to run the process.
Surely there is a way to pass a username/password (yes hardcoded) via LotusScript to connect to an Oracle instance (having the Oracle client installed is fine) to then run the approved Oracle package. The package will not have any direct access to the data for security reasons, but will have access to run another package that does.
It may sound a little backward, but it is what I would expect as to be normal on any environment i was working with!
Rule #1 - Never lets the users near anything that can destroy the world!
Rule #2 - Obey all the rules.
Subject: Executing Oracle Package from Notes without using LEI
So the users don’t have LEI / database access in notes but they have access to run stored procedures directly on your production Oracle environment? (sounds a bit backwards).
You can code this in lotuscript but each user would need the oracle client installed for connectivity to Oracle, unless you run a scheduled or web based agent, in that case, the server would need the oracle client installed. (or you might be able to use ODBC - yuk!)
LEI is setup at the database / key level in notes - I don’t fully understand your question - are you saying the users don’t have access to a particular database in notes that is using LEI?
Subject: Executing Oracle Package from Notes without using LEI
Check out the LC LSX which does not require LEI to run. A big hint is that you’ll want to create the agent to run on server so you do not have to setup an Oracle connection on every user client.
I will add an ODBC connection to oracle with a simple @DBLookup may be a possibility too. Never tried it though.