How to filter data based on difference of two date Column when date or timestamp values are given in Traditional Chinese format.(in Oracle)

 Some of the Chinese Date format that should be inside the column where we do the filtering.

select sysdate from dual

10-6月 -2021 is 10th of June 2021

22-10月 -20

24-9月 -20 02.42.58.000000000 上午

453642 07-12月-20 06.54.59.000000000 上午 07-12月-20 06.58.00.000000000 上午

We can filter based on the below SQL:

---One way is to convert the Traditional Chinese date to English calender date and compare.

select * from USER_DET_TABLES where  Start_Datetime between to_date('22-OCT-20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ') and to_date('25-FEB-21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ')

--Other way is to directly pass the Traditional Chinese date in To_Date() and use NLS_DATE_LANGUAGE

select * from jobstatus_users_tab where  Start_Datetime between to_date('22-10月 -20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ') and to_date('24-2月 -21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ')

==

Specifying NLS Parameters in SQL Functions and Valid Use case of NLS parameters in SQL functions in Oracle

NLS parameters are specified in SQL functions as 'parameter = value'. For example:

'NLS_DATE_LANGUAGE = AMERICAN'

The following NLS parameters can be specified in SQL functions:

NLS_DATE_LANGUAGE

NLS_NUMERIC_CHARACTERS

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_DUAL_CURRENCY

NLS_CALENDAR

NLS_SORT

Table: SQL Functions and Their Valid NLS Parameters shows which NLS parameters are valid for specific SQL functions.

SQL Functions and Their Valid NLS Parameters

SQL Function Valid NLS Parameters

TO_DATE              NLS_DATE_LANGUAGE, NLS_CALENDAR

TO_NUMBER      NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY,

TO_CHAR         NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR

TO_NCHAR         NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR

NLS_UPPER              NLS_SORT

NLS_LOWER                  NLS_SORT

NLS_INITCAP                 NLS_SORT

NLSSORT                          NLS_SORT

========

How to resolve issues while importing a DACPAC file during cloud migration which fails with kind of error Error SQL72014: or Error SQL72045: below:

 Many of the cloud migration issues are related to dacpac import for SQL server DB be it in Azure or AWS.

Below is one of them

*** Could not deploy package.

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15007, Level 16, State 1, Line 1 'XYZReporting' is not a valid login or you do not have permission.

Error SQL72045: Script execution error.  The executed script:

CREATE USER [XYZReporting] FOR LOGIN [XYZReporting];

Just import the data with excluding the objects using the below switch:

PS C:\Program Files\Microsoft SQL Server\140\DAC\bin> .\sqlpackage /a:Publish /sf:C:\temp\db_test.dacpac /tsn:abxyz.cvvulhyblrdi.us-west-2.rds.amazonaws.com /tdn:demofin /tu:admin /tp:******  /p:ExcludeObjectTypes='Users;Logins;DatabaseRoles;RoleMembership;ServerRoleMembership;ServerRoles'

How to resolve ORA-12516 TNS:listener could not find available handler with matching protocol stack?

We see the error related to Listener refused the connection with following other error message as below:

ORA-12516,TNS:listener could not find available handler with matching protocol stack.
or 
Status:Failure -Test failed:IO Error: Got minus one from read call.

Usually we face the above error when number of process is not sufficient in the initialization parameter file to create additional connections.

Resolution:

Check the current process limit in V$parameter view

Connect to the correct DB or PDB if you are running 12c or above if it is a pluggable DB;

1. Alter system set processes=<new limit> scope=spfile;

2. Bounce the DB or PDB.

3. Restart the listener services.

How to resolve ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.

From Oracle version 12c onwards: -

Initially It seems the problem is related to Editioning feature of the object in the db is not enable or the schema user is not created with edition enable.
However it could be a related to container and non container DB.
This issue will not come where CDB=NO
In those cases where CDB=YES it will come and seems to be a bug.

Just Check:

SQL> select name,dbid,con_dbid,cdb,con_id from v$database;


NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
CDB1      1000678150 1000358150 YES          0


If CDB is yes means its a container DB and installer will fail.

 SQL> select name,dbid,con_dbid,cdb,con_id from v$database;

 NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
ORCLXYZ  74410566 2774410566 NO           0


If CDB is NO issue will not come.

How to resolve ORA-01400: cannot insert NULL into ("ABUSER"."DOMAINS"."ID")

How to resolve ORA-01400: cannot insert NULL into ("ABUSER"."DOMAINS"."ID")
[Failed SQL: INSERT INTO ABUSER.Domains (Name, RowVersion) VALUES ('Default Domain', '1')]

If this issue is coming before Oracle 12c(11g) then it could be an issue with the sequence.
Try to create the sequnce on the right column.

Post or from Oracle 12c first check the alert log,it will throw an exception of creating temp tablespace size.
Unable to extend temp tablespace...
Initially It seems the problem is related to Domain index.
However it could be a related to container and non container DB.
This issue will not come where CDB=NO
In those cases where CDB=YES it will come and seems to be a bug.

Just Check:

SQL> select name,dbid,con_dbid,cdb,con_id from v$database;


NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
CDB1      1000678150 1000358150 YES          0


If CDB is yes means its a container DB and installer will fail.

 SQL> select name,dbid,con_dbid,cdb,con_id from v$database;

 NAME            DBID   CON_DBID CDB     CON_ID
--------- ---------- ---------- --- ----------
ORCLXYZ  74410566 2774410566 NO           0


If CDB is NO issue will not come.

ERROR in alert log: Shared memory area is accessible to instance startup process prior to instance startup operation

Applicable to Oracle DB  Release 12.1.0.2.0.

Developer when connecting to a PDB service through SQLdeveloper may see the error startup shutdown in progress after restart of the Database in Oracle 12c or afterwards.

Issue applicable if we don't save the state of the DB.

Usually from Oracle 12c if we don't save the state of a PDB database after the restart PDB may not come up to the same state.

In that case we may face this issue.

Resolution:

SQL> connect to the DB.


Connected.

Check the status of the PDBs.

SQL> select con_id, name, open_mode, total_size from v$pdbs;

    CON_ID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY   838860800
         3 PDBCR                          MOUNTED             0

SQL> alter pluggable database PDB$SEED CLOSE IMMEDIATE;
alter pluggable database PDB$SEED CLOSE IMMEDIATE
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

Open the PDB database

SQL> alter pluggable database PDBCR open;

Pluggable database altered.

Again check the PDB status

SQL> select con_id, name, open_mode, total_size from v$pdbs;

    CON_ID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY   838860800
         3 PDBCR                          READ WRITE 4229955584

How to read value from sys_refcursor variable

How to read value from sys_refcursor variable?

Developer often write functions which return type is sys_refcursor.

To directly read the value of the returned function we can use the below method:-

create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct ADD_MONTHS(SYSDATE, -365) as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW
            union all
            select distinct ADD_MONTHS(SYSDATE, -365)+1 as "day",
            COOL_id, COOL_name,MATION_id
            from QA.PQRS_STATS_VIEW where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;


create or replace function TESTCALENDERDAYS return sys_refcursor is
rc sys_refcursor;
begin
open rc for select distinct to_char(ADD_MONTHS(SYSDATE, -365)) as "day",COOL_id, COOL_name,MATION_id from TESTCALENDERDAYS_MV2
            union all
            select distinct to_char(ADD_MONTHS(SYSDATE, -365)+1) as "day",COOL_id, COOL_name,MATION_id
            from TESTCALENDERDAYS_MV2 where ADD_MONTHS(SYSDATE, -365)+1 < sysdate;
return rc;
end;

select to_date(day),COOL_ID,COOL_NAME,MATION_ID from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTCALENDERDAYS())
columns
DAY date  PATH 'DAY',
COOL_ID PATH 'COOL_ID',
COOL_NAME PATH 'COOL_NAME',
MATION_ID PATH 'MATION_ID'
));

select * from  TESTCALENDERDAYS_MV2 from dual





select TESTF() from dual
select TESTCALENDERDAYS() from dual

select * from (select * from xmltable('/ROWSET/ROW'
PASSING xmltype(TESTF())
columns
ID  PATH 'ID',
COMPANY PATH 'COMPANY'
));

select * from TESTCALENDERDAYS_MV2

CREATE TABLE "SWARNASHIS"."TESTCALENDERDAYS_MV2" 
   ( "day" DATE, 
"COOL_ID" NUMBER(38,0), 
"COOL_NAME" NVARCHAR2(512), 
"MATION_ID" NUMBER(38,0)
   )
   tablespace users

insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-2,12,'ashisswarn',13);
insert into TESTCALENDERDAYS_MV2 values(sysdate-3,13,'ashisswarn',14);
insert into TESTCALENDERDAYS_MV2 values(sysdate-4,14,'ashisswarn',15);
insert into TESTCALENDERDAYS_MV2 values(sysdate-5,15,'ashisswarn',16);
insert into TESTCALENDERDAYS_MV2 values(sysdate-6,16,'ashisswarn',17);
insert into TESTCALENDERDAYS_MV2 values(sysdate-7,17,'ashisswarn',18);
insert into TESTCALENDERDAYS_MV2 values(sysdate-8,18,'ashisswarn',19);
insert into TESTCALENDERDAYS_MV2 values(sysdate-9,19,'ashisswarn',20);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);
insert into TESTCALENDERDAYS_MV2 values(sysdate-1,11,'ashisswarn',12);

commit


This works for fetching:

select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))

====

select day,COOL_id from (select extractvalue(column_value,'/ROW/day') day,
extractvalue(column_value,'/ROW/COOL_ID') COOL_id,
extractvalue(column_value,'/ROW/COOL_NAME') COOL_name,
extractvalue(column_value,'/ROW/MATION_ID') MATION_id
--     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(TESTCALENDERDAYS()))) where COOL_id