How to execute a PL/SQL package from powershell?

Question

I'm trying to write a powershell script to help with some user maintenance tasks across multiple databases.

• Database 1 contains my user ID and user type (ex: employee, subcontractor, etc...)
• Database 2 contains a db with many PL/SQL packages and procedures I can execute to update information in this db.

Here essentially what I am trying to accomplish in pseudo-code but I do not have much experience with PL/SQL packages and procedures especially from powershell.

Is it possible to execute the wwsec_api.set_defaultgroup procedure from powershell?

SELECT userID FROM Database2.users

while (userID){
    SELECT employeeType FROM Database1.users WHERE Database2.users.userID = Database1.users.userID

    If employeeType = 'Employee' then
        run Database2 PL/SQL package procedure wwsec_api.set_defaultgroup(ID, USER)
    end if
}

Answer 1

I'd be tempted not to use PowerShell here. Instead, I'd use a database link. The database link would connect Database2 to Database1, and allow me to pull data from Database1 across to Database2.

You create a database link using something like

CREATE DATABASE LINK Database1 CONNECT TO user IDENTIFIED BY password USING 'database1_tns_name';

Replace the user and password with the username and password of the schema owner on Database1, and database1_tns_name with the TNS name of Database1 in Database2's tnsnames.ora file. (You might need to create an entry for Database1 if it doesn't already exist in Database2's tnsnames.ora file.)

Then, I'd run a PL/SQL block similar to the following on Database2:

DECLARE
  v_employeeType     users.employeeType@Database1%TYPE;
BEGIN
  FOR user_rec IN (SELECT userID FROM users)
  LOOP
    SELECT employeeType INTO v_employeeType
      FROM users@Database1
     WHERE userID = user_rec.userID;

    IF v_employeeType = 'Employee' THEN
      -- Check the parameters of this procedure - where does ID come from,
      -- and do you really want to use the built-in function USER, which
      -- returns the name of the currently-connected user?
      wwsec_api.set_defaultgroup(ID, USER);
    END IF;
  END LOOP;
END;