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

Dear Experts,

 I need help to create a view from the following table, the view contain 2 columns ID & sum of Hold time which is difference between each Hold_RELEASE & HOLD



ORDERID  |  STATE                   |  Timestamp
1                |  HOLD                    | 12-Nov-13 06:03:46
1                |  HOLD_RELEASE   | 18-Nov-13 04:13:33
1                |  HOLD                    | 19-Dec-13 03:12:34
1                |  HOLD_RELEASE   | 25-Dec-13 12:04:55
2                | HOLD                     | 12-Oct-13 06:22:33
2                | HOLD_RELEASE    | 23-Nov-13 08:12:11


Thanks
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Raw_data.xlsxHello Expert,

I have a table with following record details.

SR_NO ROOM_NO         FLOOR_NO      TIME_830AM_930AM      FLOOR1      TIME_930AM_1030AM      FLOOR2      TIME_1030AM_1130AM      FLOOR3      TIME_1130AM_0130AM      FLOOR4
        1           216                    2ND                      ME                        H1                             NULL                 NULL                    BE                                   G1                         NULL                          NULL
        2           216                      2ND                             ME                         H1                             GE                            I1                   NULL                                 NULL                      NULL                        NULL
        3           216                      3RD                       NULL               NULL                             EE                           E1                   NULL                            NULL                         FE                            F1
        4           216                      3RD                            NULL              NULL                            NULL                 NULL                    AA                                   A1                         NULL                          NULL
        5           217                      1ST                            NULL              NULL                             BB                           B1                   NULL                                 NULL                         NULL                          NULL

Looking for O/p like this!

SR_NO ROOM_NO         FLOOR_NO      TIME_830AM_930AM      FLOOR1      TIME_930AM_1030AM      FLOOR2      TIME_1030AM_1130AM      FLOOR3      TIME_1130AM_0130AM      FLOOR4
0
TABLE1
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date


TABLE2
Relation_ID
Relation_Name

TABLE3
Store_ID
Store_Name

TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong.  I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)

The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'

How do I only use the Max records of TABLE1 in 2016?
0
In oracle how can I replace the chinese character from a given string
string = '020?Domestic CT outgoing?20测试叙事测试叙事 测试叙事测试叙事测试叙事 ?21测试叙事测试叙事)测试叙事测试叙事测试叙事?31测试叙事测试叙事 测试叙事?32测试叙事'

I want the Output as
020?Domestic CT outgoing?20:?21:?31:?32

Please help me
0
How to generate the column header in APEX 5.0 classic report (using generic column) using PL/SQL Function Body?
Screen-Shot-2018-07-13-at-07.10.55.png
0
Hi Experts,
I need a help on build a report by using the SQL.
I have a two tables
1) Document table
2) Fields table

The relation between these tables are
documentid in both the tables..

Basically I need the SQL to get the report as shown in the spread sheet. ( Attached excel sheet)

Thank you
Regards,
KumarTest.xlsx
0
hi ,

When i try to do an insert statement from Java to Oracle table i am getting below error
ORA-01693: max # extents (1000) reached in lob segment.

Is there any way i can overwrite my insert statement from Oracle side . So that it will not break the code .
I can't ALTER the table in near future . But i can remove the BLOB data in the table . If i create a trigger like before INSERT Update BLOB column to EMPTY String ,will these solve the issue ?  . The issue happening only in production .
0
Hello,
Iam looking  on how to implement the requirement file using oracle sql.
The excel file has the final output.
The rawdata is from a table workeffort
The report date column is the past 30 days from the system date and in the sample Iam running the report on 7/6/2018.
The past due and on time is calculated based on the calculation formula:
CASE  WHEN (ENDTIME < (SYSDATE - 30)) THEN 'PASTDUE'
ELSE 'ONTIME' END

Please let me know.
Thanks
sample2.xlsx
0
select a.abc ,bdf ,cgh , b.xyz
from tablea a , tableb b
where a.abc in ( select distinct xyz from tableb);

this gives error-
ora - 01652 unable to extend temp segment by 640 in tablespace oratemp
0
hi,
I'm trying to execute this query and the following error happens

select a.abc ,bdf ,cgh , b.xyz
from tablea a , tableb b
where a.abc in ( select distinct xyz from tableb);

this gives error-
ora - 01652 unable to extend temp segment by 640 in tablespace oratemp
0
Managing Security & Risk at the Speed of Business
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

Hello...

I want to change "stale_percent" to 3 and "concurrent" to true, but I need to test it first. I need to improve statistics gathering on very large fast changing tables.

What is the best way to check before and after it will be set to check if this make any difference?

I read that I need to check parameter JOB_QUEUE_PROCESSES and parallel_adaptive_multi_user before I will set "concurrent".

Could you advise what values are save when setting it?

Thank you in advance.
0
hi am having issue when trying to insert in my pre-insert triger in oracle form

insert
this is the code
BEGIN
	:soi_id.unit_cde := :keys.unit_cde;
  :soi_id.soi_stat_dte := sysdate;
  IF to_number(:global.hq_unt) = to_number(:global.bas_unit_cde) then
     :soi_id.soi_prefix := 'H';
  ELSE
     :soi_id.soi_prefix := 'U';
  END IF;
  :soi_id.soi_version := 1;
  BEGIN
    select nvl(seq_no,0) + 1
      into :soi_id.soi_no
      from ils.lgpt_seq_no
     where unit_cde    = :keys.unit_cde
       and seq_no_type = 'SOI NO'
       FOR update of seq_no;
  EXCEPTION                            
    when no_data_found then
         :soi_id.soi_no := 1;
  END; 
  
  message('soi '||:soi_id.soi_no );
  IF :soi_id.soi_no is not null then 
     update ils.lgpt_seq_no
     set    seq_no      = to_number(:soi_id.soi_no)
     where  unit_cde    = :keys.unit_cde
     and    seq_no_type = 'SOI NO';
 

     --:system.message_level := '5';
     IF form_failure then                                                   
        message('Could not update the lgpt_seq_no table');
        CLEAR_FORM;
        raise form_trigger_failure;
     END IF;                                                           
     --:system.message_level := '0';
  END IF;           
  declare
  	cnt number;
  	
  	cursor a is select count(*) from ils.lgpt_seq_no
     where unit_cde    = :keys.unit_cde
       and seq_no_type = 'SOI NO';
   begin
  	open a;
  	fetch a into cnt;
  	
  	

Open in new window

0
Hello! There's a (stripped off version of real) SQL query. I understand its logic. I can't understand why it leaves only 2 records of level 1 after uncommenting AND...
Can anybody explain? (http://sqlfiddle.com/#!4/56df0e/17)
SELECT id,PRIOR id,level n
FROM(SELECT'aaa' AS id,2 AS ticket_cnt
     FROM dual
     UNION ALL
     SELECT 'bbb' AS id,4 AS ticket_cnt
     FROM dual
    )
CONNECT BY NOCYCLE (
    level <= ticket_cnt
    --AND id = PRIOR id
    )
ORDER BY    3   ,1   ,2;
And by the way, why there is cycle (comment off nocycle - you'll see)?
0
Hello All,
I have to print the below dates as report rows based on the sys date and going back to 30 days as the data rows.Using Oracle as the database.Please let me know how can I do it in the select statement.
Report
6/5/2018
6/6/2018
6/7/2018
6/8/2018
6/9/2018
6/10/2018
6/11/2018
6/12/2018
6/13/2018
6/14/2018
6/15/2018
6/16/2018
6/17/2018
6/18/2018
6/19/2018
6/20/2018
6/21/2018
6/22/2018
6/23/2018
6/24/2018
6/25/2018
6/26/2018
6/27/2018
6/28/2018
6/29/2018
6/30/2018
7/1/2018
7/2/2018
7/3/2018
7/4/2018
7/5/2018
7/6/2018
0
I have a package. The procedure in the package is called in the trigger. This procedure has a commit. So what I am creating a waybill and saving. I get this error.
Enclosed print shot of the error.

Need help to fix it. How can I not use a commit but still save the waybill that calls the package?
screen-print.jpg
0
SQl for Oracle SQL Developer.  I am trying to count the number of items by a date field between two dates but it does not like my date syntax.
and EVENT_DT > TO_DATE('01-JAN-2017', 'DD-MON-YYYY') and
and EVENT_DT > fROM_DATE('31-DEC-2017', 'DD-MON-YYYY')

The first date works but not when I add the FROM Date.
0
I have an Oracle 11g  table for documents TB_DOc  with relevant columns
    DocNo  RevNo DocState  Plant   ( ... More columns)

Latest document revision has the DocState  10  or 20    ( For a given document)  . There can be  two latest revision rows (One with 10 and one with 20)  and that is fine.
 Ideally the Plant should be filled for latest revision ( it is Ok if it filled  for previous revisions . But there is a problem if Plant is filled for Previous rev but not Latest  Rev)
 I am looking to find the documents whose Latest Rev does not have plant value but previous revision ( any row with State no not 10 or 20)  has Plant value

Sample  (In the sample below Doc1 and Doc2 are fine. But Doc3 is not .  So I need all the rows with Doc3

DocNo      RevNo      DocState      Plant
Doc1      2                10                P1
Doc1      2A                20                P1
Doc1      1A                2      
Doc2      2               10      
Doc2      2A               20                P1
Doc2      1A               2      
Doc3      2              10      
Doc3      2A              20      
Doc3      1A              2               P1
0
Using Oracl regular expressions I want to extract character after the \

'OPS$BCTGTWDOM\SMANAVI'     Output => SMANAVI

How can I do this?

Thanks
0
Hi there,

I’m working for a prototype that integrate Kerberos, ASP.NET , nhibernate  with an Oracle Database backend.  

On the  IIS web server itself, I was able to simply use sqlplus /@myoracle_server to login without a password  to access oracle .   I also tried a console app that use nhibernate , put  and it works perfectly.

However, when I tried to use it in an web application (ASP.NET )  I am constantly having a credential error.  I think it is the classic “Double Hop” issue but most online tutorial refers to SQL Server but not Oracle.

The connection string I use is “Data Source=MY_DEV;Integrated Security=true”

I used the Kerberos Configuration Manager for IIS server tool to setup my IIS server  https://blogs.msdn.microsoft.com/surajdixit/2018/02/07/kerberos-configuration-manager-for-internet-information-services-server/   and
Everything seems to be configured correctly but when I run the web app with querying oracle db, I keep getting

ORA-12638: Credential retrieval failed   (Hitting the web app on the IIS server)  OR
ORA-12641: Authentication service failed to initialize (Hitting the web app from the another workstation.)


I would be really appreciated with some help on this .
0
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

need connect different database as such Mysql and Oracle to the sharepoint by web service
but the issue is oracle cannot connect
i need online help
0
SCOPE :
The following question deals with the bare metal restore of a Microsoft Hyper-V server running (amongst others) two virtual machines on which is installed a licensed Oracle 12c (Standard). I am not interested in an “Oracle for DR” installation scheme. There is exactly one backup scenario: the entire Hyper-V server is backup up to local media using Windows Server Backup. There are exactly two restore scenarios: (1) the entire server is restored on the original equipment  from backup media using the appropriate Microsoft Distribution Media and (2) a single VM is restored from backup media using Windows Server Backup.

Comment:
I understand that Oracle’s default position is that all installations require licensing. The first scenario above implies that more than one physical machine may hold the Hyper-V server; the second scenario implies that restoring VMs could create more than the two licensed installations. To quote a post by Brian Lowinger (https://www.softwareone.com/en/blog/licensing-oracle-database-for-disaster-recovery) : “Oracle uses the term ‘failover’ to describe a scenario where, within a cluster of servers, a database running on a primary server could move to a secondary server when the primary server fails. Oracle allows for the database to run on the secondary server for up to 10 days without additional licensing requirements.” If we stick to the scope of the question and the 10 days grace period, all licensing is honored.

Constraints:
-      The OS is…
0
I have 1 java web service and 1 spring batch job process which access same Oracle database server. Oracle DB is installed on Linux VM. Both use spring JDBC template for database operations. When I run both simultaneously I start getting "The Network Adapter could not establish the connection" error. We also see "ORA-00018.Maximum number of sessions exceed" exception after few seconds.  If I run them standalone, I don't get these error. Earlier everything was working fine even while running them together. What might have changed? What could be the reason for this error? What should I check?
0
am trying to report and display all data that has been updated in a month, for every month in a year, along with the data that did not. I have created the following code that creates a new field with the month it was updated and based on that I have grouped them by the new field so its grouped by month updated. Now that part works fine, what I dont get is all the other records. For example: If i have 800 total records, 400 of which were updated in May, 200 in April and 200 in June. I get all of those. What I need in addition is the 400 that were not updated in May, the 600 that were not updated in April etc. Any help is appreciated. I have added the code I am using

With potscorehx as (
Select *
--rdm.rdt_id,
--RDI.NETWORKED_ID,
--RDM.HX_MET_STRING_VAL  
--,rdm.HX_MET_LAST_UPD_DTTM
--,RDM.HX_MET_ID
--,RDM.LINE
FROM (
       select RDI.NETWORKED_ID,RDM.HX_MET_STRING_VAL,rdm.HX_MET_LAST_UPD_DTTM,RDM.HX_MET_ID, ROW_NUMBER() over
              (PARTITION BY RDI.NETWORKED_ID ORDER BY RDI.NETWORKED_ID,rdm.HX_MET_LAST_UPD_DTTM desc) rmax_sal  
           from reg_data_hx_metrics RDM 
           JOIN REGISTRY_DATA_INFO RDI ON rdm.rdt_id = RDI.RECORD_ID
           WHERE
                rdm.HX_MET_ID = 52011105
                and
                rdm.HX_MET_LAST_UPD_DTTM BETWEEN TO_DATE('2018-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2018-06-30 23:59:59','YYYY-MM-DD HH24:MI:SS') --this date will need to be 1 month behind the date you are pulling all data on. 
     )

Open in new window

0
This is to migrate the existing SuSE Linux Enterprise server 11 (think with SP1) to another newly-setup SuSE Linux Enterprise 11 with SP4. This existing server is hosting apache, and supporting an oracle via PHP. The main thing is, we also have to configure the new SuSE to have PHP supporting Oracle with the following working descriptions from the application owner as follows,

           - Apache connecting to PHP accessing a Oracle table through Oracle instant client

with this requirement:
1)      the PHP has to be compiled to enable Oracle instant client
2)      apache has to have the connector configured (not shown here ) and recompiled

So far, on this new SuSE, a apache and associated modules were installed. When typing "rpm -qa |grep apache", the results shows,

      apache2-prefork-2.2.10-2.18
      apache2-utils-2.2.10-2.18
      apache2-mod_python-3.3.1-147.19
      apache2-mod_php5-5.2.6-50.17
      apache2-2.2.10-2.18
      apache2-mod_perl-2.0.4-40.19

How should we proceed from here?

thanks in advance.
0
Dear  Experts,

 I need to make a join between the following 2 views but I got he error message: ORA-00932: inconsistent datatype: expected - got CLOB

the field CUSTOMER_PRODUCT_NAME contain very large text

My database is Oracle 11.

each view contain SFDC_ID & Customer_product_name

I want to make join between the 2 views & display the both Customer_product_name where is different on the smme SFDC_ID

View1:
        SELECT SFDC_ID,
                 RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", CUSTOMER_PRODUCT_NAME || ',') ORDER BY CUSTOMER_PRODUCT_NAME), '/x/text()').getclobval(), ',') CUSTOMER_PRODUCT_NAME
            FROM BPMAPP.APPMAP_PRODUCT_MAP
            WHERE MAP_STATUS = 'VALID(MAPPED)' AND IS_GM <> 'Y'
        GROUP BY SFDC_ID


View2:

    SELECT  ID SFDC_ID,
             RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", CUSTOMER_PRODUCT_NAME__C || ',') ORDER BY CUSTOMER_PRODUCT_NAME__C), '/x/text()').getclobval(), ',') CUSTOMER_PRODUCT_NAME__C
    FROM
    (
    select ID,
        regexp_substr(CUSTOMER_PRODUCT_NAME__C,
                        '[^,]+',
                        1,
                        column_value)
            CUSTOMER_PRODUCT_NAME__C
    from ( SELECT * FROM BPMAPP.TJPRODUCT__C WHERE ISDELETED <> 1 AND IS_MAPPED__C = 1 AND IS_GLOBAL_MANUFACTURING__C <> 1 AND RECORDTYPEID = '012500000001GPFAA2' ) ,
        table(select collect(level)
                    from dual
                connect by level <= …
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.