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

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 22 Jun 2001 08:37:34 -0700
Message-ID: <F001.00333B4D.20010622081101@fatcity.com>

Here is simple function, that I pu into dll (it's on NT):

extern "C" void __declspec(dllexport) os_cmd(char *cmd_string,
short cmd_string_indicator,
short cmd_string_length,

int e_mode,
short e_mode_indicator,
int *ret_val)
{
int exec_mode;

char *args[10];
char *l_buffer;
char *pdest;
char *pdest1;

int result;
int l_ch = ' '; /* TAB */
int i = 0;
int nI;

for (nI = 0; nI < 10; nI++)
args[nI] = NULL;
l_buffer = (char *)calloc(cmd_string_length + 1, sizeof(char)); strncpy(l_buffer, cmd_string, cmd_string_length); l_buffer[cmd_string_length] = '\0';
pdest1 = l_buffer;
while ((pdest = strchr(pdest1, l_ch)) != NULL) {
result = pdest - pdest1;
args[i] = (char *)calloc(result + 1, sizeof(char)); strncpy(args[i++], pdest1, result);
pdest1 = pdest + 1;
}
args[i] = (char *)calloc(strlen(pdest1) + 1, sizeof(char)); strncpy(args[i++], pdest1, strlen(pdest1)); args[i] = NULL;

if (e_mode == SyncMode)
exec_mode = _P_WAIT;
else
exec_mode = _P_NOWAIT;
if (_spawnv(exec_mode, args[0], args) == -1) {
if (errno == E2BIG)
*ret_val = 1;
else if (errno == EINVAL)
*ret_val = 2;
else if (errno == ENOENT)
*ret_val = 3;
else if (errno == ENOEXEC)
*ret_val = 4;
else if (errno == ENOMEM)
*ret_val = 5;
else
*ret_val = 6;
}
else
*ret_val = 0;

for (nI = 0; nI <= i-1; nI++) 
free(args[nI]);
free(l_buffer);

}

and PL/SQL wrapper:

--  Executes OS command
-- 'p-mode': 0 - sync, 1 - async.
-- If there are parameters to pass, they should be separated by TABs.
-- To run internal DOS command use 'cmd /C <your_command>'

PROCEDURE exec_os_cmd(
cmd_string IN varchar2,
e_mode IN BINARY_INTEGER,
ret_val OUT BINARY_INTEGER) IS
external
library externDDM
NAME "os_cmd"
LANGUAGE C
PARAMETERS (cmd_string STRING,
cmd_string INDICATOR,
cmd_string LENGTH,
e_mode INT,
e_mode INDICATOR,
ret_val INT);

'externDDM' is the name of the library I create:

create library externDDM as
'D:\ORANT\BIN\ext_ddm.dll';

Please read oracle docs for more details.

Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627<A href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com

Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Nirmal Kumar Muthu Kumaran To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

Sent: Friday, June 22, 2001 10:31
AM
Subject: RE: How to do a bitwise OR from SQL*Plus

Hi neyman,
Thank god, i had a requirement to transfer data files from ftp server to oracle directory, and then am processing it...

Can you tell me method, how to write the external stored procedures and call from pl/sql. thanks in advance.
 Nirmal Qatar Telecom <A
 href="mailto:nirmalk_at_qtel.com.qa">nirmalk_at_qtel.com.qa

-----Original Message----- From: Igor Neyman
 [SMTP:ineyman_at_perceptron.com] Sent: Friday, June 22, 2001 4:30 PM To: <FONT

    size=1>Multiple recipients of list ORACLE-L <FONT 
    size=1>Subject:        <FONT 
    size=1>Re: How to do a bitwise OR from SQL*Plus

I am using external stored procedures (C/C++), mostly to execute OS commands from PL/SQL code. Pretty straightforward (following docs), works fine. Should be accurate mapping C-types to PL/SQL types.
 Igor Neyman, OCP DBA Perceptron,
 Inc. (734)414-4627 ineyman_at_perceptron.com

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, June 22, 2001 6:05 AM > 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: <A target=_blank href="http://www.orafaq.com">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: <A target=_blank href="http://www.orafaq.com">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: <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com -- Author: Igor Neyman INET: ineyman_at_perceptron.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).

Received on Fri Jun 22 2001 - 10:37:34 CDT