27 November 2011

External C Procedures In the Oracle Database


It is possible to use external library procedures within PL/SQL. If external procedures are written in Java language, it is very easy. You can create and compile a java source inside the database via DDL commands. But if you have to call some C procedures, you cannot do it as easy as Java sources. First, you have to create a C procedure, compile it with proper compiler, generate shared object (so) with necessary parameters and copy it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH). Up to now, the steps should be followed in the server side, not in the database. Then, database steps (DDL commands) should be executed: create a library object and a wrapper PL/SQL function to call external C procedure. For java sources, there is no need for creating library object.

Prior to Oracle 11G, external procedures need a special network configuration, extproc with IPC protocol. With 11G, you do not need to make this tns configuration in most situations.

I will demonstrate, how to create a simple C procedure to get value of environment variable value and execute and get response of system commands with PL/SQL functions. Please note that, you can do most of these requirements with java sources. Note also that, C procedures below are intended to be use in simulation purposes. You may need to add some more lines of codes for your reqs.


I did not write any C programs almost for nine years. I remember my old-school days and how difficult I understand the pointers concepts in C languageJ. At least ten times I read Chapter 5 (or 6) - Pointers from “C How to Program” (Deitel pub.) J….



1.       First make necessary Oracle network configuration. You can use netmgr tool or do it by handJ.
listener.ora before change
==================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )

    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))
      (PROTOCOL_STACK =
         (PRESENTATION = HTTP)
         (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))
      (PROTOCOL_STACK =
         (PRESENTATION = FTP)
         (SESSION = RAW)
      )
    )
  )
listener.ora after change
==================================
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))
      (PROTOCOL_STACK =
        (PRESENTATION = HTTP)
        (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))
      (PROTOCOL_STACK =
        (PRESENTATION = FTP)
        (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
    )
  )

tnsnames.ora entry
==================================
EXTPROC_CONNECTION_DATA=           
 (DESCRIPTION=                    
   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))                     
   (CONNECT_DATA=
    (SID=extproc)))


2.       Create a C program (c_system_lib.c) that fulfills our needs.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

char* execute_command( char *inputCommandText )
{

  FILE *fp;
  char temp[10000];
  char commandReturnText[1000000];
  char *commandReturnPointer;
 
  memset(&commandReturnText[0], 0, sizeof(commandReturnText));
 
  fp = popen(inputCommandText, "r");
  if (fp == NULL) {
    commandReturnPointer = "ERROR:Unable to popen...";
  }
  else{
    while (fgets(temp, sizeof(temp)-1, fp) != NULL) {
      strcat (commandReturnText,temp);
    }
    pclose(fp);
  }
 
  commandReturnPointer = commandReturnText;
  return commandReturnPointer;
}

char* get_env_value( char *environmentValueName )
{
    char *environmentValue;
    environmentValue = getenv(environmentValueName);
    return environmentValue;
}


3.       Compile c_system_lib.c with proper compiler, generate shared object (so) with necessary parameters and copy it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH).  
[oracle@dhcppc5 admin]$ uname -a
Linux dhcppc5 2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@dhcppc5 sample_c_ext]$ gcc -c  c_system_lib.c
[oracle@dhcppc5 sample_c_ext]$ ld -shared -melf_i386 -o c_system_lib.so c_system_lib.o
[oracle@dhcppc5 sample_c_ext]$ chmod 755 c_system_lib.so
[oracle@dhcppc5 sample_c_ext]$ cp c_system_lib.so $ORACLE_HOME/lib


4.       Create a library object (c_system_lib) and two wrapper PL/SQL functions(ExecuteShellCommandViaC and GetEnvValueViaC) to call external C procedures:
CREATE LIBRARY c_system_lib is '/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib/c_system_lib.so';
/

CREATE OR REPLACE FUNCTION ExecuteShellCommandViaC(pis_CommandString IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "execute_command"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_CommandString string,RETURN string);
/

CREATE OR REPLACE FUNCTION GetEnvValueViaC(pis_EnvironmentVariableName IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "get_env_value"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_EnvironmentVariableName string,RETURN string);
/


5.       Test the wrapper functions:
select GetEnvValueViaC('ORACLE_HOME') from dual;

GETENVVALUEVIAC('ORACLE_HOME')
--------------------------------------------------------------------------------
/home/oracle/app/oracle/product/11.2.0/dbhome_2



select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;
EXECUTESHELLCOMMANDVIAC('/BIN/
--------------------------------------------------------------------------------
apex
apexlistener.sh
app
dav
deneme.txt
Desktop
emshutdown
emstartup
fix.sql
jdeveloper
listener
oracle
oracle_db11g_clr.gif
oradiag_oracle
otn_logo_small.gif
otn_new.css
repos
reset_apex
reset_imdbcache
reset.log
reset_OE.sql
reset_sqldev
reset_xdbPorts.sql
reset_xmldb
shrink.sh
shutdown
StartHere2010.htm
start_timesten
startup
status
stop_timesten
svn_repo
temp_dir
tmp





Please note that DBMS_SYSTEM.get_env procedure can also show envrionment variable values; but DBMS_SYSTEM package does not recommended for customer use. By default, there is no synonym for DBMS_SYSTEM supplied package.
DECLARE
  vs_OracleHome VARCHAR2(100);
BEGIN
  sys.dbms_system.get_env('ORACLE_HOME', vs_OracleHome);
  dbms_output.put_line(vs_OracleHome);
END;
/

26 November 2011

Unable to sftp “OTN Developer Days” Box - Received message too long


Today, I was not able to connect my “OTN Developer Days” Box via sftp interface. When I check it, profile file (the file which executes initially when you connect) was echoing some informational data. I think sftp interface has some problems with initially printed messagesJ. Then I comment some lines in the .bashrc and .bash_profile files in order to connect my RedHat box. I showed the solution as follows. Please note that I gave static IP address to my RH box.


connect sftp
====================
[root@dhcppc5 oracle]# sftp oracle@192.168.1.38
Connecting to 192.168.1.38...
The authenticity of host '192.168.1.38 (192.168.1.38)' can't be established.
RSA key fingerprint is 07:15:bb:a2:a6:ba:60:3f:c3:31:a9:c9:4a:7c:51:6a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.38' (RSA) to the list of known hosts.
oracle@192.168.1.38's password:
Received message too long 1097624608





connect via ssh before any changes
======================================================
Last login: Tue Nov 22 22:57:06 2011 from 192.168.1.35
All passwords are oracle or noted if otherwise.

The SQL Developer and Data Modeler scripts are in the directory:
/home/oracle/Desktop/SQLDev_Tutorials/sqldev

To Reset the labs:
SQL Developer:            /home/oracle/reset_sqldev
Application Express:      /home/oracle/reset_apex
In-Memory Database Cache: /home/oracle/reset_imdbcache
XML Database:             /home/oracle/reset_xmldb

Oracle Enterprise Manager
Start: emctl start dbconsole
Stop : emctl stop dbconsole
URL  : https://127.0.0.1:1158/em

*** Please note that this appliance is for testing purposes only,
as such it is unsupported and should not be used as a production environment.


          inet addr:192.168.1.38  Bcast:192.168.1.255  Mask:255.255.255.0
          inet addr:127.0.0.1  Mask:255.0.0.0

LD_LIBRARY_PATH set to /home/oracle/app/oracle/product/TimesTen/tt1121/lib:/home/oracle/app/oracle/product/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib

ANT_HOME set to /home/oracle/app/oracle/product/TimesTen/tt1121/3rdparty/ant

PATH set to /home/oracle/app/oracle/product/TimesTen/tt1121/bin:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/oci:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/odbc:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/odbc/xla:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/jdbc:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/odbc_drivermgr:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/proc:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/ttclasses:/home/oracle/app/oracle/product/TimesTen/tt1121/quickstart/sample_code/ttclasses/xla:/home/oracle/app/oracle/product/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/home/oracle/app/oracle/product/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk:/home/oracle/app/oracle/product/TimesTen/tt1121/3rdparty/ant/bin:/usr/java/latest/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin:/home/oracle/bin

CLASSPATH set to /home/oracle/app/oracle/product/TimesTen/tt1121/lib/ttjdbc6.jar:/home/oracle/app/oracle/product/TimesTen/tt1121/lib/orai18n.jar:/home/oracle/app/oracle/product/TimesTen/tt1121/lib/timestenjmsxla.jar:/home/oracle/app/oracle/product/TimesTen/tt1121/3rdparty/jms1.1/lib/jms.jar:.:/home/oracle/app/oracle/product/11.2.0/dbhome_2/ucp/lib/ucp.jar:/home/oracle/app/oracle/product/11.2.0/dbhome_2/jdbc/lib/ojdbc6.jar

TNS_ADMIN set to /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin

IP Address is:
          inet addr:192.168.1.38  Bcast:192.168.1.255  Mask:255.255.255.0
          inet addr:127.0.0.1  Mask:255.0.0.0
[oracle@dhcppc5 ~]$




change .bashrc
================
## following lines have been commented by mennan for ftp issue
# cat ~/Desktop/README.txt

# /sbin/ifconfig | grep "inet addr"

change .bash_profile
==================================
## following line has been commented by mennan for ftp issue
## please note that if you are using TimesTen in memory database, please do not comment and check contents of following file and remove any unnecessary _echoes_
# . $TT_HOME/bin/ttenv.sh


connect via ssh after changes
======================================================
Last login: Sat Nov 26 22:42:14 2011 from 192.168.1.35
IP Address is:
          inet addr:192.168.1.38  Bcast:192.168.1.255  Mask:255.255.255.0
          inet addr:127.0.0.1  Mask:255.0.0.0
[oracle@dhcppc5 ~]$


connect sftp after changes
========================================
[root@dhcppc5 oracle]# sftp oracle@192.168.1.38
Connecting to 192.168.1.38...
oracle@192.168.1.38's password:
sftp> exit

23 November 2011

Solution of "ORA-02303: cannot drop or replace a type with type or table dependents" and "ORA-14452: attempt to create, alter or drop an index on temporary table already in use"




Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as mennan

SQL> drop type ACTIVATION_TYP;

drop type ACTIVATION_TYP

ORA-02303: cannot drop or replace a type with type or table dependents

SQL> set serveroutput on;
SQL>
SQL> BEGIN
  2    dbms_utility.get_dependency(TYPE   => 'TYPE',
  3                                SCHEMA => 'MENNAN',
  4                                NAME   => 'ACTIVATION_TYP');
  5  END;
  6  /

-
DEPENDENCIES ON MENNAN.ACTIVATION_TYP
------------------------------------------------------------------
*TYPE MENNAN.ACTIVATION_TYP()
*   TYPE BODY MENNAN.ACTIVATION_TYP()
*   TABLE MENNAN.RLM$SESSRSLTTTAB_97272()

PL/SQL procedure successfully completed


SQL> drop table RLM$SESSRSLTTTAB_97272;

drop table RLM$SESSRSLTTTAB_97272

ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL>
SQL> select sid, serial# from v$session where sid in ( select sid from v$lock where id1 =
 ( select object_id from user_objects where object_name='RLM$SESSRSLTTTAB_97272'));---- find  sessions to be killed
SQL> alter system kill session '122,22';--kill sessions sid,serial
SQL> drop table RLM$SESSRSLTTTAB_97272;

Table dropped

SQL>
SQL> drop type ACTIVATION_TYP;

Type dropped

SQL>