Re: How to execute a sql script in PL/SQL procedure ?

From: cava123 <cava123_at_noos.fr>
Date: Sat, 22 Jun 2002 13:11:08 +0200
Message-ID: <3D145B4C.1B61FAD5@noos.fr>

Try to do this steps :

1. Listener.ora :

   ---

LISTENER_DBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =
test_oracle)(PORT = 1521))
  )
SID_LIST_LISTENER_EXTPROC =
  (SID_LIST =
    (SID_DESC =
(PROGRAM = extproc)
(SID_NAME = extproc)
(ORACLE_HOME = /logiciel/oracle_817)
    )
  )
SID_LIST_LISTENER_DBTEST =
  (SID_LIST =
    (SID_DESC =
(GLOBAL_DBNAME = dbtest.world)
(ORACLE_HOME = /logiciel/oracle_817)
(SID_NAME = dbtest)

    )
  )
LISTENER_EXTPROC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))   )

2) tnsnames.ora :

---

DBTEST.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
test_oracle)(PORT = 1521))

    )
    (CONNECT_DATA =
(SID = dbtest)

    )
  )
EXTPROC_CONNECTION_DATA.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
    )
    (CONNECT_DATA =
(SID = extproc)

    )
  )

ATTENTION : l'alias doit necessairement se nommer EXTPROC_CONNECTION_DATA (plus .WORLD si c'est le default domain)

3) Privileges :

---

pour executer le script @?/plsql/demo/dbgextp.sql qui permet de debugger
les procs externes, faire en tant que system :

SQL> grant create library to chris;

(optionnel SQL> grant create any directory to chris;)

puis :

SQL> conn chris
Enter password:
Connected.
SQL> @?/plsql/demo/dbgextp.sql

Essai avec un prog C :

---

test_oracle<dbtest>$ cat exec_shell.c

  #include "/usr/include/stdio.h"
  #include <stdlib.h>
  #include <string.h>

  void sh(char *);

  void sh( char *cmd )
  {

          int num;

          num = system(cmd);
  }

test_oracle<dbtest>$ gcc -G -c exec_shell.c
test_oracle<dbtest>$ ld -r -o exec_shell.so
exec_shell.o

4) Demarrer le listener

test_oracle<dbtest>$ lsnrctl start listener_extproc test_oracle<dbtest>$ ps -ef|grep extproc   oracle 28408 1 0 10:40:20 ? 0:00 /logiciel/oracle_817/bin/tnslsnr listener_extproc -inherit

faire sous chris :

SQL> exec debug_extproc.startup_extproc_agent

PL/SQL procedure successfully completed.

on a alors sous Unix :

test_oracle<dbtest>$ ps -ef|grep extproc   oracle 28485 1 1 11:07:33 ? 0:00 extprocextproc (LOCAL=NO)
  oracle 28408 1 0 10:40:20 ? 0:00 /logiciel/oracle_817/bin/tnslsnr listener_extproc -inherit

L'agent restera alors present jusqu'a la fin de la session, et est donc
associable a un debugger (par exemple dbx).

SQL> CREATE LIBRARY shell_lib is
'/logiciel/oracle_dbtest/scripts/exec_shell.so'; SQL> create or replace procedure shell(cmd IN char) as external

     name "sh"
     library shell_lib
     language C
     parameters (cmd string);

/
SQL> exec shell('touch
/logiciel/oracle_dbtest/scripts/essai_chris.touch');

PL/SQL procedure successfully completed.

et alors sous unix on a :

test_oracle<dbtest>$ ls -l
/logiciel/oracle_dbtest/scripts/essai_chris.touch -rw-r--r-- 1 oracle dba 0 May 29 14:08 /logiciel/oracle_dbtest/scripts/essai_chris.touch

Epicentre team A a écrit :

> Hi
> How to execute a sql script in PL/SQL procedure ?.
>
> Thanks

Received on Sat Jun 22 2002 - 06:11:08 CDT