Home » SQL & PL/SQL » SQL & PL/SQL » How to read oracle diagram in the documentation? (oracle 10g on aix)
How to read oracle diagram in the documentation? [message #442518] Mon, 08 February 2010 18:42 Go to next message
kang
Messages: 89
Registered: November 2007
Member
How to read oracle diagram in the documentation?

legend etc.

Thanks in advance.
Re: How to read oracle diagram in the documentation? [message #442519 is a reply to message #442518] Mon, 08 February 2010 18:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

What is URL to page containing the diagram you ask about?
Re: How to read oracle diagram in the documentation? [message #442520 is a reply to message #442518] Mon, 08 February 2010 18:57 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member

I'm saying general topic.
for now

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions164.htm#SQLRF06116

in sys_connect_by_path, the second parameter is chr,
but chr(10) or chr(13) is not allowed I guess.

any can help me with general topic in oracle docs.

[Updated on: Mon, 08 February 2010 18:58]

Report message to a moderator

Re: How to read oracle diagram in the documentation? [message #442523 is a reply to message #442520] Mon, 08 February 2010 21:19 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.


Re read the content properly....it is char not chr.

its already provided one Examle for you..

SQL> set pages 1000
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
  2     FROM employees
  3     START WITH last_name = 'Kochhar'
  4     CONNECT BY PRIOR employee_id = manager_id;

Path
--------------------------------------------------------------------------------
 /Kochhar
   /Kochhar/Whalen
   /Kochhar/Mavris
   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz
   /Kochhar/Greenberg
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Chen
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Greenberg/Popp

12 rows selected.


sriram
Re: How to read oracle diagram in the documentation? [message #442538 is a reply to message #442520] Mon, 08 February 2010 23:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
in sys_connect_by_path, the second parameter is chr,
but chr(10) or chr(13) is not allowed I guess.

SQL> select level, sys_connect_by_path(ename,ch(10)) path
  2  from emp
  3  connect by prior empno = mgr
  4  start with ename='KING'
  5  /
select level, sys_connect_by_path(ename,ch(10)) path
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function

You're right, you may raise a documentation bug because the restriction is missing.

Regards
Michel


[Updated on: Mon, 08 February 2010 23:06]

Report message to a moderator

Re: How to read oracle diagram in the documentation? [message #442546 is a reply to message #442538] Mon, 08 February 2010 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
kind of works for me

  1  select level, sys_connect_by_path(ename,'
  2  '	 ) path
  3	 from emp
  4	 connect by prior empno = mgr
  5*	 start with ename='KING'
SQL> /
	 1

KING

	 2

KING
JONES

	 3

KING
JONES
SCOTT

	 4

KING
JONES
SCOTT
ADAMS

	 3

KING
JONES
FORD

	 4

KING
JONES
FORD
SMITH

	 2

KING
BLAKE

	 3

KING
BLAKE
ALLEN

	 3

KING
BLAKE
WARD

	 3

KING
BLAKE
MARTIN

	 3

KING
BLAKE
TURNER

	 3

KING
BLAKE
JAMES

	 2

KING
CLARK

	 3

KING
CLARK
MILLER


14 rows selected.

SQL> 

Re: How to read oracle diagram in the documentation? [message #442550 is a reply to message #442546] Mon, 08 February 2010 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Silly me, I can't write a quey without putting a typo.
But even if I used the correct function, it is not allowed (just a remark):
SQL> select ascii('/') from dual;
ASCII('/')
----------
        47

1 row selected.

SQL> select level, sys_connect_by_path(ename,chr(47)) path
  2  from emp
  3  connect by prior empno = mgr
  4  start with ename='KING'
  5  /
select level, sys_connect_by_path(ename,chr(47)) path
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function

YOu have to put a static character not an expression returning a character.

Regards
Michel
Re: How to read oracle diagram in the documentation? [message #442555 is a reply to message #442550] Tue, 09 February 2010 00:35 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Hay Michel

same error for me too.

  1  select level, sys_connect_by_path(ename,chr(47)) path
  2      from emp
  3      connect by prior empno = mgr
  4      start with ename='KING'
  5      /
 select level, sys_connect_by_path(ename,chr(47)) path
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function



but when I have replaced chr(47) with '/' and it worked for me!


  1   select level, sys_connect_by_path(ename,'/') path
  2      from emp
  3      connect by prior empno = mgr
  4*     start with ename='KING'
	/


no rows selected


My version is

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production


[Updated on: Tue, 09 February 2010 00:36]

Report message to a moderator

Re: How to read oracle diagram in the documentation? [message #442557 is a reply to message #442555] Tue, 09 February 2010 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, this is what I said:
You have to put a static character not an expression returning a character.

Regards
Michel

Re: How to read oracle diagram in the documentation? [message #443467 is a reply to message #442546] Mon, 15 February 2010 23:41 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
You are right.
how to trim the leading white spaces then?

select level,
       sys_connect_by_path(ename,'
  ') path
  from emp
connect by prior empno = mgr
 start with ename = 'KING'


this doesn't work.
Re: How to read oracle diagram in the documentation? [message #443468 is a reply to message #443467] Mon, 15 February 2010 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>this doesn't work.

my car doesn't work.
Tell me how to make my car go.

Re: How to read oracle diagram in the documentation? [message #443469 is a reply to message #443468] Mon, 15 February 2010 23:55 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
my mistakes.

select level,
       trim(sys_connect_by_path(ename,'
  ')) path
  from emp
connect by prior empno = mgr
 start with ename = 'KING'


select level,
       trim(ltrim(sys_connect_by_path(ename,'
  '),chr(10))) path
  from emp
connect by prior empno = mgr
 start with ename = 'KING'



conclusiion!
chr(10) can't be trimed with trim method.

Thanks.
Re: How to read oracle diagram in the documentation? [message #443480 is a reply to message #443469] Tue, 16 February 2010 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
conclusiion!
chr(10) can't be trimed with trim method.

SQL> select '"'||ltrim('
  2  abc',chr(10))||'"' from dual;
'"'||
-----
"abc"

1 row selected.

Regards
Michel
Re: How to read oracle diagram in the documentation? [message #443523 is a reply to message #443480] Tue, 16 February 2010 02:31 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
I mean TRIM not ltrim(nor rtrim).
Re: How to read oracle diagram in the documentation? [message #443531 is a reply to message #443523] Tue, 16 February 2010 02:49 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Conclusion: use ltrim.

Regards
Michel
Previous Topic: variables in this loop procedure
Next Topic: Case Limitations
Goto Forum:
  


Current Time: Sun Feb 09 08:58:48 CST 2025