RE: How to do a bitwise OR from SQL*Plus

There is a some good info on working with external procedures out on metalink and also on this orafaq document.

Here is the jist of it:

/*--------------------------------------------------------------------

• extproc.c
  *
  
• Call operating system commands from PL/SQL using the External
• Procedure Interface.
  *
  
• Frank Naude - Dec 2000
  *--------------------------------------------------------------------
  
• Setup instructions:
  *
  
• 1. Compile this program: cc -G extproc.c -o extproc.so (on Unix)
• 2. Run $ORACLE_HOME/bin/extproc to ensure it is executable
• 3. Define this TNSNAMES.ORA entry (Use the correct domain):
• EXTPROC_CONNECTION_DATA.WORLD = (DESCRIPTION =
• (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
• (CONNECT_DATA=(SID=extproc)))
• 4. Define this LISTENER.ORA entry:
• EXTERNAL_PROCEDURE_LISTENER =
• (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
• SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
• (SID_LIST=(SID_DESC=(SID_NAME=extproc)
• (ORACLE_HOME=/app/oracle/product.8.1.7)(PROGRAM=extproc)))
• 5. Start the new listener: lsnrctl start EXTERNAL_PROCEDURE_LISTENER
• 6. SQL> create library shell_lib as '/app/oracle/local/extproc.so';
• /
• 7. SQL> create or replace function sysrun (syscomm in varchar2)
• return binary_integer
• as language C -- Use "as external" for older Oracle releases
• name "sysrun"
• library shell_lib
• parameters(syscomm string);
• /
• 8. Execute an OS command from PL/SQL:
• PL/SQL> declare
• rc number;
• begin
• rc := sysrun('/bin/ls -l');
• dbms_output.put_line('Return Code='||rc);
• end;
• /
  *
  *--------------------------------------------------------------------
  
• Notes:
  *
  
• 1. When running shell-scripts, very few environment variables will be
• defined (as with cron jobs). Remember to set everything
• explicitly. Ie. $PATH, etc.
• 2. Rewrite this program using C Piping if you need to capture command
• output. Look at the popen (pipe open) function.
• 3. In addition to this, you can also try to make the external
• procedure example as provided by Oracle:
• $ cd $ORACLE_HOME/plsql/demo
• $ make -f demo_plsql.mk extproc.so
  *
  *--------------------------------------------------------------------
  */
  

int sysrun(char *command)
{

   return system(command);
}

-----Original Message-----
Sent: Friday, June 22, 2001 4:05 AM
To: Multiple recipients of list ORACLE-L

Hi

Would external procedures be a way of doing this if bitand didn't exist ? Just that there is a lot of very cool C code out there.

Have RTFM, but I'm a C compiler dunce and cc turned out to stand for completely confused :-)

Has anyone managed to use external procedures ? Is it hard to do ? What's the stability ? And performance ?

Cheers
GS

-----Original Message-----
Sent: Thursday, 21 June 2001 16:01
To: Multiple recipients of list ORACLE-L

> why do you need to do a "bitwise or" within sqlplus?

Good question... I have a really good engineer who is working with C and Oracle OCI. He's developing an install routine and assures me that he needs bitwise operators from SQL because he can do some really powerful things with them. Then he said SQLServer and MySQL had them so the challenge was on. Turns out I was able to give him what he wanted with a little bit of help from something I found in a google search. There is an undocumented bitand function in oracle and it appears it must be called from another function. (Why?) You can see how this function is used in some of the data dictionary view creation scripts. Here's and example of bitand...

SQL> select sum(bitand(12,11)) bitand from dual;  

    BITAND

         8

If you can get a bitand you can do a bitor... SQL> select sum(12+11-bitand(12,11)) bitor from dual;  

     BITOR

        15

The math...
1100 "12"
1011 "11"
==== bitand
1000 "8"

1100 "12"
1011 "11"
==== bitor
1111 "15"

Not an alpha geek today,
Steve Orr

-----Original Message-----
Sent: Thursday, June 21, 2001 7:06 AM
To: Multiple recipients of list ORACLE-L

but, why do you need to do a "bitwise or" within sqlplus? just curious.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, June 20, 2001 5:51 PM
To: Multiple recipients of list ORACLE-L

OK, for the alpha geek award of the day...

Who can tell me how to do a "bitwise or" from SQLPlus -- NOT PL/SQL? Isn't there an internal undocumented bitand function and how could you use that to implement a bitor function from SQL?

Steve Orr

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Orr, Steve
  INET: sorr_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing)...
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Solomon
  INET: greg.solomon_at_betfair.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kempf, Reed
  INET: rkempf_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).