trim(' ') problem & ORA-03113

Whenever I execute TRIM(' ')in a stored procedure or trigger, I get an
ORA-03113 error.

I have an oracle db 8.1.7.0.1 Enterprise edition installed on a
linux-Mandrake 9.1.

EXEMPLE:
I created the function IS_NULL which returns 1 or 0 if the parameter is
empty or not:

CREATE OR REPLACE FUNCTION IS_NULL(v VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF TRIM(v) IS NULL THEN
RETURN 1;
ELSE
RETURN 1;
END IF;
END;


When excuting the following sql I get an ORA-03113 error

SQL> select is_null(' ') from dual;
select is_null(' ') from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


What's wrong with TRIM(' ') and how else can I check if a variable
consists of empty spaces without using TRIM?

Jan Bols

re: trim(' ') problem & ORA-03113

Jan Bols wrote:
[color=blue]
> Whenever I execute TRIM(' ')in a stored procedure or trigger, I get an
> ORA-03113 error.
>
> I have an oracle db 8.1.7.0.1 Enterprise edition installed on a
> linux-Mandrake 9.1.
>
> EXEMPLE:
> I created the function IS_NULL which returns 1 or 0 if the parameter is
> empty or not:
>
> CREATE OR REPLACE FUNCTION IS_NULL(v VARCHAR2)
> RETURN NUMBER
> IS
> BEGIN
> IF TRIM(v) IS NULL THEN
> RETURN 1;
> ELSE
> RETURN 1;
> END IF;
> END;
>
>
> When excuting the following sql I get an ORA-03113 error
>
> SQL> select is_null(' ') from dual;
> select is_null(' ') from dual
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> What's wrong with TRIM(' ') and how else can I check if a variable
> consists of empty spaces without using TRIM?
>
> Jan Bols
>[/color]

Not really any help, but I don't think the problem is the trim-function.
Your example works fine on my database. I'm running 9.2.0.1 on Windows
though.

Michael

re: trim(' ') problem & ORA-03113

Your code worked on my 8173 Oracle db too. Maybe your sqlplus session
had some problem. Try to open another new sqlplus session and see if
it solves your problem.

HTH.

Guang


Jan Bols <jan@ivpv.ugent.be> wrote in message news:<bkemli$pg6$1@gaudi2.UGent.be>...[color=blue]
> Whenever I execute TRIM(' ')in a stored procedure or trigger, I get an
> ORA-03113 error.
>
> I have an oracle db 8.1.7.0.1 Enterprise edition installed on a
> linux-Mandrake 9.1.
>
> EXEMPLE:
> I created the function IS_NULL which returns 1 or 0 if the parameter is
> empty or not:
>
> CREATE OR REPLACE FUNCTION IS_NULL(v VARCHAR2)
> RETURN NUMBER
> IS
> BEGIN
> IF TRIM(v) IS NULL THEN
> RETURN 1;
> ELSE
> RETURN 1;
> END IF;
> END;
>
>
> When excuting the following sql I get an ORA-03113 error
>
> SQL> select is_null(' ') from dual;
> select is_null(' ') from dual
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> What's wrong with TRIM(' ') and how else can I check if a variable
> consists of empty spaces without using TRIM?
>
> Jan Bols[/color]

re: trim(' ') problem & ORA-03113

firtsly, I assume one of those is a "return 0;"

Secondly, you probably need a PRAGMA inside the function definition
(not always needed in 9+ DBs). From memory it is RNDS, WNDS, RNPS
WNPS.

ETA

Jan Bols <jan@ivpv.ugent.be> wrote in message news:<bkemli$pg6$1@gaudi2.UGent.be>...[color=blue]
> Whenever I execute TRIM(' ')in a stored procedure or trigger, I get an
> ORA-03113 error.
>
> I have an oracle db 8.1.7.0.1 Enterprise edition installed on a
> linux-Mandrake 9.1.
>
> EXEMPLE:
> I created the function IS_NULL which returns 1 or 0 if the parameter is
> empty or not:
>
> CREATE OR REPLACE FUNCTION IS_NULL(v VARCHAR2)
> RETURN NUMBER
> IS
> BEGIN
> IF TRIM(v) IS NULL THEN
> RETURN 1;
> ELSE
> RETURN 1;
> END IF;
> END;
>
>
> When excuting the following sql I get an ORA-03113 error
>
> SQL> select is_null(' ') from dual;
> select is_null(' ') from dual
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> What's wrong with TRIM(' ') and how else can I check if a variable
> consists of empty spaces without using TRIM?
>
> Jan Bols[/color]

re: trim(' ') problem & ORA-03113

Jan Bols <jan@ivpv.ugent.be> wrote in message news:<bkemli$pg6$1@gaudi2.UGent.be>...[color=blue]
> Whenever I execute TRIM(' ')in a stored procedure or trigger, I get an
> ORA-03113 error.[/color]

I've just run into the same bug - on an 8.1.6.3.0 database. It'll let
you

SELECT TRIM(' ') FROM dual;

but crashes out if you

DECLARE
x VARCHAR2(10);
BEGIN
x := TRIM(x);
END;

Fortunately there's an easy workaround, you just go back to how we
used to do it before TRIM() was invented...

x := LTRIM(RTRIM(x));

That seems to work without any problems.

-- Chris Hunt
http://www.extracon.com