Home » SQL & PL/SQL » SQL & PL/SQL » chr(10) function
chr(10) function [message #208386] Sun, 10 December 2006 04:31 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello team

please go through my query & point out what i will i do to make it correct?

i have query like this-

SQL> ed
Wrote file afiedt.buf

  1   select rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
  2  ||chr(10)||
  3   substr(authorname, 1, instr(authorname, ' ', -1))
  4  ||chr(10)||
  5  length(authorname)-length(replace(authorname, 'T', '')) "sense"
  6*  from author
SQL> /
||chr(10)||
          *
ERROR at line 4:
ORA-01722: invalid number



though after few modification on my query it runs well.


SQL> select length(authorname)-length(replace(authorname, 'T', ''))
  2  ||chr(10)||
  3  rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
  4  ||chr(10)||
  5   substr(authorname, 1, instr(authorname, ' ', -1))"sense"
  6   from author;


please advice me why i unable to run the first query??

thanxx

with regards
ashish
Re: chr(10) function [message #208389 is a reply to message #208386] Sun, 10 December 2006 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Because

length(authorname)-length(replace(authorname, 'T', ''))

is a number; you can't concatenate a string with a number. To make it work, add TO_CHAR function:

TO_CHAR(ength(authorname)-length(replace(authorname, 'T', '')))
Re: chr(10) function [message #208390 is a reply to message #208386] Sun, 10 December 2006 04:48 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
Use this...

select rpad(substr(authorname, instr(authorname, ' ', 1)), 20)
||chr(10)||
substr(authorname, 1, instr(authorname, ' ', -1))
||chr(10)||
to_char(length(authorname)-length(replace(authorname, 'T', ''))) "sense"
from author;


Hope it works for you...
Re: chr(10) function [message #208396 is a reply to message #208386] Sun, 10 December 2006 05:10 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx to all

u r right, i m concate number to character.

but i m concate character to number which runs well.
(second query works, it dnt shows any error)

please advice me more.

with regards

ashish
Re: chr(10) function [message #208401 is a reply to message #208396] Sun, 10 December 2006 07:06 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
Go through Oracle SQL Reference guide for complete details.
Re: chr(10) function [message #208421 is a reply to message #208401] Sun, 10 December 2006 15:48 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd like to correct myself.

No, it is not about TO_CHAR function. Oracle is clever enough to perform an implicit conversion between datatypes if possible.

It is about arithmetic.

How come? Check this example: first, concatenate a character and a number. Oracle will implicitly convert 3 (a number) into '3' (a character) and return the correct value: 'a3':
SQL> select 'a' || length('abc') from dual;

'A
--
a3

Now let's try something like you did: concatenate a string with "length - length". If we calculate it, shortly it would look like this:

SELECT 'a' || 3 - 2 FROM dual:
SQL> select 'a' || length('abc') - length('de') from dual;
select 'a' || length('abc') - length('de') from dual
            *
ERROR at line 1:
ORA-01722: invalid number

Oracle was confused: first part is exactly the same as previous example, but what is that "minus" sign doing here? Am I concatenating or subtracting? I have no idea - I'll return an "INVALID NUMBER" error. So, make up your mind and tell me exactly what to do!

But, if we enclose "length - length" into brackets, we'll again simplify it to

SELECT 'a' || (3 - 2) FROM DUAL =
SELECT 'a' || 1 FROM DUAL -> again our first example. Oracle knows how to deal with it, so it correctly returns the answer:
SQL> select 'a' || (length('abc') - length('de')) from dual;

'A
--
a1

Finally, example which shows a "working" case although there's subtraction and concatenation: Oracle first performs subtraction, gets a result (number 1), converts it into a character to be able to concatenate it with a character:
SELECT 3 - 2 || 'a' FROM DUAL =
SELECT 1 || 'a' FROM DUAL -> again our first, known case
SQL> select length('abc') - length('de') || 'a' from dual;

LE
--
1a

I'm sorry if my first, quite stupid answer, lead you to a wrong conclusion.

[Updated on: Sun, 10 December 2006 15:53]

Report message to a moderator

Re: chr(10) function [message #208424 is a reply to message #208386] Sun, 10 December 2006 16:52 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx littlefoot

ur great Cool
u gave me time to think about it.
ur not right . i m very -very impressed with ur answer
& appreciate ur answer. u respond me calmly, thanxx for it.

now i understand about the logic behind it.
what ever doubt was in my mind is clear now.

thanxx

with regards
ashish
Previous Topic: Aditional Support for PL/SQL or Java batche programs on RDBMS server
Next Topic: pl/sql
Goto Forum:
  


Current Time: Mon Dec 05 13:18:32 CST 2016

Total time taken to generate the page: 0.10843 seconds