Home » SQL & PL/SQL » SQL & PL/SQL » Letter and TIMESTAMP CONCAT (INSERT error) (19c)
Letter and TIMESTAMP CONCAT (INSERT error) [message #687811] |
Sat, 17 June 2023 09:42 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
I am trying to concatenate a letter with a TIMESTAMP and I am getting the following error, which I can't seem to figure out. I was hoping someone can tell me how to fix this issue.
Below is my error and setup.
ORA-00933: SQL command not properly ended
CREATE OR REPLACE PACKAGE mf_names IS
FUNCTION random_first_name(
gender IN VARCHAR2 DEFAULT NULL,
percentage_mf IN NUMBER DEFAULT 50
) RETURN VARCHAR2;
FUNCTION random_last_name RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY mf_names IS
first_names_male SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Tom', 'Andy', 'Paul', 'Peter', 'Keith', 'Mark', 'Solomon', 'Joseph', 'John', 'Roger', 'Douglas','Harry', 'Barry', 'Larry', 'Gary', 'Jeffrey', 'David', 'Stuart', 'Karl', 'Seth', 'David', 'Brian', 'Sidney', 'James', 'Shane', 'Zachary', 'Anthony'
);
first_names_female SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Alice', 'Anna', 'Lee', 'Barbara', 'Carol', 'Debra', 'Madison', 'Faith', 'Cheryl', 'Beth', 'Kathy', 'Abigail', 'Jill', 'Grayce', 'Lynn', 'Roz', 'Carolyn', 'Deena', 'Laura', 'Sophia', 'Elise'
);
last_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Cooper', 'Dimeo', 'Caygle', 'Luppo', 'Coralnick', 'Torchiano', 'Fazio', 'Behrens', 'Zaza', 'Lebowitz', 'Vatsch', 'Malden', 'Kramer', 'Stein', 'Tessio', 'Weinreb', 'Dillon', 'Zanona', 'Rucker', 'Zanzone', 'Santoro', 'Barese', 'Silverberg', 'Aarron', 'Kern', 'Saladino', 'Rice', 'Sanford', 'Orr', 'Roth'
);
FUNCTION random_first_name(
gender IN VARCHAR2 DEFAULT NULL,
percentage_mf IN NUMBER DEFAULT 50
) RETURN VARCHAR2
IS
BEGIN
IF UPPER(gender) LIKE 'M%' THEN
RETURN first_names_male(FLOOR(DBMS_RANDOM.VALUE(1, first_names_male.COUNT + 1)));
ELSIF UPPER(gender) LIKE 'F%' THEN
RETURN first_names_female(FLOOR(DBMS_RANDOM.VALUE(1, first_names_female.COUNT + 1)));
ELSIF DBMS_RANDOM.VALUE(0, 100) < percentage_mf THEN
RETURN random_first_name('M');
ELSE
RETURN random_first_name('F');
END IF;
END;
FUNCTION random_last_name RETURN VARCHAR2
IS
BEGIN
RETURN last_names(FLOOR(DBMS_RANDOM.VALUE(1, last_names.COUNT + 1)));
END;
END;
/
create table customers
(seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
customer_id varchar2(7),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
create_ts timestamp with local time zone default on null localtimestamp,
--
constraint customer_pk primary key (seq_num),
constraint customer_uk unique (customer_id)
);
insert into customers
(
customer_id,
first_name,
last_name
)
values
(
'A' || to_char(SYSTIMESTAMP, 'FF6'),
mf_names.random_first_name(),
mf_names.random_last_name()
)
);
|
|
|
Re: Letter and TIMESTAMP CONCAT (INSERT error) [message #687812 is a reply to message #687811] |
Sat, 17 June 2023 10:18 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I checked on 21c and apart that your INSERT statement last line which is too much I have no problem:
SQL> insert into customers
2 (
3 customer_id,
4 first_name,
5 last_name
6 )
7 values
8 (
9 'A' || to_char(SYSTIMESTAMP, 'FF6'),
10 mf_names.random_first_name(),
11 mf_names.random_last_name()
12 );
1 row created.
SQL> @v
Oracle version: 21.3.0.0.0 EE - JVM v1.8.0_301 - timezone files v35
[Updated on: Sat, 17 June 2023 10:19] Report message to a moderator
|
|
|
Re: Letter and TIMESTAMP CONCAT (INSERT error) [message #687813 is a reply to message #687812] |
Sat, 17 June 2023 10:22 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Same thing in 19c:
SQL> create table customers
2 (seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
3 customer_id varchar2(7),
4 first_name VARCHAR2(20),
5 last_name VARCHAR2(20),
6 create_ts timestamp with local time zone default on null localtimestamp,
7 --
8 constraint customer_pk primary key (seq_num),
9 constraint customer_uk unique (customer_id)
10 );
Table created.
SQL> insert into customers
2 (
3 customer_id,
4 first_name,
5 last_name
6 )
7 values
8 (
9 'A' || to_char(SYSTIMESTAMP, 'FF6'),
10 mf_names.random_first_name(),
11 mf_names.random_last_name()
12 );
1 row created.
SQL> select * from customers;
SEQ_NUM CUSTOME FIRST_NAME LAST_NAME
---------- ------- -------------------- --------------------
CREATE_TS
---------------------------------------------------------------------------
1 A660000 Shane Sanford
17/06/2023 17:21:11.660
1 row selected.
SQL> @v
Oracle version: 19.13.0.0.211019 EE - JVM v1.8.0_201 - timezone files v32
|
|
|
Re: Letter and TIMESTAMP CONCAT (INSERT error) [message #687814 is a reply to message #687811] |
Sat, 17 June 2023 10:24 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have an extra right parenthesis at the end of your insert statement
reproduction of error:
##SCOTT@XE_21.3.0.0.0> insert into customers
2 (
3 customer_id,
4 first_name,
5 last_name
6 )
7 values
8 (
9 'A' || to_char(SYSTIMESTAMP, 'FF6'),
10 mf_names.random_first_name(),
11 mf_names.random_last_name()
12 )
13 );
)
*
ERROR at line 13:
ORA-00933: SQL command not properly ended
correction:
C##SCOTT@XE_21.3.0.0.0> insert into customers
2 (
3 customer_id,
4 first_name,
5 last_name
6 )
7 values
8 (
9 'A' || to_char(SYSTIMESTAMP, 'FF6'),
10 mf_names.random_first_name(),
11 mf_names.random_last_name()
12 );
1 row created.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:36:46 CDT 2024
|