469,111 Members | 1,974 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

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

Jul 19 '05 #1
4 11371
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

Jul 19 '05 #2
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

Jul 19 '05 #3
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

Jul 19 '05 #4
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
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Paul | last post: by
12 posts views Thread by Robert Mark Bram | last post: by
1 post views Thread by dirk van waes | last post: by
3 posts views Thread by yawnmoth | last post: by
4 posts views Thread by Jan Bols | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.