Home » SQL & PL/SQL » SQL & PL/SQL » about sequence
about sequence [message #285681] Wed, 05 December 2007 05:10 Go to next message
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
Member

Good day sir,

could u help me how to generate sequence with following format

eg: cust1,cust2....ect


There is anyway to create like this insteed of start with 1 and end with 100.

- Thank you.
Re: about sequence [message #285682 is a reply to message #285681] Wed, 05 December 2007 05:13 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You can concatenate your sequence with string 'cust'.

Regards,
Kiran
Re: about sequence [message #285684 is a reply to message #285681] Wed, 05 December 2007 05:17 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SQL> select 'CUST'||MY_SEQ.nextval SEQUENCE from dual;

SEQUENCE
--------------------------------------------
CUST14



Kiran.
Re: about sequence [message #285696 is a reply to message #285684] Wed, 05 December 2007 05:46 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
Member



Thank you

There is anyway in DDL statement for the above concept.
Re: about sequence [message #285698 is a reply to message #285681] Wed, 05 December 2007 05:49 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

There is anyway in DDL statement for the above concept.


No, I din't get you. As i know this is the way you can acheive.

Kiran.
Re: about sequence [message #285699 is a reply to message #285696] Wed, 05 December 2007 05:51 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
One DDL command I can think of is a "create function":
CREATE SEQUENCE mhe_seq START WITH 1 INCREMENT BY 1
/

CREATE FUNCTION get_custno
RETURN VARCHAR2
IS
  v_return VARCHAR2(20);
BEGIN
  SELECT 'cust'||TO_CHAR(mhe_seq.nextval)
  INTO   v_return
  FROM dual;
  
  RETURN v_return;
END;
/
sho err

col custno format a20
SELECT get_custno custno
FROM   dual
/

SELECT get_custno custno
FROM  ( SELECT *
        FROM   dual
        CONNECT BY LEVEL < 10
      )
/

DROP FUNCTION get_custno
/

DROP SEQUENCE mhe_seq
/

MHE

[Updated on: Wed, 05 December 2007 05:52]

Report message to a moderator

Previous Topic: PLSQL TABLE & USING FORALL
Next Topic: Columns in a Compound primary key
Goto Forum:
  


Current Time: Sun Dec 11 06:27:39 CST 2016

Total time taken to generate the page: 0.11624 seconds