12cVS19c_spool_csv

View All Public Scripts Login to Run Script

Script Name 12cVS19c_spool_csv
Visibility Unlisted - anyone with the share link can access
Description test case to generate csv file in 12c and 19c. It has worded well in 12c but in 19c, spaces are added before and after value !
Area Oracle 19c
Contributor gam
Created Monday September 20, 2021

Statement 1

CREATE FUNCTION CVDATE(cd_date NUMBER) RETURN DATE 
IS 
cd_date2 DATE; 
BEGIN 
 cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + ABS(cd_date); 
 RETURN (cd_date2); 
END;

Function created.

Statement 2

CREATE FUNCTION CVDATE(cd_date NUMBER) RETURN DATE 
IS 
cd_date2 DATE; 
BEGIN 
 cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + ABS(cd_date); 
 RETURN (cd_date2); 
END; 
 
CREATE TABLE TEST (CODEP CHAR(6), DATEV NUMBER(6)); 
 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100801',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100804',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100805',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100806',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100809',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100810',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100811',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100812',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100813',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100814',-96332); 
 
COMMIT;                         
SELECT * FROM test;

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 3

CREATE FUNCTION CVDATE(cd_date NUMBER) RETURN DATE 
IS 
cd_date2 DATE; 
BEGIN 
 cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + ABS(cd_date); 
 RETURN (cd_date2); 
END; 
 
CREATE TABLE TEST (CODEP CHAR(6), DATEV NUMBER(6)); 
 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100801',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100804',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100805',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100806',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100809',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100810',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100811',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100812',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100813',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100814',-96332); 
 
COMMIT;                         
SELECT * FROM test;

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 4

INSERT INTO TEST (CODEP, DATEV) VALUES ('100801',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 5

INSERT INTO TEST (CODEP, DATEV) VALUES ('100804',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 6

INSERT INTO TEST (CODEP, DATEV) VALUES ('100805',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 7

INSERT INTO TEST (CODEP, DATEV) VALUES ('100806',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 8

INSERT INTO TEST (CODEP, DATEV) VALUES ('100809',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 9

INSERT INTO TEST (CODEP, DATEV) VALUES ('100810',-95801)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 10

INSERT INTO TEST (CODEP, DATEV) VALUES ('100811',-96332)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 11

INSERT INTO TEST (CODEP, DATEV) VALUES ('100812',-96332)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 12

INSERT INTO TEST (CODEP, DATEV) VALUES ('100813',-96332)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 13

INSERT INTO TEST (CODEP, DATEV) VALUES ('100814',-96332)

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 14

CREATE TABLE TEST (CODEP CHAR(6), DATEV NUMBER(6))

Table created.

Statement 15

INSERT INTO TEST (CODEP, DATEV) VALUES ('100801',-95801)

1 row(s) inserted.

Statement 16

INSERT INTO TEST (CODEP, DATEV) VALUES ('100804',-95801)

1 row(s) inserted.

Statement 17

INSERT INTO TEST (CODEP, DATEV) VALUES ('100805',-95801)

1 row(s) inserted.

Statement 18

INSERT INTO TEST (CODEP, DATEV) VALUES ('100806',-95801)

1 row(s) inserted.

Statement 19

INSERT INTO TEST (CODEP, DATEV) VALUES ('100809',-95801)

1 row(s) inserted.

Statement 20

INSERT INTO TEST (CODEP, DATEV) VALUES ('100810',-95801)

1 row(s) inserted.

Statement 21

INSERT INTO TEST (CODEP, DATEV) VALUES ('100811',-96332)

1 row(s) inserted.

Statement 22

INSERT INTO TEST (CODEP, DATEV) VALUES ('100812',-96332)

1 row(s) inserted.

Statement 23

INSERT INTO TEST (CODEP, DATEV) VALUES ('100813',-96332)

1 row(s) inserted.

Statement 24

INSERT INTO TEST (CODEP, DATEV) VALUES ('100814',-96332)

1 row(s) inserted.

Statement 25
```
COMMIT
```
```
Statement processed.
```

Statement 26

SELECT * FROM test


CODEP DATEV
100801 -95801
100804 -95801
100805 -95801
100806 -95801
100809 -95801
100810 -95801
100811 -96332
100812 -96332
100813 -96332
100814 -96332

10 rows selected.

Statement 27
```
spool test.csv 
```
```
Unsupported Command
```

CODEP	DATEV
100801	-95801
100804	-95801
100805	-95801
100806	-95801
100809	-95801
100810	-95801
100811	-96332
100812	-96332
100813	-96332
100814	-96332

Statement 28

SELECT codep, ';', 
       cvdate(datev) as p 
from test


CODEP ';' P
100801 ; 31-DEC-14
100804 ; 31-DEC-14
100805 ; 31-DEC-14
100806 ; 31-DEC-14
100809 ; 31-DEC-14
100810 ; 31-DEC-14
100811 ; 14-JUN-16
100812 ; 14-JUN-16
100813 ; 14-JUN-16
100814 ; 14-JUN-16

10 rows selected.

Statement 29
```
spool off
```
```
Unsupported Command
```

CODEP	';'	P
100801	;	31-DEC-14
100804	;	31-DEC-14
100805	;	31-DEC-14
100806	;	31-DEC-14
100809	;	31-DEC-14
100810	;	31-DEC-14
100811	;	14-JUN-16
100812	;	14-JUN-16
100813	;	14-JUN-16
100814	;	14-JUN-16

Statement 30

CREATE FUNCTION CVDATE(cd_date NUMBER) RETURN DATE 
IS 
cd_date2 DATE; 
BEGIN 
 cd_date2 := TO_DATE('14/09/1752','DD/MM/YYYY') + ABS(cd_date); 
 RETURN (cd_date2); 
END; 
 
CREATE TABLE TEST (CODEP CHAR(6), DATEV NUMBER(6)); 
 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100801',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100804',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100805',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100806',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100809',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100810',-95801); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100811',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100812',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100813',-96332); 
INSERT INTO TEST (CODEP, DATEV) VALUES ('100814',-96332); 
COMMIT;                         
 
SELECT * FROM test; 
 
spool test.csv 
SELECT codep, ';', 
       cvdate(datev) as p 
from test; 
spool off;

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 31

SELECT * FROM test


CODEP DATEV
100801 -95801
100804 -95801
100805 -95801
100806 -95801
100809 -95801
100810 -95801
100811 -96332
100812 -96332
100813 -96332
100814 -96332

10 rows selected.

CODEP	DATEV
100801	-95801
100804	-95801
100805	-95801
100806	-95801
100809	-95801
100810	-95801
100811	-96332
100812	-96332
100813	-96332
100814	-96332

Statement 32

SELECT codep, ';', 
       cvdate(datev) as p 
from test


CODEP ';' P
100801 ; 31-DEC-14
100804 ; 31-DEC-14
100805 ; 31-DEC-14
100806 ; 31-DEC-14
100809 ; 31-DEC-14
100810 ; 31-DEC-14
100811 ; 14-JUN-16
100812 ; 14-JUN-16
100813 ; 14-JUN-16
100814 ; 14-JUN-16

10 rows selected.

CODEP	';'	P
100801	;	31-DEC-14
100804	;	31-DEC-14
100805	;	31-DEC-14
100806	;	31-DEC-14
100809	;	31-DEC-14
100810	;	31-DEC-14
100811	;	14-JUN-16
100812	;	14-JUN-16
100813	;	14-JUN-16
100814	;	14-JUN-16

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database