Home » SQL & PL/SQL » SQL & PL/SQL » Questing regarding level and connect by (Oracle 10g)
Questing regarding level and connect by [message #624592] Tue, 23 September 2014 14:53 Go to next message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Dear all,

I have to convert a simple string into its ASCII code for every character. So, I've wrote this simple code to archive the requirement


DECLARE

V_STRING        VARCHAR2(30):='Ascii Test';
V_ASCII         VARCHAR2(500);

BEGIN

    FOR i IN 1..LENGTH(V_STRING) LOOP
    
     V_ASCII:= V_ASCII || '|' || ASCII(SUBSTR(V_STRING,i,1));  
    
    END LOOP;
    
  dbms_output.put_line(v_ascii);  

END;


However, as far as I understood if I am not mistaken this simple requirement can be archieved in plain SQL. Can I do that with the Oracle Function level and connect by ?

Thanks in advance.
Steve.
Re: Questing regarding level and connect by [message #624594 is a reply to message #624592] Tue, 23 September 2014 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can I do that with the Oracle Function level and connect by ?


Yes, with sys_connect_by_path function.

Re: Questing regarding level and connect by [message #624595 is a reply to message #624594] Tue, 23 September 2014 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But you can also do it without:
SQL> select replace(substr(dump('Ascii Test'),instr(dump('Ascii Test'),':')+2),',','|') res from dual;
RES
-----------------------------------
65|115|99|105|105|32|84|101|115|116

Re: Questing regarding level and connect by [message #624596 is a reply to message #624595] Tue, 23 September 2014 15:05 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or even
SQL> select rtrim(xmlagg(xmlelement (e, ascii(substr('Ascii Test', level, 1)) ||';')).
  2         extract ('//text()'), ';') result
  3  from dual
  4  connect by level <= length('Ascii Test');

RESULT
--------------------------------------------------------------------------------
65;115;99;105;105;32;84;101;115;116

SQL>

(but I prefer Michel's way).
Re: Questing regarding level and connect by [message #624597 is a reply to message #624596] Tue, 23 September 2014 15:16 Go to previous messageGo to next message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Dear Michael & LitteFoot.

Thank you very much for helping me. I didn't know that there was a funcion called dump which returns the ascii value for each character.

Again, thank you.

Regards,
Steve.
Re: Questing regarding level and connect by [message #624598 is a reply to message #624597] Tue, 23 September 2014 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can learn a lot by simply Reading The Fine Manual (URL below)

http://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
Re: Questing regarding level and connect by [message #624612 is a reply to message #624592] Wed, 24 September 2014 01:40 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@Michel & @LF , what does 'typ' denotes in dump function?
Re: Questing regarding level and connect by [message #624613 is a reply to message #624612] Wed, 24 September 2014 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is described in documentation; did you not read it?
Quote:

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr

[Updated on: Wed, 24 September 2014 01:47]

Report message to a moderator

Re: Questing regarding level and connect by [message #624614 is a reply to message #624612] Wed, 24 September 2014 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The type of the data.

Re: Questing regarding level and connect by [message #624724 is a reply to message #624614] Thu, 25 September 2014 00:31 Go to previous message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

Yup got it Smile
Previous Topic: User Login Query for Specific Month
Next Topic: Row generator
Goto Forum:
  


Current Time: Thu Apr 25 06:55:10 CDT 2024