PL/SQL Example: Bitwise Operations using External Procedures Overview -------- There is no official support for Bitwise operators in Oracle PL/SQL or SQL. An enhancement request has been submitted to introduce this functionality, but there is no plan to implement it within the Oracle 8i time frame. BITAND is an undocumented PL/SQL function provided for use in internal packages by Oracle and is present from version 2.0 onwards The UTL_RAW package contains bitwise operations, but these are for use with RAW datatypes. This article contains information on how to perform bitwise operations in PL/SQL using the external procedures interface from Oracle8. Note: These operations are subject to limitations of the platform the external procedure is running on. These limitations include the largest size of the number to perform bitwise operations on. Some platforms limit the size to a 2-byte number. Program Notes ------------- When setting up the code: o ensure that the Listener is configured to handle external procedures. o compile the bitutil sharable object. o and compile the bitutil SQL script (changing library path for library). A test script is included to should how to use the code from both PL/SQL and SQL. Caution ------- The sample program in this article is provided for educational purposes only. It is NOT supported by Oracle Support Services, however, it has been tested and appears to work as documented. Be sure to test this code in your environment before relying on it. Example Code ------------ /* * bitutil.c * * bitwise operations for external procedures (PL/SQL) * * to build: * copy $ORACLE_HOME/rdbms/demo/demo_rdbms.mk into directory and run: * make -f demo_rdbms.mk extproc_nocallback SHARED_LIBNAME=bitutil.so \ * OBJS=bitutil.o */ int bitand( int num1, int num2 ) { return ( int ) ( num1 & num2 ); } /* end bitand( ) */ int bitor( int num1, int num2 ) { return ( int ) ( num1 | num2 ); } /* end bitor( ) */ int bitxor( int num1, int num2 ) { return ( int ) ( num1 ^ num2 ); } /* end bitxor( ) */ int bitcomp( int num1 ) { return ( int ) ( ~num1 ); } /* end bitcomp( ) */ int bitlshift( int num1, int places ) { return ( int ) ( num1 << places ); } /* end bitlshift( ) */ int bitrshift( int num1, int places ) { return ( int ) ( num1 >> places ); } /* end bitrshift( ) */ =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= REM bitutil.sql CREATE LIBRARY bitutillib AS '/apps/extproc/lib/bitutil.so'; / CREATE OR REPLACE PACKAGE bitutil AS -- package bitutil -- contains bitwise functions FUNCTION bitand( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitand, WNDS, WNPS, RNDS, RNPS ); -- bitand(): returns the bitwise and of num1 and num2 FUNCTION bitor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitor, WNDS, WNPS, RNDS, RNPS ); -- bitor(): returns the bitwise or of num1 and num2 FUNCTION bitxor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitxor, WNDS, WNPS, RNDS, RNPS ); -- bitxor(): returns the bitwise exclusive or of num1 and num2 FUNCTION bitcomp( num1 IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitcomp, WNDS, WNPS, RNDS, RNPS ); -- bitcomp(): returns the bitwise compilment of num1 FUNCTION bitlshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitlshift, WNDS, WNPS, RNDS, RNPS ); -- bitlshift(): returns num1 shifted places to the left FUNCTION bitrshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER ) RETURN BINARY_INTEGER; PRAGMA RESTRICT_REFERENCES( bitrshift, WNDS, WNPS, RNDS, RNPS ); -- bitrshift(): returns num2 shifted places to the right END; / CREATE OR REPLACE PACKAGE BODY bitutil AS FUNCTION bitand( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitand"; FUNCTION bitor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitor"; FUNCTION bitxor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitxor"; FUNCTION bitcomp( num1 IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitcomp"; FUNCTION bitlshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitlshift"; FUNCTION bitrshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER ) RETURN BINARY_INTEGER AS EXTERNAL LIBRARY bitutillib NAME "bitrshift"; END; / =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= rem test bitutil package set serveroutput on exec dbms_output.put_line( 'result:= ' || bitutil.bitand( 1, 2 )); exec dbms_output.put_line( 'result:= ' || bitutil.bitor( 1, 2 )); exec dbms_output.put_line( 'result:= ' || bitutil.bitxor( 1, 2 )); exec dbms_output.put_line( 'result:= ' || bitutil.bitcomp( 1 )); exec dbms_output.put_line( 'result:= ' || bitutil.bitlshift( 1, 2 )); exec dbms_output.put_line( 'result:= ' || bitutil.bitrshift( 4, 2 )); select bitutil.bitor( 4, 8 ) from dual; Problem Description ------------------- You are looking for bitwise functions that can be used in PL/SQL, such as BITOR or BITXOR (exclusive or). However, the following error is received when referencing these functions in a query: ORA-00904 invalid column name
Solution Description -------------------- There is no official support for bitwise operators in Oracle PL/SQL or SQL. An enhancement request was submitted to introduce this functionality, but there is no plan to implement it within the Oracle 8i time frame. BITAND is an undocumented PL/SQL function provided for use in internal packages by Oracle and is present from version 2.0 onwards The following bitwise functions are available in PL/SQL package UTL_RAW. These functions, however, are provided for manipulating RAW datatypes. ?BIT_AND ?BIT_OR ?BIT_XOR ?BIT_COMPLEMENT These functions are defined as follows: BIT_AND: UTL_RAW.BIT_AND ( r1 IN RAW, r2 IN RAW) RETURN RAW; This function performs bitwise logical 'and' of the values in RAW r1 with RAW r2 and returns the 'anded' result RAW. If r1 and r2 differ in length, then the 'and' operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result. The result length equals the longer of the two input RAWs. BIT_OR: UTL_RAW.BIT_OR ( r1 IN RAW, r2 IN RAW) RETURN RAW; This function performs bitwise logical 'or' of the values in RAW r1 with RAW r2 and returns the 'or'd' result RAW. If r1 and r2 differ in length, then the 'or' operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result. The result length equals the longer of the two input RAWs. BIT_XOR: UTL_RAW.BIT_XOR ( r1 IN RAW, r2 IN RAW) RETURN RAW; This function performs bitwise logical 'exclusive or' of the values in RAW r1 with RAW r2 and returns the 'xor'd' result RAW. If r1 and r2 differ in length, then the 'xor' operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result. The result length equals the longer of the two input RAWs. BIT_COMPLEMENT: UTL_RAW.BIT_COMPLEMENT ( r IN RAW) RETURN RAW; This function performs bitwise logical 'complement' of the values in RAW r and returns the 'complement'ed' result RAW. The result length equals the input RAW r length.
Overview
-------- There is no official support for Bitwise operators in Oracle PL/SQL or SQL.
An enhancement request has been submitted to introduce this functionality,
but there is no plan to implement it within the Oracle 8i time frame. BITAND is an undocumented PL/SQL function provided for use in internal
packages by Oracle and is present from version 2.0 onwards The UTL_RAW package contains bitwise operations, but these are for use
with RAW datatypes. This article contains information on how to perform
bitwise operations in PL/SQL using the external procedures interface from
Oracle8. Note: These operations are subject to limitations of the platform the
external procedure is running on. These limitations include the
largest size of the number to perform bitwise operations on. Some
platforms limit the size to a 2-byte number.
Program Notes
------------- When setting up the code: o ensure that the Listener is configured to handle external procedures.
o compile the bitutil sharable object.
o and compile the bitutil SQL script (changing library path for library). A test script is included to should how to use the code from both PL/SQL and
SQL.
Caution
------- The sample program in this article is provided for educational purposes only.
It is NOT supported by Oracle Support Services, however, it has been tested
and appears to work as documented. Be sure to test this code in your
environment before relying on it.
Example Code
------------ /*
* bitutil.c
*
* bitwise operations for external procedures (PL/SQL)
*
* to build:
* copy $ORACLE_HOME/rdbms/demo/demo_rdbms.mk into directory and run:
* make -f demo_rdbms.mk extproc_nocallback SHARED_LIBNAME=bitutil.so \
* OBJS=bitutil.o
*/ int bitand( int num1, int num2 )
{
return ( int ) ( num1 & num2 );
} /* end bitand( ) */ int bitor( int num1, int num2 )
{
return ( int ) ( num1 | num2 );
} /* end bitor( ) */ int bitxor( int num1, int num2 )
{
return ( int ) ( num1 ^ num2 );
} /* end bitxor( ) */ int bitcomp( int num1 )
{
return ( int ) ( ~num1 );
} /* end bitcomp( ) */ int bitlshift( int num1, int places )
{
return ( int ) ( num1 << places );
} /* end bitlshift( ) */ int bitrshift( int num1, int places )
{
return ( int ) ( num1 >> places );
} /* end bitrshift( ) */ =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= REM bitutil.sql CREATE LIBRARY bitutillib AS '/apps/extproc/lib/bitutil.so';
/ CREATE OR REPLACE PACKAGE bitutil AS
-- package bitutil
-- contains bitwise functions FUNCTION bitand( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitand, WNDS, WNPS, RNDS, RNPS );
-- bitand(): returns the bitwise and of num1 and num2 FUNCTION bitor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitor, WNDS, WNPS, RNDS, RNPS );
-- bitor(): returns the bitwise or of num1 and num2 FUNCTION bitxor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitxor, WNDS, WNPS, RNDS, RNPS );
-- bitxor(): returns the bitwise exclusive or of num1 and num2 FUNCTION bitcomp( num1 IN BINARY_INTEGER ) RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitcomp, WNDS, WNPS, RNDS, RNPS );
-- bitcomp(): returns the bitwise compilment of num1 FUNCTION bitlshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER )
RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitlshift, WNDS, WNPS, RNDS, RNPS );
-- bitlshift(): returns num1 shifted places to the left FUNCTION bitrshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER )
RETURN BINARY_INTEGER;
PRAGMA RESTRICT_REFERENCES( bitrshift, WNDS, WNPS, RNDS, RNPS );
-- bitrshift(): returns num2 shifted places to the right
END;
/ CREATE OR REPLACE PACKAGE BODY bitutil AS
FUNCTION bitand( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitand"; FUNCTION bitor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitor"; FUNCTION bitxor( num1 IN BINARY_INTEGER, num2 IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitxor"; FUNCTION bitcomp( num1 IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitcomp"; FUNCTION bitlshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitlshift"; FUNCTION bitrshift( num1 IN BINARY_INTEGER, places IN BINARY_INTEGER )
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY bitutillib
NAME "bitrshift";
END;
/ =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*= rem test bitutil package set serveroutput on exec dbms_output.put_line( 'result:= ' || bitutil.bitand( 1, 2 ));
exec dbms_output.put_line( 'result:= ' || bitutil.bitor( 1, 2 ));
exec dbms_output.put_line( 'result:= ' || bitutil.bitxor( 1, 2 ));
exec dbms_output.put_line( 'result:= ' || bitutil.bitcomp( 1 ));
exec dbms_output.put_line( 'result:= ' || bitutil.bitlshift( 1, 2 ));
exec dbms_output.put_line( 'result:= ' || bitutil.bitrshift( 4, 2 ));
select bitutil.bitor( 4, 8 ) from dual;
Problem Description
------------------- You are looking for bitwise functions that can be used in PL/SQL, such as
BITOR or BITXOR (exclusive or). However, the following error is received
when referencing these functions in a query: ORA-00904 invalid column name
-------------------- There is no official support for bitwise operators in Oracle PL/SQL or SQL.
An enhancement request was submitted to introduce this functionality, but
there is no plan to implement it within the Oracle 8i time frame. BITAND is an undocumented PL/SQL function provided for use in internal
packages by Oracle and is present from version 2.0 onwards The following bitwise functions are available in PL/SQL package UTL_RAW.
These functions, however, are provided for manipulating RAW datatypes. ?BIT_AND
?BIT_OR
?BIT_XOR
?BIT_COMPLEMENT These functions are defined as follows: BIT_AND: UTL_RAW.BIT_AND (
r1 IN RAW,
r2 IN RAW)
RETURN RAW; This function performs bitwise logical 'and' of the values in RAW r1 with
RAW r2 and returns the 'anded' result RAW. If r1 and r2 differ in length, then the 'and' operation is terminated after
the last byte of the shorter of the two RAWs, and the unprocessed portion of
the longer RAW is appended to the partial result. The result length equals
the longer of the two input RAWs. BIT_OR: UTL_RAW.BIT_OR (
r1 IN RAW,
r2 IN RAW)
RETURN RAW; This function performs bitwise logical 'or' of the values in RAW r1 with
RAW r2 and returns the 'or'd' result RAW. If r1 and r2 differ in length, then the 'or' operation is terminated after
the last byte of the shorter of the two RAWs, and the unprocessed portion of
the longer RAW is appended to the partial result. The result length equals
the longer of the two input RAWs. BIT_XOR: UTL_RAW.BIT_XOR (
r1 IN RAW,
r2 IN RAW)
RETURN RAW; This function performs bitwise logical 'exclusive or' of the values in
RAW r1 with RAW r2 and returns the 'xor'd' result RAW. If r1 and r2 differ in length, then the 'xor' operation is terminated after
the last byte of the shorter of the two RAWs, and the unprocessed portion of
the longer RAW is appended to the partial result. The result length equals
the longer of the two input RAWs. BIT_COMPLEMENT: UTL_RAW.BIT_COMPLEMENT (
r IN RAW)
RETURN RAW; This function performs bitwise logical 'complement' of the values in RAW r
and returns the 'complement'ed' result RAW. The result length equals the
input RAW r length.