[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am using PL SQL (Oracle) to run a  have a query and when I copy the results into an Excel spreadsheet the "clm_no" ( looks like this "1.7282E+14" instead of the correct format "172820014002563".  I tried using "CAST(a.clm_no AS VARCHAR2(50)) as Claim_Number", but it did work for all of the fields.

Is there a way to make sure they clm_no's will copy correctly to the spreadsheet?  Below is the code:

select distinct cin, lname, fname, dob, bill_type, net_amt as Net_Amount, srv_from_date, srv_thru_date, clm82_payee_id as Payee_ID, clm82_chk_date as Check_Date,
  clm82_chk_num as Check_Number,
  npi, project_code, clm_no AS Claim_Number, patient_control as Patient_Control_Number, ppg, prv00_prv_name as PPG_Name  
  from phdp_claims a left join phdp_evr b on substr(b.clm_no,4) = clm_no
  left join cn114261.dhcs_update dhs ON substr(dhs.ccn, 4) = clm_no  
  where dhs.ccn is null
  and npi in ('xxxxxxxxxx')
  and srv_thru_date <= '31-dec-2017'
  order by cin;
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hi,

 any way to find out isolation level  of oracle 10g/12c ?

 will export feature export isolation level  setting from 10g and can be import to 12c  ?
0
Hi,

Hi Experts,

Not sure I have this correct as my source field has a space in it.  That might be problem, but not sure if I did this correctly.  I'm trying to change '100' to 'KABOOM'.  I'm not sure if 100 is stored as a CHAR or NUM though.  So I think that matters?


Case Statement:

CASE WHEN (Document Number) IN '100' THEN 'KABOOM ' ELSE (Document Number) END

Getting this error:    Oracle SQL Error:  ORA-00920: invalid relational operator

SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
1
Hi Experts,
create table BIT_BANK_CODES
(
COUNTRYCODE VARCHAR2(2),
ACCTNUMBER VARCHAR2(34),
ACCTIDENTIFIER  VARCHAR2(34),
GROUP_ID    VARCHAR2(15) NOT NULL,
BANK_ID VARCHAR2(20) NOT NULL,
DELETEFLAG  VARCHAR2(1) NOT NULL) ;


INSERT INTO BIT_BANK_CODES VALUES('SG','22205251117',null,'testgrp','CBLSGS0XXX','N');
INSERT INTO BIT_BANK_CODES VALUES('SG','0100879756',null,'testgrp','CBLSGS0XXX','N');

select bank_id from BIT_BANK_CODES where group_id='testgrp' and (acctnumber='0100879756' OR acctidentifier='0100879756');

Open in new window


I HAVE TO WRITE SAME QUERY USING FUNCTION
THIS FUNCTION SHOULD HAVE THREE INPUTS -->group_id,acctnumber,acctidentifier
AND IT WILL RETURN bank_id

I HAVE TO CREATE A PACKAGE-->THEN I HAVE TO CREATE A FUNCTION



created package:
================
create or replace
PACKAGE PKG_INF_BANKCODE AS

  FUNCTION GET_BIT_BANKCD(group_id IN varchar2,acctnumber IN varchar2,acctidentifier IN varchar2) RETURN  varchar2;

END PKG_INF_BANKCODE;

created function:
=================
create or replace FUNCTION GET_BIT_BANKCD(in_group_id IN varchar2,in_acctnumber IN varchar2,in_acctidentifier IN varchar2)
RETURN  varchar2
IS BANKCODE VARCHAR2(20);
BEGIN
select bank_id INTO BANKCODE from BIT_BANK_CODES where group_id= in_group_id
and (acctnumber=in_acctnumber OR acctidentifier=in_acctidentifier);
RETURN BANKCODE;
END GET_BIT_BANKCD;

CAN SOME HELP ABOVE CODE IS CORRECT?
DO I NEED TO CREAT PACKGAGE BODY?
HOW CALL THIS PACKAGE and function BY PASSING INPUTS
I AM USING SQL DEVELOPER.
0
Hi Expert,

I wants to archive and purge my table data based on following condition, could you please guide me how to do this using rowid .

--This is based on create_daytime column
Archive_criteria  --365*3    
Purge_criteria  -- 365*8    

Please let me know if you need more info.

Thanks in Advance
Mihir
0
Hi Expert,

I have a issue with a job, i'm trying to fetch and insert into common_error_log table.

FYI.. I have mentioned the code chunk.
This is failing before insert into Common_Error_Log table as the cursor has no record to fetch .
   OPEN C_GET_MAX_RETRY;
   FETCH C_GET_MAX_RETRY INTO L_max_retry_count;
   CLOSE C_GET_MAX_RETRY;
   
   IF L_max_retry_count IS NULL THEN
      O_error_message :=  'INVALID_MAX_RETRY_COUNT';
      RETURN FALSE; 
   END IF;

Open in new window


But after checked the value from table it's there, could you please throw an light if entry is there than why going this failure.

Please find the following attachment select stmt for cursor.

CEL
Please let me know if there need more info.
Thanks,
Mihir
1
Hello,

I search a script to compare number of lines on all tables between two schémas.

Thank you

Best regards
0
https://community.oracle.com/thread/686026

Extracted fr above, "According to documentation, a Private Database Link is more secure than a public or global link";
we have less critical systems that use DB links to a very critical system (Oracle).

I'm new to the organization & DBA told me there's a number of "Public DB links" including some with RW & without
SSL cert.

Q1:
Is it a lot of work or feasible to convert from public to private?


Q2:
Can self-signed SSL certs be used?


Q3:
Any other mitigations if we leave it as public?
0
I have stored my xml in a table. Select and insert in a new table works.

I save the xml file with sqloader as xmltype as HUGECLOB. I can select with xpath the data and insert it in a new table.

My Table structure is:
MAINPART | MAINPART2 | MAINPART3 | MAINPARTEXTENSION1 | MAINPARTEXTENSION2 | MAINPARTEXTENSION3 | MAINPARTEXTENSION4

I can map everything from the xml file except the mainpartextension. The select gives me a singleton error. How can I handle with Select or PL/SQL with a Loop or another xpath xpression to laod the xml tag MAINPARTEXTENSION into MAINPARTEXTENSION1 , MAINPARTEXTENSION2, MAINPARTEXTENSION3,....

Please help.
0
https://www.michalsons.com/blog/what-is-a-national-critical-information-infrastructure/17701
https://publicwiki-01.fraunhofer.de/CIPedia/index.php/Critical_Information_Infrastructure

I have to draft a guideline for systems that interface with a CII system & need inputs:
currently, the interfaces concerned are limited to 3 types only:

1. files transfer
============
I can only think that the generally practices ie:
 a) encryption of data in transit (eg: using sftp instead of ftp/mapping a drive or NFS)
 b) encryption of data at rest if it's sensitive (tampered with)

2. API
=====
how do we secure these (in particular APIs using microServices)??
I've heard of API needs to be certified so before requesting for it, need to be certain
else applications developers may question its relevance/usefulness

3. DBLink
========
Those sqlconnect  esp Oracle links to extract / update data.
Will need to define if the non-CII system is
   a) updating into CII, will have to be extra stringent but how?
   b) extracting from CII, just encrypting the sql calls
 

Oracle databases, weblogic are involved in the critical systems
while the less-critical systems may be Windows, Linux on
various apps (including mobile apps).

Editing thread to add Oracle as it relates to DBLink.
0
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Hi,
I have a temp table like below with clause.

I want to create a procedure where we should have the ability to

do execute sp_employee (enter empid '')
and below code pass that  parameter in where employee_id  ='000006190'

I need help in store procedure.

WITH T_employee  (employe_ALL)

     AS (

select  distinct employee_id as employee1 from employee_1

union

select  distinct employee_id as employee1 from employee_2

) A

where employee_id  ='000006190'

)

 

SELECT

  SUBSTR( tt. employe_ALL,2,3) AS SEQ_EMP,

                CASE

                   WHEN SUBSTR (tt.employe_ALL,1, 1) = 1

ELSE

                         'no valid info in em[;oyee****'

                           END

                   AS employe_ALL

                  FROM T_employee  tt

Open in new window

0
the attached is the sample data im using.

I need some sql/stored procedure that:
searches for all tables that contain a named column,
queries those tables to see if any of the rows in the named column have a specific value.
outputs the result into a new table.


I’m using this  query to look for all tables that contain the column BASE_ID.

SELECT table_name,
column_name
FROM   all_tab_cols
where
column_name  =  'BASE_ID'

Open in new window

this outputs a number of tables and columns. Using the sample data, this query would return 5 rows.

img0
I then want to run a query that lists all of the tables that have row where the base_id is a certain value (in this example, BASE_ID = 100001), and a column with counts the rows that meet the condition

the sql would probably be something like:

Select Count(*), 'ANT_GIS_PROCESS_ERROR' As Table_Name, 'BASE_ID' As Column_Name From Ant_Gis_Process_Error Where Base_Id = 100001 Union
Select Count(*), 'Alternative_Allocations_Bases' As Table_Name, 'BASE_ID' As Column_Name From Alternative_Allocations_Bases Where Base_Id = 100001 Union
Select Count(*), 'Ant_Report_Selection' As Table_Name, 'BASE_ID' As Column_Name From Ant_Report_Selection Where Base_Id = 100001 Union
Select Count(*), 'Appeals_Dw' As Table_Name, 'BASE_ID' As Column_Name From Appeals_Dw Where Base_Id = 100001 Union
Select Count(*), 'CHOICES_BCK' As Table_Name, 'BASE_ID' As Column_Name From Choices_Bck Where Base_Id = 100001

Open in new window



The output would be something like:

img2

I would like this result to go into a table that looks like the above output.
sampledata.xlsx
0
Hello Expert,

Could anybody please through light on following query. How json table works here !

SELECT ext.legacy_id        legacy_id,
                               ext.record_type      record_type,
                               ext.tran_type        tran_type,
                               ext.corp_id          corp_id,
                               ext.mdse_co_id       mdse_co_id,
                               json.loc             location,
                               ext.sty_cd           sty_cd,
                               ext.sty_clr_cd       sty_clr_cd,
                               ext.mkt_cd           mkt_cd,
                               ext.unit_price       unit_price,
                               ext.effective_date   effective_date
                          FROM risprc.ris_legacy_price_stg ext,
                               json_table('['||ext.excep_str_nbr||']', '$[*]' COLUMNS loc  NUMBER PATH '$') json,   --How it works?
                               TABLE (CAST (L_loc_tbl AS NUMBER_TBL)) tbl
                         WHERE ext.record_type   = LP_corp_rec_type
                           AND ext.excep_str_nbr IS NOT NULL
                           AND ext.location      = VALUE(tbl) ;

Open in new window

0
I have 2 tables  xxeis_Cost_function
it has the columns

employee_name, cost_center, payroll_group function  


and table2 xxeis_job_function
has column

cost_center, payroll_group, function

I have to find the employee
for the payroll_group, cost_center in second table,  dosent have the correct function value in the first table


table 2 has the correct fiunction value

and display the correct function_value and the employee name and wrong function_value

my query

select a.employee_full_name, substr(a.payroll_name,1,3) payroll_group, a.employee_number , a.pj#job_function
, regexp_substr(organization_name, '[0-9.]+') cost_center from xxeis_cost_function a
           where not exists( select null from xxeis_job_function b
                where substr(a.payroll_name,1,3)= b.payroll_name
                 and  regexp_substr(a.organization_name, '[0-9.]+')=b.cost_center
                 and a.pj#job_function=b.function )
0
Hi,

as we found data type after convert from Oracle to MariaDB there are diff, I'd like to know:

1) does MySQL/MariaDB has DATE only type ? Why MariaDB use datetime? MariaDB only has datatime?
2)  Why varchar2(75) become TEXT
3) why varchar2(100) become TEXT

any information about what is the space occupied by both type ?
0
Keep getting invalid identifier when running the following statement. Need to color rows based on criteria
within the SQL code in a third party dashboard.

With N1 As (
SELECT 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
  BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, 
  DEPARTMENT.DEPT_NAME, 
  BA_VIEW_SHIPPING_ORDERS.PN, 
  BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE,
  BA_VIEW_WO_SUMMARY.WO_TYPE,
  TRUNC(CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - BA_VIEW_WO_SUMMARY.NET_TAT) AS WO_DAYS_LEFT, 
  TRUNC(BA_VIEW_SHIPPING_ORDERS.DUE_DATE - SYSDATE) AS SO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'RO' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE NULL END) AS RO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'WHS' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE NULL END) AS WHS_DAYS_LEFT
FROM 
  BA_VIEW_SHIPPING_ORDERS 
  LEFT OUTER JOIN BA_VIEW_WO_SUMMARY ON BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = BA_VIEW_WO_SUMMARY.WO_NUMBER 
  LEFT OUTER JOIN DEPARTMENT ON BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = DEPARTMENT.DPT_AUTO_KEY 
WHERE 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%' 
  AND BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED')
  )
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||NVL(RO_DAYS_LEFT,'')||NVL(WHS_DAYS_LEFT,'')) 

Open in new window

1
Hi there,
Wondering if someone can help me out here...
We're in the middle of implementing a new financial system (JDE E1 on Oracle), which will be integrated with our work management application (Maximo). So far, I created a schema for Maximo (MAXIMO) with a mapping table (MAPITM).  I created a role FC_MAXIMO with SELECT, INSERT, UPDATE, DELETE privileges on MAPITM. There are 2 roles in JDE (JDEADMIN, JDEUSER), and I added them to FC_MAXIMO. There are also 4 users: JDE, JDEPROXY, JDESRV, TESTDTA and added them to FC_MAXIMO. I created a Public Synonym for the MAPITM (but found out later on the 'Net, it really isn't 'best practice').

This is where I'm a little out of my expertise (I don't have a lot of experience in Oracle...just enough to be dangerous...LOL). The SME (subject matter expert) wants me to create a view of the MAPITM table in the TESTDTA schema. After, he wanted the 2 roles (JDEADMIN, JDEUSER) to have access to the view. I told him I don't think you can assign views to Roles, only to Users (I could be wrong). So, he wanted the users: JDE, JDEPROXY, and JDESRV to have access to the view in TESTDTA. This is where I'm lost.

I'm wondering if I'm on the right path, or just completely out to lunch. What would be 'best practise' in this situation? Is it necessary to use a View? I would like to keep MAXIMO table(s) separate from JDE, but need multiple Roles/Users to access the MAXIMO schema.

Hope this clarifies things, and please let me know if you need …
0
Migrating Oracle procedures which has JSON data as input and business validation to insert/ update/delete tables to MariaDB.

Can we use SQL_MODE to ORACLE and use the same procedure ? Or we need to rewrite it again.

Like few differences where we use JSON_PARSER.PARSER(<<my input JSON>>) or JSON_EXT.GET_NUMBER / JSON_EXT.GET_STRING in Oracle to get the values for specific keys. So can we directly use the same procedure to work on Mariadb by setting the mode or we need to rewrite the entire procedure.
0
How I can prevent the results of DAYS_LEFT from adding two zeroes to the end of the value(screenshot attached)? here is the statement I am using:
With N1 As (
SELECT 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
  BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
  BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, 
  DEPARTMENT.DEPT_NAME, 
  BA_VIEW_SHIPPING_ORDERS.PN, 
  BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
  BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, 
  TRUNC(CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - BA_VIEW_WO_SUMMARY.NET_TAT) AS WO_DAYS_LEFT, 
  TRUNC(BA_VIEW_SHIPPING_ORDERS.DUE_DATE - SYSDATE) AS SO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'RO' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE 0 END) AS RO_DAYS_LEFT, 
  TRUNC(CASE WHEN ORDER_TYPE = 'WHS' THEN sysdate - BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE ELSE 0 END) AS WHS_DAYS_LEFT
FROM 
  BA_VIEW_SHIPPING_ORDERS 
  LEFT OUTER JOIN BA_VIEW_WO_SUMMARY ON BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = BA_VIEW_WO_SUMMARY.WO_NUMBER 
  LEFT OUTER JOIN DEPARTMENT ON BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = DEPARTMENT.DPT_AUTO_KEY 
WHERE 
  BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%' 
  AND BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED')
  )
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||RO_DAYS_LEFT||WHS_DAYS_LEFT) Days_Left
From N1

Open in new window

screenshot
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I have a image and i want to upload it .

I have created a table and given datatype clob.

so few question

1)how can i upload the image in database.
2)After uploading how can i query in database to see image.

Thanks
0
Hi Expert,

I have tale with following data set.
 data_file
Looking o/p with following format?
outputPlease let me know if there need more info.

Thanks.
0
Looking for a compelling reason to continue with my current database upgrade choice.  Currently we are proceeding down the path of upgrading from Oracle RAC database 12.1 to 12.2c, rather than the much talked about Oracle 18c.  Apparently there was much fanfare at OOW and our J2EE Software Developer are very interested.  However, I have already start my investigation/research and practice with Oracle Database 12.2c.  How can I persuade folks that 12.2 is just as a good direction? Need to be able to make really good argument.
0
I have data in on column
Column 1 This is test data. / bbccff
I want two columns now
Column1 This is test data.
Column2 bbccff
0
Experts,
A simple query that returns a number stored as a string.
For some, there will be no matching row found
As this value will later be used in division, I want it to return 1 by default

Neither NVL or COALESCE are returning the 1. I suspect this is the difference between a null column and No matching record.
Not sure how to correct this.

select 
COALESCE(ALT_INVT_REP_CODE,'1'),
NVL(ALT_INVT_REP_CODE,1)
from M_Item_D3 
where Comp_Code = 'A1' and Cust_code = 'ARCINT' and ALT_INVT_REP_TP_CODE = 'UNIT'
and Item_code = 'XMC613'

Open in new window

0
Need help figuring out where ba_wo_excusable_delay comes from.

select wodates2."WO_NUMBER",wodates2."PN",wodates2."DESCRIPTION",wodates2."WOO_AUTO_KEY",wodates2."OPEN_FLAG",wodates2."SHOP",wodates2."CUSTOMER_CODE",wodates2."CUSTOMER",wodates2."CUST_TYPE",wodates2."CONTRACT_TYPE",wodates2."CONTRACT_NUMBER",wodates2."SHOP_TYPE",wodates2."WO_TYPE",wodates2."WORK_PERFORMED",wodates2."SYSCM_AUTO_KEY",wodates2."ACCT_COMPANY",wodates2."STATUS",wodates2."RECEIVED_DATE",wodates2."ENTRY_DATE",wodates2."CLOSE_DATE",wodates2."DATE_MOVED_TO_QUOTING",wodates2."CAA_DATE",wodates2."CLOSE_DATE_ORIG",wodates2."SHIP_DATE",wodates2."SHIP_HOLD",wodates2."FIRST_QUOTE_SENT",wodates2."LAST_QUOTE_APPROVED",wodates2."RO_NUMBER",wodates2."DUE_DATE",wodates2."MANUAL_ECD",wodates2."SO_NUMBER",wodates2."EXCHANGE_DATE",wodates2."EXCHANGE_TYPE_CODE",wodates2."CONTRACT_TAT",wodates2."TAT_START_DATE",wodates2."TAT_STOP_DATE",wodates2."TAT_STOP_DATE_CUST"
,round(nvl(tat_stop_date,sysdate)-tat_start_date) gross_tat
,ba_wo_excusable_delay(woo_auto_key,null) excusable_delay
,round(nvl(tat_stop_date,sysdate)-tat_start_date)-nvl(ba_wo_excusable_delay(woo_auto_key,null),0) net_tat
from (
select wodates.*
,trunc(case when received_date is null then trunc(entry_date) else case when received_date<entry_date then trunc(received_date) else trunc(entry_date) end end) tat_start_date
,trunc(case when work_performed is null or work_performed='SCRAP' then close_date_orig else case when caa_date is not null then 

Open in new window

0

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.