trim(' ') problem & ORA-03113

[Jan Bols]

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

[Michael Willer]

Jan Bols wrote:

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

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

[G M]

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 <ja*@ivpv.ugent.be> wrote in message news:<bk**********@gaudi2.UGent.be>...

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

[Ethel Aardvark]

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 <ja*@ivpv.ugent.be> wrote in message news:<bk**********@gaudi2.UGent.be>...

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

[Chris Hunt]

Jan Bols <ja*@ivpv.ugent.be> wrote in message news:<bk**********@gaudi2.UGent.be>...

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

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