Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Alphanumeric Sequence (Oracle 11g,win7)
Oracle Alphanumeric Sequence [message #613311] Sun, 04 May 2014 07:53 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello Friends,
I am want to create one sequence that contain some alphanumeric words.The Sequence is start like
1A,2A,3A....9A after the 9A the next value is 1B,1C,1D...1Z .than after 2B,2C....2Z. so finally the series like ..

---------------------
1A,1B,1C....1Z
2A,2B.......2Z
.........
......SO ON ...
-------------------------

Please Help me out....


Thanks


Re: Oracle Alphanumeric Sequence [message #613313 is a reply to message #613311] Sun, 04 May 2014 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1A,2A,3A....9A after the 9A the next value is 1B,1C,1D...1Z .than after 2B,2C....2Z.
above disagrees with below
>1A,1B,1C....1Z

Please clearly & consistently specify the actual result set.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Re: Oracle Alphanumeric Sequence [message #613314 is a reply to message #613313] Sun, 04 May 2014 08:48 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
The proper sequence is ...

1A,2A,3A,4A...9A,1B,1C,1D....1Z,2B,2C,2D....2Z,3B,3C,3D....3Z,4B,4C,4D...4Z.....So on ..

and this sequence is end with 9z(Last Value).

Thanks for reply...
Re: Oracle Alphanumeric Sequence [message #613315 is a reply to message #613314] Sun, 04 May 2014 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is a nice homework assignment.
You need to post what you have tried already.

>and this sequence is end with 9z(Last Value).
is "9z" different from "9Z"?

You are consistently inconsistent.
Re: Oracle Alphanumeric Sequence [message #613316 is a reply to message #613315] Sun, 04 May 2014 08:59 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
"9z" and "9Z" are same thing ....

Re: Oracle Alphanumeric Sequence [message #613317 is a reply to message #613314] Sun, 04 May 2014 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
1A,2A,3A,4A...9A,1B,1C,1D....1Z,2B,2C,2D....2Z,3B,3C,3D....3Z,


Are you sure?
See what you wrote:
1A,2A,3A,4A...9A
1B,1C,1D....1Z
2B,2C,2D....2Z
3B,3C,3D....3Z
You want 9 "A" first then 25 "1", then 25 "2", then 25 "3" and what after?

[Updated on: Sun, 04 May 2014 09:22]

Report message to a moderator

Re: Oracle Alphanumeric Sequence [message #613318 is a reply to message #613316] Sun, 04 May 2014 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Xandot wrote on Sun, 04 May 2014 06:59
"9z" and "9Z" are same thing ....



Oracle disagrees with you!

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  STR1 VARCHAR2(2) := '9z';
  3  STR2 VARCHAR2(2) := '9Z';
  4  BEGIN
  5  IF STR1 = STR2 THEN
  6      DBMS_OUTPUT.PUT_LINE('SAME');
  7  ELSE
  8      DBMS_OUTPUT.PUT_LINE('DIFFERENT');
  9  END IF;
 10  END;
 11  /
DIFFERENT

PL/SQL procedure successfully completed.
Re: Oracle Alphanumeric Sequence [message #613320 is a reply to message #613311] Sun, 04 May 2014 09:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Xandot, I don't think anyone understands what sequence you want. You need to post the homework question, and your attempt so far, and say what you are stuck on. Is it, for instance, the concept of how to generate rows? If so, here are a few ideas, http://www.orafaq.com/forum/t/95011/2/
Re: Oracle Alphanumeric Sequence [message #613351 is a reply to message #613320] Mon, 05 May 2014 06:02 Go to previous messageGo to next message
forums_man
Messages: 3
Registered: May 2014
Junior Member

create sequence abc

2 start with 65
3 maxvalue 90
4 minvalue 65
5 cycle
****************************

creat sequence numbers
start wiht 1
max value 1000
minvalue 1
cycle


//////////////////////////////


create or replace function alph_seq

return varchar2

is
mix varchare2;

begin

select chr(numbers.nextval)||chr(abc.nextval) into mix from dual;
return mix ;

end alph_seq ;

finally

just call the function in loop
Re: Oracle Alphanumeric Sequence [message #613352 is a reply to message #613351] Mon, 05 May 2014 06:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. It is very nice to see someone whose first message is an answer, not a question. But please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Re: Oracle Alphanumeric Sequence [message #613354 is a reply to message #613351] Mon, 05 May 2014 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition...
Verify your statements are correct:
SQL> creat sequence numbers
SP2-0734: unknown command beginning "creat sequ..." - rest of line ignored.
SQL> start wiht 1
SP2-0310: unable to open file "wiht.sql"
SQL> max value 1000
SP2-0734: unknown command beginning "max value ..." - rest of line ignored.
SQL> minvalue 1
SP2-0042: unknown command "minvalue 1" - rest of line ignored.
SQL> cycle
SP2-0042: unknown command "cycle" - rest of line ignored.

Verify that your "solution" fits the need.
Here 1) we don't know what are the specifications and 2) anyway, given the few we know, I doubt "chr(numbers.nextval)||chr(abc.nextval)" will give a number followed by a character.

[Updated on: Tue, 06 May 2014 02:02]

Report message to a moderator

Re: Oracle Alphanumeric Sequence [message #613361 is a reply to message #613351] Mon, 05 May 2014 10:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@forums_man,

Apart from what John suggested you to follow the forum rules, please look your untested code :

forums_man wrote on Mon, 05 May 2014 16:32

mix varchare2;


Above is a syntax error.

Quote:

just call the function in loop


Why plsql? Also, think about the sequence when it exceeds the required limits which you assumed to just suffice the requirement for Aplhabets and Numbers.

P.S. : I appreciate. Good to see that you tried to post a solution, however, if it is syntactically incorrect then it is not going to benifit anyone. Please reconsider posting again with a syntactically correct code. Thanks.

[Updated on: Mon, 05 May 2014 10:29]

Report message to a moderator

Re: Oracle Alphanumeric Sequence [message #613363 is a reply to message #613351] Mon, 05 May 2014 14:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
forums_man wrote on Mon, 05 May 2014 07:02

create sequence abc
creat sequence numbers
finally just call the function in loop


No need for two sequences. No need for function. All can be done in SQL with one sequence + CEIL + MOD + CHR.

SY.
Re: Oracle Alphanumeric Sequence [message #613371 is a reply to message #613363] Tue, 06 May 2014 00:02 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
No need for two sequences. No need for function.


But need a clear question.. Laughing

Previous Topic: Creating Table in plsql
Next Topic: Time Out Or Kill the Session
Goto Forum:
  


Current Time: Thu Mar 28 03:55:59 CDT 2024