Geek asks and answers

How to execute a PL / SQL package from powershell?

I am trying to write a powershell script to help with some user maintenance tasks in multiple databases.

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

Here, essentially, is what I'm trying to execute in pseudo-code, but I don't 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
}
+3
source share
2 answers

PowerShell . . 2 1 1 2.

, -

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

user password Database1 database1_tns_name TNS Database1 Database2 tnsnames.ora. (, Database1, Database2 tnsnames.ora.)

PL/SQL, 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;
+2

, PowerShell SQLPLUS SQL script, ?

- :

  • SQL Plus DB 1, .
  • , SQL plus DB2.
  • , , SQL plus .

SQLPlus, , PowerShell .NET, Database Connection , .NET-.

0

More articles:


All Articles