Feed aggregator

TEMP_UNDO_ENABLED parameter

Tom Kyte - 2 hours 46 min ago
what is the use of "TEMP_UNDO_ENABLED" in oracle database. when our user also use global temporary table. When it's necessary to set TEMP_UNDO_ENABLED = TRUE in which circumtance? If I enable this parameter to true so it's decrese the performance of database ?
Categories: DBA Blogs

tkprof sort option to find most expensive query

Tom Kyte - 2 hours 46 min ago
Hi, I saw various sort options in tkprof. sort=(prsela,exeela,fchela) sort=PRSDSK, EXEDSK, FCHDSK sort=EXECPU,FCHCPU sort=(exeela,fchela) I really want to get poorly performing queries on the top. and then i want to print 10 top most poorly written queries from trace file. PRINT = 10 tkprof a.trc limited_data sys=no explain=apps/ABC@DB sort=(prsela,exeela,fchela) print=10 Hence what is a right sorting option? And is one option is helpful for both Select & DML ( insert/update/delete) statements or need to use two sort options. Please can you provide some light. Thank Amey
Categories: DBA Blogs

Use Different Listener For Standby Duplication?

Michael Dinh - Sun, 2021-04-18 11:59

I know what you are thinking!

Why make this more complicate than it needs to be and is an ounce of prevention worth a pound of cure?

Oracle support started patching Oracle Exadata Cloud@Customer (ExaCC) environment.

After patching, the listener did not start because there were entries in the listener referencing database and oracle home that have been removed.

There are multiple database homes for the same database versions and this is how it was implemented.

Primary database (DB_ASHBURN) was used to create a second standby (DB_PHOENIX).

The primary database (DB_ASHBURN) was switchover to second standby (DB_PHOENIX)

DB_PHOENIX is now the new primary and DB_ASHBURN is the standby.

DB_ASHBURN (standby) was decommissioned and ORACLE_HOME was removed.

Unfortunately, listener.ora was not modified and failed to start after patching was completed.

Here is an example for LISTENER failed to start.

[oracle@ol7-112-dg1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:19:49

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0.4/dbhome_2/bin/oracle for SID DB_PHOENIX
[oracle@ol7-112-dg1 admin]$

---------------------------------------------------------
--- ORACLE_HOME may be in /etc/oratab but does not exist.
---------------------------------------------------------
[oracle@ol7-112-dg1 admin]$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/oracle/product/11.2.0.4/dbhome_1
/u01/app/oracle/product/11.2.0.4/dbhome_2

[oracle@ol7-112-dg1 admin]$ ls -ld /u01/app/oracle/product/11.2.0.4/
drwxr-xr-x. 3 oracle oinstall 22 Apr 14 18:29 /u01/app/oracle/product/11.2.0.4/
[oracle@ol7-112-dg1 admin]$

Having separate listeners, LISTENER started without issues

[oracle@ol7-112-dg1 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-APR-2021 16:32:24

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg1.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-APR-2021 16:32:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-112-dg1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk_DGMGRL" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol7-112-dg1 admin]$

What’s your preference, having separate listeners to play it safe or decommissioning the environment properly and completely?

Pretty Print JSON in PL/SQL

Jeff Kemp - Fri, 2021-04-16 01:11

I have been working with some code that uses JSON, sometimes fairly large documents of the stuff, and it’s often necessary to send this to the debug log (e.g. DBMS_OUTPUT) for debugging; however, the builtin functions that convert a JSON object to a string (or clob) return the JSON document in one big long line, like this:

{"surname":"Jones","name":"Bob","age":42,"alive":true,"children":[{"name":"Sally","age":10},{"name":"Terrance","age":8},{"name":"Ulyses","age":6}]}

To show this formatted, I added the following function using JSON_SERIALIZE with the “PRETTY” option, to my utility package:

function format_json (p_clob in clob) return clob is
    l_blob blob;
    l_clob clob;

    function clob_to_blob(p_clob clob) return blob is
        l_blob blob;
        o1 integer := 1;
        o2 integer := 1;
        c integer := 0;
        w integer := 0;
    begin
        sys.dbms_lob.createtemporary(l_blob, true);
        sys.dbms_lob.converttoblob(l_blob, p_clob, length(p_clob), o1, o2, 0, c, w);
        return l_blob;
    end clob_to_blob;

begin
    l_blob := clob_to_blob(p_clob);
    select JSON_SERIALIZE(l_blob returning clob PRETTY) into l_clob from dual;
    return l_clob;
end format_json;

Note that my function takes a CLOB, not a JSON object, because sometimes I receive the data already as a CLOB and I don’t want to require conversion to JSON before passing it to my formatting function.

Now, when I call this function:

declare
    l_json     json_object_t := json_object_t();
    l_children json_array_t := json_array_t();
    l_clob     clob;
begin
    l_json.put('surname','Jones');
    l_json.put('name','Bob');
    l_json.put('age',42);
    l_json.put('alive',true);
    l_children.append(json_object_t('{"name":"Sally","age":10}'));
    l_children.append(json_object_t('{"name":"Terrance","age":8}'));
    l_children.append(json_object_t('{"name":"Ulyses","age":6}'));
    l_json.put('children',l_children);
    l_clob := l_json.to_clob;
    l_clob := utility_pkg.format_json(l_clob);
end;

I get the following result:

{
  "surname" : "Jones",
  "name" : "Bob",
  "age" : 42,
  "alive" : true,
  "children" :
  [
    {
      "name" : "Sally",
      "age" : 10
    },
    {
      "name" : "Terrance",
      "age" : 8
    },
    {
      "name" : "Ulyses",
      "age" : 6
    }
  ]
}

Where to Put PostgreSQL in AWS

Pakistan's First Oracle Blog - Thu, 2021-04-15 22:44

When it comes to putting PostgreSQL database in AWS, you are spoiled for choice. There are 3 ways to do that:



1) Install and configure PostgreSQL on EC2 instance.

2) Amazon RDS for PostgreSQL

3) Amazon Aurora for PostgreSQL

You can watch the whole video here.

Categories: DBA Blogs

Radio radio button prompt shifted after upgrade to 20.2

Tom Kyte - Thu, 2021-04-15 10:26
We are upgrading Apex from 5.2 to 20.2. Our applications are using theme 26. (Of course we need to update the applications to theme 42, but for now this step is too big.) After the upgrade, the prompt of the region buttons is shifted to left (behind the buttons). See https://apex.oracle.com/pls/apex/f?p=18605:1. Can we easily solve this with an css-adjustment?
Categories: DBA Blogs

convert database server sysdate to GMT date and daylight saving aware

Tom Kyte - Thu, 2021-04-15 10:26
We need to code a function to convert database server sysdate to GMT date. So we use: alter database set time_zone='EST' to set dbtimezone, and then code something like: CREATE OR REPLACE FUNCTION FN_GET_GMT_DATE RETURN DATE IS v_dbtimezone varchar2(10); BEGIN select dbtimezone into v_dbtimezone from dual; RETURN NEW_TIME(SYSDATE, ltrim(rtrim(v_dbtimezone)), 'GMT'); END FN_GET_GMT_DATE; / But this is not daylight saving awarance. What's the best way to convert database sysdate to GMT date and also daylight saving aware?
Categories: DBA Blogs

Deploying Oracle RAC on Docker

Tom Kyte - Thu, 2021-04-15 10:26
Hello Ask-Tom-Team, I came across this Oracle documentation ?Deploying Oracle RAC on Docker?: https://www.oracle.com/technetwork/database/options/clustering/rac-ondocker-bp-wp-5458685.pdf Specifically the sentence: OracleRAC on Docker supports the <b>non-privilege</b> mode feature. This allows Oracle RAC to safely and securely run on a single hostor <b>multiple hosts without interference</b> from the other DockerContainers. Does it mean, I could install two Oracle RAC clusters (e.g. 2 node each, 4 total) on multiple containers on the same physical host without interference between them? Oracle RAC clusterware uses Linux-Root account taking over the entire physical host. With Oracle RAC Docker non-privilege mode feature, I guess that wouldn't be the case. Where do I find more information on this Oracle RAC Docker non-privilege mode feature? Does Oracle supports / certifies "RAC on Docker" for production systems? Thank you. Elio
Categories: DBA Blogs

Password Security During Logins in 2021

Tom Kyte - Thu, 2021-04-15 10:26
Greetings, I have a few questions regarding how Oracle by default encrypts the password when sent to the Oracle databases. (https://asktom.oracle.com/pls/apex/asktom.search?tag=password-security-during-logins&p_session=117601880297172) First, please confirm that Oracle by default also encrypts the password when the client uses a JDBC thin client to connect to the databases. Second, what kind of encryption algorithm (AES128, DES, etc.) does it use? Third, why doesn't the default encryption of passwords require the complexity of setting up SSL encryption for data in motion? The complexity I am referring to is the need to create various server and client certificates ( CA certificate, self signed, pass it to through SHA-2 algorithm), create wallets and setup several sqlnet.ora parameters on both client and server? Forth, do you think it is a good idea for Oracle Oracle to provide a simple on/off option to turn on the same default password encryption but for all data in motion? Lastly, if Oracle encrypts the password automatically, why does Oracle provide this document: "Step by Step Guide To Configure SSL Authentication (Doc ID 736510.1)?" "This article describes how to configure and use the Secure Sockets Layer (SSL) for Oracle Database User Authentication." If Oracle encrypts the password by default, then Oracle providing the document seems unnecessary. Thanks for your valuable insights. John
Categories: DBA Blogs

ORA-65040 when loading jar file into pluggable database with sys.dbms_java.loadjava or commandline tool loadjava

Tom Kyte - Thu, 2021-04-15 10:26
I want to load a jar file into the database. First I loaded it into the root container. loadjava -o -f -noverify -synonym -stdout -verbose -recursivejars -resolve -grant PUBLIC -user sys/password@CDB javax.mail.jar >\mail.log This also works, but I don't have access to it in the PDB. Trying to load the jarfile into the PDB fails with the error ORA-65040: operation not allowed from within a pluggable database. The same message comes when I use dbms_java.loadjava('-v -r -g PUBLIC /path/java/javax.mail.jar'); after switching into the PDB. How can I load jarfiles into a PDB or make them available there? Thanks for help Sigrid
Categories: DBA Blogs

How to exclude rows from outer join calculation but still include them in results

Tom Kyte - Thu, 2021-04-15 10:26
Hello Chris, Hello Connor. I have a view with a schedule of some kind of launches: <code>create or replace view v_schedule as select trunc(sysdate) + interval'1'minute * level as plan_date from dual connect by level <= 100000</code> And a log of actual launch dates: <code>create table t_launches as select trunc(sysdate) + interval'1'minute * level + mod(abs(dbms_random.random), 60) * interval'1'second as launch_date from dual connect by level <= 1000;</code> I want to join them like that: <code>create table t1 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule ) left join t_launches on (launch_date >= plan_date and launch_date < next_plan_date);</code> This works for about 10 seconds. But I have an extra piece of information: launch_date cannot be larger than sysdate(<i>trunc(sysdate) + interval'1'minute * 1000 in this example</i>). So for 99% of rows in v_schedule there is no point in even trying to join them with anything in t_launches. So a query like that works for about half a second: <code>create table t2 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule where plan_date <= trunc(sysdate) + interval'1'minute * 1000 ) left join t_launches on (launch_date >= plan_date and launch_date < next_plan_date) union all select plan_date, null from v_schedule where plan_date > trunc(sysdate) + interval'1'minute * 1000;</code> My question is: Is there any way to tell Oracle to filter rows from an outer join calculation but still include them in the results? I thought this should work, but it doesn't: <code>create table t3 as select plan_date, launch_date from ( select plan_date, lead(plan_date) over (order by plan_date) as next_plan_date from v_schedule ) left join t_launches on (plan_date <= trunc(sysdate) + interval'1'minute * 1000 and launch_date >= plan_date and launch_date < next_plan_date);</code> I would very much like to avoid union all because my actual query is much larger and complicated than this and union all would almost double its size. Besides, it does an extra pass over v_schedule.
Categories: DBA Blogs

Synchronize PK values

Tom Kyte - Wed, 2021-04-14 16:06
Hi! We would appreciate your advice about problem we have. We have two tables of the same structure in different DB (DB1 and DB2) . Rows are inserted into table in DB1, and then some of them (based on simple condition) are being prorogated to the table in DB2 via trigger using dblink. When rows are inserted into those tables different sequences are used to generate primary key values. So, all the data in rows being pushed between DB is identical except for PK values. Now, we need to synchronize PK values as well and we would like to drop sequence in DB2 and insert rows with incoming PK values. Is it possible to actualize PK values for already inserted rows in the table in DB2 or the only way in to drop and recreate the table ? Thank you!
Categories: DBA Blogs

sdo_geometry varrays and basic to securefile migration

Tom Kyte - Wed, 2021-04-14 16:06
Hi Tom, we're making plans to migrate from basicfile to securefile lob storage (Oracle Database 19c). The database also contains some spatial layers, in partitioned tables with a local spatial index. These tables/partitions were defined in Oracle 11 without explicitly specifying a storage clause for the sdo_geometry varray elements (sdo_elem_info and sdo_ordinates). As I understand from the Oracle docs (https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/introduction-to-large-objects.html#GUID-9B66BC56-A427-4C1B-B185-8E004CF4B2F7), these varrays are only stored as basicfile LOB's in case their size exceeds approximately 4kB. The varray elements of smaller geometries are stored as raw values inline. As we want to migrate to securefile lobs (using table redefinition), we have two options for the definition of the sdo_geometry varrays 1. WITH an explicit storage clause: <code> VARRAY "SPATIAL_COLUM"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB (CACHE ENABLE STORAGE IN ROW) VARRAY "PERC_GEOMETRIE_PERCEEL"."SDO_ORDINATES" STORE AS SECUREFILE LOB (CACHE ENABLE STORAGE IN ROW) ENABLE ROW MOVEMENT </code> 2. WITHOUT an explicit storoge clause. In this case only the larger geometry varrays will be stored as LOB's using securefiles. The other varrays will be stored inline as raw values. My question is what option is advisable in term of spatial query performance? Thank you. best regards
Categories: DBA Blogs

Log User Activities with a trigger

Tom Kyte - Wed, 2021-04-14 16:06
I am in the process of implementing a process in APEX. This process includes some roles. The process is initiated by a person who fills in forms in the first step. The data entered is stored in collections and entered into the corresponding tables after submission. This also initiates the next step of the process. Now to my question, I would like to display a kind of history of the completed steps after the completed forms have been submitted (i.e. after the first process step has been completed). I was thinking of a kind of log table. This summarises which action has been taken, who has taken the action, and what other important information has been entered in the table (e.g. which person responsible was selected in the form, etc., the ID of the table). <code> -- create log tbl create table form_submitted log_id number not null, Responsible_id1 number , Responsible_id2 number, Responsible_id3 number, Responsible_id4 number, added_date date, action varchar2(100); alter table form_submitted add constraint pk_log_id PRIMARY KEY (log_id); -- create sequence to autoincrement values create sequence seq_log_id start with 1 increment by 1 nocache nocycle; --create trigger to insert auto-incremented value create or replace trigger bi_form_submitted before insert on form_submitted for each row begin :new.log_id :=seq_log_id.nextval; end; </code> To achieve this, I thought of creating a trigger that would track the user's activities and enter the necessary information into the log table. <code> -- create trigger to track user activity and insert on logs table create or replace trigger trg_user_activity after insert or update or delete on tbl1 for each row begin if deleting then --insert data in tbl_logs insert into form_submitted (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action) values (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_deleted'); end if; if updating then --insert data in tbl_logs insert into form_submitted (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action) values (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_updated'); end if; if inserting then --insert data in tbl_logs insert into form_submitted (Responsible_id1,Responsible_id2, Responsible_id3, Responsible_id4,created_time, added_date, action) values (:new.Responsible_id1,:new.Responsible_id2,:new.Responsible_id3,:new.Responsible_id4, SYSDATE,to_char(SYSDATE,'HH:MI:SS PM'),SYSDATE, 'form_submitted'); end if; end; </code> This table I want then to display as a classic report in the history tab. Each time a user makes a change it will be logged and displayed. But each time in a different log tables. This means in the second step, another user has to make an action, this should as well be logged and been displayed in the history tab. The aim is to log and display every activity of each user in the process. I would like some help with the following three things: 1. how can I get the information I need for the log table( like Responsible_id1 ) from the other table (here tbl1). 2. different users edit the same table (which means that the next user adds data to the same table) but a different entry has to be made in a different log table. Can I bind the trigger to a user or to this specific process step. 3.After the first process step has been successfully completed, how can I display the current status (this means that the steps should not have to be carried out again the next time the user logs in but he should see in which step of the process he is at the moment)? ...
Categories: DBA Blogs

Deploying An Application To A GKE Cluster

Online Apps DBA - Wed, 2021-04-14 09:04

Clusters are essentially the backbone of the Google Kubernetes Engine system. All objects that are part of Kubernetes run atop clusters. These include all the containerized applications, for which the cluster acts as a platform. A cluster consists of at least one Control plane or cluster master machine, and multiple worker machines called nodes. Read […]

The post Deploying An Application To A GKE Cluster appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Flexible Network Load Balancing Now Available

Online Apps DBA - Wed, 2021-04-14 00:11

Network Load Balancer provides the benefits of flow high availability, source and destination IP addresses, and port preservation. It is designed to handle volatile traffic patterns and millions of flows, offering high throughput while maintaining ultra-low latency. Want to know more about Flexible Network Load Balancing? Read the blog post at k21academy.com/oci84 to learn more. […]

The post Flexible Network Load Balancing Now Available appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Loading a Double Quote enclosed csv file into Table where csv file has Newline character itself in the enclosed data

Tom Kyte - Tue, 2021-04-13 21:46
Hi Sir, I have a csv (Say Dummy.csv) file like the below : <i> "D_ID","D_DATA_A","D_DATA_B" "1","Normal Data","This is a Dummy Data" "2","Data with Comma","This is a Dummy Data, with Comma" "3","Data with New Line","This is a Dummy Data Along with New Line" "4","Data with Double Quote","This is a ""Dummy Data"" with Double Quote" "5","Data with all of the above","This is a ""Dummy Data "" with Double Quote, and Comma With Newline" "6","Test Data with Null","" "7","Data with really big dummy data","ABCD , EFGH "" IJKL MNOP"" QRST , UVWX"" ""YZ AB, ""CDEF"",""GHIJ KL . . MNOP, QRST "" UVWX" </i> Now I need to load this in a table say Dummy_Load table having the definition : D_ID NUMBER(2) D_DATA_A VARCHAR2(100) D_DATA_B VARCHAR2(1000) While using sqlldr for this with the control file as follows: <code> LOAD DATA INFILE '<root_path>/Dummy.csv' INSERT INTO TABLE Dummy_Load FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( d_id, d_data_a, d_data_b ) </code> all the rows are not getting loaded due to the new line characters throwing the below error in log <i> Record 5: Rejected - Error on table DUMMY_LOAD, column D_DATA_B. second enclosure string not present Record 8: Rejected - Error on table DUMMY_LOAD, column D_DATA_B. second enclosure string not present Record 9: Rejected - Error on table DUMMY_LOAD, column D_ID. no terminator found after TERMINATED and ENCLOSED field Record 12: Rejected - Error on table DUMMY_LOAD, column D_DATA_B. second enclosure string not present Record 14: Rejected - Error on table DUMMY_LOAD, column D_ID. no terminator found after TERMINATED and ENCLOSED field Record 16: Discarded - all columns null. Record 21: Discarded - all columns null. Record 22: Discarded - all columns null. Record 23: Discarded - all columns null. </i> What am I really missing here? Is it not possible to import a csv where newline is present in the data itself in table? Thanks in advance. Regards, Soumya
Categories: DBA Blogs

Sorting on columns with many data points having same value

Tom Kyte - Tue, 2021-04-13 21:46
We are trying to confirm the expected behavior of Oracle for how it handles sorting on a column where many of the data points are the same value. For example if I queried the following table and sorted on the Data column I might get changing result order on each query like the two iterations below. (First query) <code>| ID | Data | ----------------- | 1 | James | | 2 | James | | 3 | James |</code> (Second query) <code>| ID | Data | ----------------- | 3 | James | | 1 | James | | 2 | James |</code> We believe it is expected that this can happen and that Oracle does not guarantee the order of row for this type of query. Regardless,we know we can update the application to add a second sort on a unique column, but that is a huge development effort. So wanted to know if perhaps there is something we can do on the database side to avoid making large application changes.?
Categories: DBA Blogs

CSV

Jonathan Lewis - Tue, 2021-04-13 09:00

An old question with a little extra twist came up on the Oracle Developer forum recently – someone wanted to export a table into a CSV file but wanted to do a character set conversion at the same time because various “non-printable” characters were getting corrupted.

Looking at the code they had supplied they were running from SQL*Plus and were trying to use a select statement to generate a script that, for each row in a table, produced a statement of the form:

insert into tableX({list of columns}) values({list of values});

This worked reasonably well when they didn’t attempt to get a UTF8 output, but when they modified their client O/S environment to make SQL*Plus produce the desired UTF8 output the operation was much slower, increasing from about 1 minute to more than half an hour. (This may simply be the overhead of SQL*Net translating each line of output from the server character set to the client character set.)

Since the system in question was running on 12.2 I suggested a change of strategy so that they could at least take advantage of a built-in CSV generator to see how this behaved. Oracle extended the “set markup” command in 12c to include a CSV option:

set markup csv on delimiter {character} quote on

If this behaves nicely with the user’s attempt to get the output in their chosen characterset then a change in strategy to generate a “proper” CSV file and reload it through an external table, rather than generating and executing a list of “insert ….” statements might do a lot less work. (Unfortunately they had a requirement to generate the insert statements rather than adopting this bulk strategy – but I wasn’t going to let that stop me finishing this note.)

One detail to note with this markup option is that, unlike the “delimiter” character, the “quote” character cannot be specified and will always be the double-quote (ASCII character 34). If this character appears in any of the reported strings Oracle will “escape” it by duplicating it. Here’s a little example to demonstrate the effects – first a little data with a basic output (running 12.2.0.1):

rem
rem     Script:         markup.csv.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2020
rem

create table t1 (v1 varchar2(10), n1 number, n2 number, v2 varchar2(10));

insert into t1 values('abcdef',1,1,'qwer');
insert into t1 values('abc"def',2,null,'asdf');
insert into t1 values (null,3,3,null);

commit;

prompt  ==================
prompt  markup not yet set
prompt  ==================

select * from t1;


V1                 N1         N2 V2
---------- ---------- ---------- ----------
abcdef              1          1 qwer
abc"def             2            asdf
                    3          3

3 rows selected.

I’ve included one row with a double-quote in one of the columns, and a couple of rows with NULLs in various columns. Now I just switch markup to CSV using its default options:

prompt  ==================
prompt  Default CSV markup
prompt  ==================

set markup csv on
select * from t1;


"V1","N1","N2","V2"
"abcdef",1,1,"qwer"
"abc""def",2,,"asdf"
,3,3,

3 rows selected.


As you can see this has behaved nicely – every column is present (even when empty – judging by the number of delimiters) and character strings have been quoted with the double-quote character, and the stored double-quote in the second row has been duplicated. The default option for CSV should be nearly perfect for use as an extrernal table – though we probably want to get rid of column headings, page breaks, and feedback etc.

Before creating a suitable external table, let’s play around with a couple of variations for the options:


prompt  ===========
prompt  delimiter =
prompt  ===========

set heading off
set pagesize 0
set feedback off

set markup csv on delimiter =
select * from t1;

"abcdef"=1=1="qwer"
"abc""def"=2=="asdf"
=3=3=

prompt  ================================
prompt  quote off (delimiter remembered)
prompt  ================================

set markup csv on quote off
select * from t1;

abcdef=1=1=qwer
abc"def=2==asdf
=3=3=

Now to read back one of the outputs – I’ll pick the default output for this (but with headers etc. off to leave just three lines of data). Here’s how I can define my external table to read back the values from the file:

create or replace directory ext_tab as '/mnt/working/';
create or replace directory ext_log as '/mnt/working/';

create table ext(
        v1 varchar2(10), 
        n1 number, 
        n2 number, 
        v2 varchar2(10)
)
organization external(
        type oracle_loader
        default directory ext_tab
        access parameters (
                records delimited by newline 
                discardfile     ext_log:'exttab_dis.txt'
                badfile         ext_log:'exttab_bad.txt'
                logfile         ext_log:'exttab_log.txt'
        fields 
                csv 
                with embedded
                missing field values are null
        )
        location ('markup_csv.lst')
)
reject limit 10
;

select * from ext;

V1                 N1         N2 V2
---------- ---------- ---------- ----------
abcdef              1          1 qwer
abc"def             2            asdf
                    3          3

3 rows selected.

I’ve created a couple of directories to get started – linking a physical location to a logical name that I can use in the external table definition.

The key details that allow Oracle to read the file correctly appear in the “fields” specification: it’s a pity that the output from the default CSV markup doesn’t become the default expectation for the external table CSV option, but I needed to use the “with embedded” to adjust the duplicated “double-quote” characters and then add “missing field values are null” to cope with a null column at the end of the line.

Summary

With a simple call to “set markup csv on” (and a handful of other set {formatting} commands) it’s very easy to turn a table into a flat CSV file that is comma separated, (double)quote deliminted.

It’s then very easy – but requires two non-default settings – to read this flat file back into an Oracle database using “fields CSV” as the basic formatting directive.

Presenting At The “Oracle Horizon Breakfast Series” Throughout May in Canberra

Richard Foote - Mon, 2021-04-12 20:38
  If you’re lucky enough to be based close to sunny Canberra, Australia, please join me in cooperation with Oracle Corporation for the “Oracle Horizons Breakfast Series” I’m running throughout May 2021 at the Canberra Hyatt Hotel. Enjoy breakfast while I discuss some exciting Oracle Database related topics: 5 May  : Innovate with Oracle Database […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator