[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

hi,

I am converting oracle table structure and data to mariaDB V10.3, using tools https://www.convert-in.com/oracle-to-mysql.htm, and the log say there are table can't be convert, let's see what it is:

1)
Table 'AQ$_QUEUES': column 'SUBSCRIBERS' has unsupported type 'AQ$_SUBSCRIBERS', skip it

2)
Table 'ROLLING$STATISTICS': column 'VALUEINT' has unsupported type 'INTERVAL DAY(3) TO SECOND(2)', skip it

3)
Table 'SCHEDULER_JOB_ARGS_TBL': column 'ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it

4)
Table 'SCHEDULER_PROGRAM_ARGS_TBL': column 'DEFAULT_ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it

is ANYDATA a customer defined type ? what type should it be when port to MariaDB ?

what type should this be : INTERVAL DAY(3) TO SECOND(2) when port to MariaDB ?
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.

Need to combine WO_DAYS_LEFT, SO_DAYS_LEFT, RO_DAYS_LEFT, RW_DAYS_LEFT columns into one column named DAYS_LEFT for corresponding record.
How would I do this?

SELECT        dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, dbo.DEPARTMENT.DEPT_NAME, dbo.BA_VIEW_SHIPPING_ORDERS.PN, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL 
                         THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - dbo.BA_VIEW_WO_SUMMARY.NET_TAT AS WO_DAYS_LEFT, DATEDIFF(DAY, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.DUE_DATE, GETDATE()) AS SO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REPAIR_ORDERS.SM_ENTRY) 
                         AS RO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REWORK_RO.SM_ENTRY) 
                         AS RW_DAYS_LEFT
FROM            dbo.BA_VIEW_SHIPPING_ORDERS LEFT OUTER JOIN
                         dbo.BA_VIEW_REWORK_RO ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REWORK_RO.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_REPAIR_ORDERS ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REPAIR_ORDERS.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_WO_SUMMARY ON 

Open in new window

0
Hi Team,

I have a written a procedure which is execute in package . But the procedure is not working .
Could you please below procedure and help me. And the data is not loaded into the Iaml_Custphon_cdds_stg.
procedure P_custphon_cdds
AS
  /* Truncate Staging Tables*/

Begin
  --Reload Iaml_Custphon_cdds_stg
   EXECUTE IMMEDIATE 'TRUNCATE TABLE Iaml_Custphon_cdds_stg';

Insert into Iaml_Custphon_cdds_stg
  (cust_id,
   phone_rec_number,
   phone_purpose,
   phone_number,
   phone_ext,
   source_system,
   country_of_phone_number)

   select distinct
   ltrim(rtrim(p.customer_identifier)) as cust_id,
    ROW_NUMBER () OVER (PARTITION BY p.customer_identifier,p.address_purpose ORDER BY p.customer_identifier ASC) as phone_rec_number,
    decode (ltrim(rtrim(p.address_purpose)),'Party Registered Address','B','Corresponding Address','H' )as phone_purpose,
  ltrim(rtrim(p.phone_number)),
  ltrim(rtrim(p.phone_extension)) as phone_ext,
   'CDS',
   get_country_cde(p.country_of_phone_number)
 from CDDS_CUST_ACCT_TEMP p
 where p.customer_identifier is not null
-- and p.account_type1!='FCLTATN'
  group by     p.customer_identifier,
    p.address_purpose,
p.phone_number,
  p.phone_extension ,
 (decode( ltrim(rtrim(p.source_system)) ,'ICON_PARTY','CDS')),   p.country_of_phone_number  ;


EXCEPTION WHEN OTHERS
THEN dbms_output.put_line(SQLERRM);


commit ;

Open in new window

0
By using NVL() IS it possible to get the default value as 0.00

when i try to use the below function i am getting val as 0 only
NVL(charges.chrg_margin_rate,0.00)

could some one suggest how to get the val as 0.00
0
I have two tables:

Tbl1:
ColKey1 ColData

ColKey1 will have two categories of values - ColKey1_Old and ColKey1_New.

Tbl2:
ColKey1 ColData2_1 ColData2_2 ColData2_3. Tbl2 does not have a primary key, however, all 4 columns combined make each row unique.

I need to update Tbl2's ColKey1 by joining Tbl1 and Tbl2 on Tbl1.ColKey1_Old = Tbl2.ColKey1. So the statement will be something like this:

update Tbl2, Tbl1
set ColKey1 = Tbl1.ColKey1_New
where ColKey1 = Tbl1.ColKey1_Old

I tried using MERGE but it won't allow updation of a column that is in the WHERE clause. I would love to hear some ideas.
Thanks to anyone who responds.
0
Hi,

I have an employee_ref_id column and can have multiple entry in address table.  When employee do any chnage a new row insert in address table and new row in employee table. I have to count how many times he changed his address.

Example: employee : JOHN
employee_ref_id in year 2017 is : 100
with this id, he have an address in address table suppose "Newyork"

after that in 2018 he changed address 4 times
for same employee john , 4 ref_id created 101, 102,103,104
address : washington, newyork, newyork, washington

basically he chnaged 3 times.since 2017. first newyork to washington and then washinton to newyork and in last again newyork to washington.. so I have to give count 3.
0
Hello,

I'm trying to write a select where I can get part numbers sold in the last hour. The thing is, I'm having difficulties in getting the results right. Here's the info...

select c.partnumber,o.timeplaced,count(o.orders_id) AS count
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and o.timeplaced > current_timestamp - interval '1' hour
group by c.partnumber,o.timeplaced;

This all works fine. The issue is that since timeplaced is a timestamp, it goes down to the millisecond. Therefore, I'm not able to get the grouping I'm looking for. I really only want orders placed in the last hour. I really don't care about doing any ordering, although being able to order by the count would be nice.

How can I run my query but only get results in the last hour so the grouping will be better? For example, in my current query, the results look something like this...

PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 7:22:23.325000 AM      1
123456      10/16/2018 7:34:40.448000 AM      1
123456      10/16/2018 7:54:51.566000 AM      1
123457      ...

When I'd like it to be:
PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 07 AM                  3  
123457      ...

I did try using TO_CHAR(o.timeplaced,'yyyy-mm-dd hh24') in my select, but the results are basically the same. I'm wondering if this is because of the "and o.timeplaced > current_timestamp - interval '1' hour" while the …
0
FISCAL WEEK CALCULATION IN SQL MONTH STARTS FROM JUN AND THE WEEK SHOULD START FROM SUNDAY
0
I have an oracle pl/sql query that performance is really bad and hoping I can adjust if possible.   I have the following query and where you see the nested sub queries I have about 10 of those just showing 2.   Is there any better way to handle this for the sub queries as the timing is really bad when running with the nested sub queries.  

SELECT
c.CUST_ID,
c.CUST_NAME,
, (select r.FORMATTED_RESULT
                            FROM TEST t
                            JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test1'
                            JOIN RESULT r ON t.TEST_ID = r.TEST_ID
                            JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result1'
                            JOIN (SELECT MAX(TEST_ID) AS TEST_ID, CUST_ID, NAME
                                        FROM TEST
                                        GROUP BY CUST_ID, NAME) g ON g.TEST_ID = t.TEST_ID AND g.CUST_ID = t.CUST_ID AND g.NAME = t.NAME
                            WHERE t.CUST_ID = C.CUST_ID) "CustTest1"
            , (select r.FORMATTED_RESULT
                            FROM TEST t
                            JOIN TEST_TEMPLATE tt ON t.TEST_TEMPLATE_ID = tt.TEST_TEMPLATE_ID AND tt.NAME = 'Test2'
                            JOIN RESULT r ON t.TEST_ID = r.TEST_ID
                            JOIN RESULT_TEMPLATE rt ON r.RESULT_TEMPLATE_ID = rt.RESULT_TEMPLATE_ID AND rt.NAME = 'Result2'
       …
0
Hi ,
I have a table table A having column col1,col2,col3,col4

I want to select the data in the table which has null values but with a statement which shows that what are the column having null values as below example:

INPUT:

col1 col2 col3
A               B
         X      B
A              

OUTPUT:

col1 col2 col3  statement
A               B      NULL IS COL2
         X               NULL IS COL1,COL3
A                        NULL IS COL2,COL3
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 an oracle 11g table TB_Doc with column DocNo among other columns
Sample values for DocNo
   Doc1
   Doc1_AR1
   Doc2
    Doc2_AR2
   Doc3
   Doc4
   Doc4_AR1

The ending _ARx need to be trimmed and I need to get a view with DerivedDoc as

  Doc1
   Doc1
   Doc2
    Doc2
   Doc3
   Doc4
   Doc4

   Please give me the Sql for the view
0
hi,

I am trying this tools ? http://www.sqlines.com/download: to try migrate tables and data from oracle 12c to mariaDB, but the downloaded SQL data tools binary always shows error:

when I test the connection to Oracle, it shows this error

SQLdata_Oracle.jpg
when I test the connection to MariaDB, it shows this error

SQLdata_MariadB.jpg
any idea on it ? come file missing ? how can I get it back?

the installation instruction of SQL data is :

How to Start:

  1. Download and unzip the file, no installation is required
  2. Run sqldataw.exe on Windows to launch the GUI version
  3. Run ./sqldata on Linux or sqldata.exe on Windows to launch the command line tool. Command Line Reference
  4. The tool uses low level native C/C++ connections and in-memory bulk loader APIs, so if you are able to connect to your databases using the existing client software, you should be able to start using SQLines Data immediately. SQLines Data Connections.

I do nothing wrong here.
0
Hi,
I would like some advice.
Recently I installed MYSQL V8 and I only work with Excel files which can be as large as 800 columns wide and up to 1 million rows.
Some time ago I had installed MYSQL for Excel which I managed to import a couple of files however I have since changed
computers and had overlooked that version.
Considering it took me a week or so to finally get this V8 working I am reluctant to uninstall and reinstall.
I did try to install the Excel version but cannot install over the existing installation.
My question is, is there a way to have both versions installed ? renaming folders etc ?
Thanks
Ian
0
I have an Oracle 11g database with table TB_Doc  and column DocNo ( and there are more columns
 
Following are some sample values for DocNo
  10-123-Doc
  10-123-Doc_DH
   MyDoc1
   AnotherDoc
   AnotherDoc_DH
   DH_GoodDoc
I need a view with additional column DocNoDerived in which all the _DH  in the end should go as  follows
   10-123-Doc
  10-123-Doc
   MyDoc1
   AnotherDoc
   AnotherDoc
   DH_GoodDoc
0
Hi

I am trying to update tables using the dbms_execute_parallel but it does not update table.  The things already checked are "job_queue_processes" it is 1000. The "parallel_min_servers" is 16. The "parallel_server" is FALSE is this the reason?

The table is CKP_PARALLEL. In this the column PPREM needs to be updated. Step1_CheckAmountAtStart The PPREM is 180.
Then executed the dbms_execute_parallel.
-------------------- PARALLEL STEP 1 ------------------------------------------
exec DBMS_PARALLEL_EXECUTE.CREATE_TASK('Update_TASK');
exec DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('Update_TASK','VM1DTA','CKP_PARALLEL',TRUE,1);
------------------------------------------------------------------------- 
DECLARE
 l_sql_stmt VARCHAR2(1000);
BEGIN
  l_sql_stmt := 'update CKP_PARALLEL set PPREM = PPREM + 10 WHERE ROWID BETWEEN :START_ROW_ID :END_ROW_ID';
  dbms_parallel_execute.run_task(
    task_name        =>'Update_TASK',
    sql_stmt         => l_sql_stmt,
    language_flag    => dbms_sql.native,
    parallel_level   => 5);
  
END;
/

Open in new window

Step2_Exeuted
And then checking the premium (after commit).Step3_StillSame
What could be hte reason of this?
Oracle version 12C:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Kindly suggest!
0
Hi,

I have this query that i would like to modify but i don't know how.

Select Description from Comment_T

Open in new window


In the Description field, i have value like: TT_MB_1. TT_CC_6, TT_FF_9 etc...

I would like to get everything from the string up to the last underscore "_".
Ex: If i have TT_MB_1, it would give me "TT_MB_".

How can i do that?

Thank you for your help
0
Searching in an Oracle database, one of my parameters for exception of the record is conditional on one field.  Here is the SQL:

select tax_id, mcal_code, taxonomy_cd
from ODW.PRV30_SERVICE_PRV a JOIN odw.prv301_abs_service_prv_npi b ON b.prv301_tax_id = b.prv301_tax_id
WHERE mcal_code = '50' and taxonomy_cd not IN ('261Q00000X', '261QP0904X')
ORDER BY a.prv30_mcal_code desc

In the where clause the two fields, mcal_code  and  taxonomy_cd are linked as both field values have to be present.  
In pseudo code:  When the mcal_code = 50 and if the taxonomy_cd =  '261Q00000X' or '261QP0904X' then that record is skipped
So in the results I will see records that have mcal_code 50 and taxonomy_cd '261Q00000X' and '261QP0904X', but not on the same record.
What is the SQL that will allow me to do this?

Thanks,
Scott
0
Hi, I need help in coding sales ration in sql, plsql. I have a file attached with details
C--Tanuja-Lake_IL-BRDs-sales-ratio-.docx
0
How to calculate linear regression in oracle plsql.
Please see the file attached.
C--Tanuja-Lake_IL-BRDs-linear-regre.docx
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hi,
I have few tables I want to do checksum if anyone try to update the record on table.

I have table aa
First name ,
Last name,

I want to apply a logic if some is updating a record and other person also want to update same record he get error can’t update as data has changed
0
Hi All,

I have an issue with a merge statement into a FACT TABLE


It was pretty simple until the users started to delete records from the source.

Current SQL:

Set Count = 1
WHEN NOT MATCHED
   INSERT
WHEN MATCHED
 UPDATED



New SQL:

So in this example, a record has been deleted from the source, it no longer matches but there is nothing to insert.  I would like it the count to be set to 0.

WHEN DELETED FROM SOURCE
Set Count = 0



Source
Bob Jones | Confirmed | 1111
Mary Jones | Confirmed | 1112
James Jones | Confirmed | 1113
Helen Jones | Confirmed | 1114


TARGET
Bob Jones | Confirmed | 1111 | Count 1
Mary Jones | Confirmed | 1112| Count 1
James Jones | Confirmed | 1113| Count 1
Helen Jones | Confirmed | 1114| Count 1
Peter Market | Confirmed | 1115| Count 0



I’m loading to a fact table using a merge and now they are just blanket deleting records, my facts are off.  This must be accounted for somehow?


Thank you for any help at all.
1
Hi we had a "first time" Nessus scan done on our network. There appeared to be some database connection drops - so I got them to stop scan

They came back and did scan after hours. it completed
We have a Oracle sun server running VMs for all our Oracle software and databases.
Now issue is that we have subsequent to the scan a poor performing oracle environment

After sometime I determined that the ping times to the oracle sun server were slow - im talking few at  <1ms most >8ms and higher some up to 1000 ms - no dropped pings
but consistently slow. this is all on a 10gb LAN.
I'm almost certain that Neussus scan has done something to the server in its scan. They appeared to have the safe scan option ticked. I dont know much at all about nessus scans
Is it possible that the server could perform poorly POST the scan - requiring a server reboot. No other changes on the LAN
could nessus have done something to the network layer of the sun server code. The server is not under any extra load perf monitor is limited on oob interface gui
everything we use sits on a database and is slow for entire business. ANy nessus experts out there?
0
GETTING ORA-01719 WHEN I USE OR or IN CONDITIONS

SELECT * FROM summary_trans trans,doc_hdr hdr
WHERE trans.doc_number = hdr.doc_no (+)
AND hdr.filler_107 NOT IN ('TRA','CON')
OR hdr.filler_107 IS NULL;

WHAT IS THE ALTERNATIVE FOR THIS QUERY APART FROM UNION ALL CASE
0
I have a timestamp column on an 11g DB which, due to a coding error which has since been fixed, caused the minute, second, millisecond... to be included instead of just "00". For example,

10/7/2018 02:03:02.432000 AM

What I need is to update all of these records which have a minute, second, millisecond greater than "00" to be "00"

e.g. 10/7/2018 02:00:00.000000 AM

I've played around with the extract() function, but I just can't figure out the correct syntax.

Thanks,
Larry
1
Hi,

Before we migrate from oracle to MariaDB DB we will do PoC and for the PoC we need to prepare some information:

1) How many Tables, Views, Procedures, Functions, Packages, Triggers, Sequences, synonyms  are there need to migrate

2) how often items in 1) has been executed

3) do we need to modify SQL statements within the application?

For 1) and 2) any script helps to find out? the script should filter out the items NOT belongs to oracle system.

for 3) I have to check embedded SQL code within the application, right ?
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.