ADG_REDIRECT_DML in Oracle Active Data Guard 19c

on
0

External procedures: How to run OS commands from the database (SQL*Plus) and result store in the database

on
This solution works for all versions of the Oracle database (8i/9i/10g/11g)

OS part
Database part
How to read result and save it to the database

OS part

1. already compiled shell.c for specified OS

/*
 * shell.c
 * Example program used to demonstrate how to call O/S
 * commands from PL/SQL using external procedures. 
 *
 */

#include
#include
#include

void sh(char *command) {

  int num;

  num = system(command);

}

void shell(char *command, char buff[4000]) {
//char buff[4000];
FILE *fp = popen(command, "r" );
while ( fgets( buff, sizeof buff, fp ) != NULL ) {
 // do stuff with lines
 printf("%s\n", buff);
}
pclose( fp );
}

How to compile shell.c
Using the table below, first run the appropriate command to compile the shell.c program then run the command to generate the shared object.
Operating System extproc bit version 1st: Compile the shell.c program 2nd: Generate the shared object
Solaris SPARC extproc (64 bit) gcc -G -c -m64 shell.c ld -r -o shell.so shell.o
Solaris SPARC* extproc32 (32 bit) gcc -G -c shell.c ld -r -o shell.so shell.o
Linux RH AS3 32 bit extproc (32 bit) gcc -c shell.c ld -shared -o shell.so shell.o
Linux RH AS4 x86-64 extproc (64 bit) gcc -fPIC -c shell.c ld -shared -o shell.so shell.o
Linux RH AS4 x86-64* extproc32 (32 bit) gcc -m32 -c shell.c ld -shared -melf_i386 -o shell.so shell.o
AIX 5L extproc (64 bit) gcc -maix64 -c shell.c gcc -maix64 -shared -o shell.so shell.o
AIX 5L* extproc32 (32 bit) gcc -c shell.c gcc -shared -o shell.so shell.o
HPUX 11.11** extproc32 (32 bit) gcc -c shell.c gcc -shared -o shell.sl shell.o
HPUX 11.11 extproc (64 bit) /usr/local/pa64/bin/gcc -c shell.c /usr/local/pa64/bin/gcc -shared -o shell.sl shell.o

* Add $ORACLE_HOME/lib32 to LD_LIBRARY_PATH, restart the listener and oracle
** Add $ORACLE_HOME/lib32 to SHLIB_PATH, restart the listener and oracle

2. Change permissions of shell.so and copy the file to $ORACLE_HOME/lib

Copying this file will bypass the need for adding ENVS and EXTPROC_DLLS parameters to the listener.ora file. Remember, tweaking can be done later; for now, we want the configuration to be as simple as possible.
chmod 755 shell.so cp shell.so $ORACLE_HOME/lib

Or there is also possibility to configure listener.ora: 
 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host_name>)(PORT = <port>))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) --> this must be also included to the definition of listener
    )
  )

 SID_LIST_LISTENER=              
  (SID_LIST=
   (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = <ORACLE_HOME>) --> correct $ORACLE_HOME
    (PROGRAM = extproc)
    (ENVS="EXTPROC_DLLS=ONLY:<directory>/shell.so")  --> exactly specified destination to the file
   )
  )

Changes in tnsnames.ora: 
 extproc_connection_data.world=
 (description=
 (address=(protocol=ipc)(key=EXTPROC))
 (connect_data=(sid=PLSExtProc)))

Attention: listener and tnsnames configuration is case sensitive

3. Test configuration for OS part

Use tnsping to test the extproc configuration.  This step has to succeed. 
  host$ tnsping extproc_connection_data
 
  TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 12-APR-2012 21:21:09
                                
  Copyright (c) 1997,  2007, Oracle.  All rights reserved.
                                 
  Used parameter files:
  /.../sqlnet.ora
                                
                                
  Used TNSNAMES adapter to resolve the alias
  Attempting to contact (description= (address=(protocol=ipc)(key=EXTPROC)) (connect_data=(sid=PLSExtProc)))
  OK (0 msec)


Database part

1. Create a LIBRARY definition in Oracle 
  CREATE LIBRARY shell_lib is '<path to shell.so>/shell.so';
  /

2. Create the PL/SQL procedure

  CREATE OR REPLACE PROCEDURE shell(command IN char)
  AS EXTERNAL
  NAME "sh"
  LIBRARY shell_lib
  LANGUAGE C
  PARAMETERS (command string);
  /

3. Test the stored procedure

  exec shell('ls');

  exec shell('ls>output.txt');


How to read result and save it to the database

Create database directory, where will be result of the OS command from the database

  create or replace directory TEST_DIR as '<directory_name>'; --> specify directory name

  grant write on directory TEST_DIR to <user_name>;
  grant read on directory TEST_DIR to <user_name>;
  grant execute on UTL_FILE to <user_name>;
  grant create procedure to <user_name>;

Create table for storing result

 create table test (name varchar2(15), content varchar2(4000));

Create the PL/SQL procedure for reading and update the table

CREATE OR REPLACE PROCEDURE show_file (loc IN VARCHAR2,file IN VARCHAR2)
 IS
 fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'R');
 line VARCHAR2(4000);
 BEGIN
  DBMS_OUTPUT.PUT_LINE (file);
  insert into test (name, content) values (file,'');
  commit;
  LOOP
  UTL_FILE.GET_LINE (fid, line);
  update test set content = content ||'|'|| line where name=file;                 
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
 END;
/

How to use it

 exec shell('ls /tmp > /tmp/test');
 exec show_file ('TEST_DIR','test');

 select * from test;




Comments

Post a Comment