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
}

Luke Woodward · Accepted Answer · 2011-04-01 18:48:05Z

2

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;

answered Apr 1, 2011 at 18:48

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ProfessionalAmateur Over a year ago

This approach would work but there are a few issues (maybe there is a way around them?) I need to be able to schedule it to run daily and also be able to run it at will. Additionally I need the ability to update the script/sql/program and our DBA's keep the db locked down (as they should) pretty good. I will definitely go this route if I cannot find a way to run this from my PC.

Jon Heller Over a year ago

@ProfessionalAmateur: You can create a procedure instead of an anonymous block. Then you can call it at any time from SQL*Plus, or you can schedule it with DBMS_SCHEDULER. This is exactly the kind of stuff PL/SQL is made for. If you can keep your code on the server it will probably be faster, easier to manage, and safer.

N West · Accepted Answer · 2011-04-01 18:33:35Z

0

What if you just have PowerShell do two system calls to SQLPLUS, and execute the SQL script that you generate from the output of the first call?

Something like:

Execute SQL Plus query against DB 1, return results into file.
Read file and for each user, execute SQL plus against DB2.
Compare result employee type, if it is an employee, then execute SQL plus again.

If you don't want to use SQLPlus, remember that PowerShell can use the .NET framework, so you could instantiate a couple Database Connection objects and execute your queries directly, returning the results into .NET objects.

answered Apr 1, 2011 at 18:33

N West

6,79428 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to execute a PL/SQL package from powershell?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related