Re: DBMS_PIPE handles nulls?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 14 Aug 1998 18:35:43 GMT
Message-ID: <35e88161.193109657@192.86.155.100>

A copy of this was sent to jboes_at_my-dejanews.com (if that email address didn't require changing) On Thu, 13 Aug 1998 20:46:20 GMT, you wrote:

>How does the DBMS_PIPE package handle null values?
>
>I'm sending fields to a process which reads from the pipe. My receiving code
>looks like this:
>
> DBMS_PIPE.UNPACK_MESSAGE(:msgField INDICATOR :msgField_i);
>
>(I have no idea if the INDICATOR syntax has any impact here, but it is
>syntactically correct.)
>
>When I unpack a NULL from the message, I get
>
> ORA-01405: fetched column value is NULL
>
>This seems to happen even though the UNPACK_MESSAGE call is bounded by a block
>with an exception handler.

It is useful to post things like

- db version
- platform
- sqlnet (if being used) 
- etc....

But anyway... It works, try a proc program that looks like this (tested in 7.3.4 and 8.0.3)

#include <stdio.h>
#include <string.h>

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");     printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);     EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

void process( void )
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     msgField[255];
    short       msgField_i;

EXEC SQL END DECLARE SECTION;     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    msgField.len = 255;
    msgField_i = 1;

    EXEC SQL EXECUTE
    BEGIN

        if ( dbms_pipe.receive_message( 'TestingPipe' ) = 0 )
        then
            dbms_pipe.unpack_message( :msgField indicator :msgField_i );
        end if;

    END;
    END-EXEC;     printf( "msgField_i = %d\n", msgField_i );

    if ( !msgField_i ) printf( "%.*s\n", msgField.len, msgField.arr ); }

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50] = { strlen( "tkyte/tkyte" ), "tkyte/tkyte" }; EXEC SQL END DECLARE SECTION;     EXEC SQL CONNECT :oracleid;

    process();
    process();
    process();

    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

(change tkyte/tkyte to your user/pass of course)... then, in another window run:

begin

    dbms_pipe.pack_message( 'Hello World' );     if ( dbms_pipe.send_message( 'TestingPipe' ) <> 0 ) then

        raise_application_error( -20000, 'error on pipe' );     end if;
    dbms_pipe.pack_message( to_char(NULL) );     if ( dbms_pipe.send_message( 'TestingPipe' ) <> 0 ) then

        raise_application_error( -20000, 'error on pipe' );     end if;
    dbms_pipe.pack_message( rpad( '*', 2000, '*' ) );     if ( dbms_pipe.send_message( 'TestingPipe' ) <> 0 ) then

        raise_application_error( -20000, 'error on pipe' );     end if;
end;
/

in sqlplus. The expected output from the proc program will be:

msgField_i = 0
Hello World
msgField_i = -1

ORACLE error detected:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.DBMS_PIPE

the lines:

msgField_i = 0
Hello World

come from the successful read of Hello World the first time. The line:

msgField_i = -1

indicates the second call, which read a NULL, succeeded and was able to tell our program that it was null (no Oracle Error thrown).

then, just to show that errors do get thrown and caught, the next 3 lines report an error when we send more data then we are prepared to receive..  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 14 1998 - 13:35:43 CDT