Artigo

Error when creating database link using password started with numeric character.

Ricardo Rezende

Ricardo Rezende

— 2 min read
Share

Good evening everyone (or is it good morning? After all, it is dark - when I wrote this post, not when I published it! :) ).

This is our DBA life. Always implementing new functionalities on database in a production environment at dawn. :)
And, as usual, it always happen some surprises in the middle of the implementation.
Problem found, issue solved, and now, solution shared. :D
Oracle database allows a db user to be created using a password started with numeric character, as follow:

02:55:30 SYSTEM@ORCL2> CREATE USER TEST 02:55:33   2  IDENTIFIED BY 123456; User created. Elapsed: 00:00:00.03

But see that, if a mix of alphanumeric and numeric character are used, an error is triggered:

02:57:23 SYSTEM@ORCL2> CREATE USER TEST2 02:57:26   2  IDENTIFIED BY 123dasilva5; CREATE USER TEST2 IDENTIFIED BY 123dasilva5 * ERROR at line 1: ORA-00922: missing or invalid option Elapsed: 00:00:00.00

The solution for creating user, is set password (with mixed numeric and alphanumeric) in quotation marks (" "), as follows:

02:59:52 SYSTEM@ORCL2> CREATE USER TEST2 02:59:55   2  IDENTIFIED BY "123dasilva5"; User created. Elapsed: 00:00:00.05

OK. Now there are 2 users (TEST and TEST2), created on ORCL2 db, where the first one has a password set using only numeric characters, and the second one has a mixed (numeric and alphanumeric) password.
Now, let's see database link behavior:

03:02:21 SYSTEM@ORCL> CREATE DATABASE LINK DBLINK_TEST 03:02:24   2  CONNECT TO TEST IDENTIFIED BY 123456 03:02:27   3  USING 'ORCL2'; CONNECT TO TESTE IDENTIFIED BY 123456 * ERROR at line 1: ORA-00933: SQL command not properly ended Elapsed: 00:00:00.01 03:03:45 SYSTEM@ORCL> CREATE DATABASE LINK DBLINK_TEST2 03:03:50   2  CONNECT TO TESTE2 IDENTIFIED BY 123dasilva5 03:03:53   3  USING 'ORCL2'; CONNECT TO TESTE2 IDENTIFIED BY 123dasilva5 * ERROR at line 1: ORA-00933: SQL command not properly ended Elapsed: 00:00:00.01

See that, in both situations, database links were not created. See also that message error is not so clear. The only clue is the asterisk (*) below the password, in the error message.
Solution is quite simple. No matter how user was created (password between quotation marks or not), when creating database link where user password is started with numeric character, it will be always needed to set password between quotation marks, as follow:

03:07:12 SYSTEM@ORCL> CREATE DATABASE LINK DBLINK_TEST 03:07:15   2  CONNECT TO TEST IDENTIFIED BY "123456" 03:07:18   3  USING 'ORCL2'; Database link created. Elapsed: 00:00:00.01 03:09:33 SYSTEM@ORCL> CREATE DATABASE LINK DBLINK_TEST2 03:09:38   2  CONNECT TO TEST2 IDENTIFIED BY "123dasilva5" 03:09:41   3  USING 'ORCL2'; Database link created. Elapsed: 00:00:00.01

That's it! :)

Read more