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

How do I call a BI publisher report via an apex rest service.
Can you please share any examples / links
0
The Ultimate Tool Kit for Technolgy Solution Provi
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

How to consume web API  in stored procedure I am using oracle 11g R1 DB  ? Please help with an example.
0
I have a piece of code that needs to be converted from DB2 to Oracle.
I'm trying to return all queues & sub queues that fall under the parent queue. Each sub queue can have a sub queue.
It runs fine on DB2 but getting an error message ORA-00904: invalid identifier

This is a sample portion in DB2

select *  FROM OPENQUERY(DB2,'
WITH RPL(LEVEL, PARENT_QUEUE_ID, Queue_ID, NM)
AS
          (SELECT 1
                , ROOT.PARENT_QUEUE_ID
                , ROOT.QUEUE_ID
                , ROOT.NM
           FROM schema.tbl_queue ROOT
           WHERE    ROOT.NM IN(''Auto Current'',''Auto PD'',''Auto Repo'' )
           UNION ALL
           SELECT PARENT.LEVEL + 1
                , CHILD.PARENT_QUEUE_ID
                , CHILD.QUEUE_ID
                , CHILD.NM
           FROM RPL PARENT, schema.tbl_queue CHILD
           WHERE PARENT.QUEUE_ID = CHILD.PARENT_QUEUE_ID
)
select * from RPL
')
0
I need to transfer a 500GB Table(single) from one oracle database to another database using a 500Gb network. What can be the most efficient way?
0
Hello there,

I've working in the following script for the whole day, but I'm getting the following error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

Basically, I have to extract a date from a char type field called 'text' if it meets some conditions: if name field starts with 'P' otherwise, it should take another field which is date type.

SELECT
supplier_id, name,SUM(net_amount) Net_Amount
FROM supplier_table
WHERE
(CASE
     WHEN name like 'A%' and SUBSTR( text, 4 , 1 ) = '/'
                  THEN to_date(SUBSTR(text,5,8),'MM-DD-YY')                -- here, this field char type format 12-30-18
      WHEN name like 'B%' SUBSTR( text, 4 , 1 ) = '-')
                   THEN to_date(SUBSTR(text,4,8),'MM-DD-YY')               -- here, this field char type format 12-30-18
      WHEN name like '%C%'
                   THEN invoice_date                                                            -- here, this field date type format 30-DEC-18
      end) between TO_DATE('&START_DATE', 'mm/dd/yy') and TO_DATE('&END_DATE','mm/dd/yy')
group by supplier_id, name
order by name

I'm so confused about these type of date formats.
My database save date type with this format 01-JAN-18

Thanks for help.

JS
1
I have two Oracle 11G installations on two different Unix servers. I would like to know if there is a way to compare the two installations quickly and see if there are any differences in files, settings etc.

Thanks.
0
what replication options are available for an Oracle DB running on windows server?
Does VMware SRM work for this or Xerto
Or which is best product to enable a seamless fail over and fail back?
Currently we are running on physical servers but these are due for replacement so need to decide whether to continue on physical or virtualize
0
Oracle Heterogeneous Services is a feature of Oracle DB that allows the database to talk to non-Oracle DBs through their ODBC drivers.

It is fairly straightforward to debug an ODBC driver using ODBC client tools such as isql.

With isql - I can input the driver info at the initial command prompt. isql and similar tools accept the ODBC DSN name and directly loads the driver. Thus when I type: info sharedlibrary at the GDB command-prompt I see the ODBC shared library loaded that I am interested in debugging.

How can I do the same using sqlplus or other Oracle client tool?
However, with sqlplus there is no telling when the driver gets loaded.

How do I debug an ODBC driver ("dynamically loaded library") using Oracle Heterogeneous Services?
My ODBC driver works fine in isql environment.
I am interested in knowing how Oracle HS calls my driver to uncover issues in the ODBC driver and fix them.

Environment: Linux
Debugger: GDB
Oracle Heterogeneous Services: https://docs.oracle.com/cd/A81042_01/DOC/server.816/a76960/hs_conce.htm
0
I have an Oracle 11g table TB_Doc with a column DocNo ( and a few more columns)
    Doc1Vol01/1
    Doc2Vol119/2
    Vol_Fred11/2      
    1-11-DocNormal/1
    SomeDoc    
    DocFileNormal.pdf
   
 I need to have a view in which I need to replace the word Vol and the volume number to nothing  (Only if it is at the end)  

So the above DocNos should come as
   Doc1/1
    Doc2/2
    Vol_Fred11/2      
    1-11-DocNormal/1
    SomeDoc    
    DocFileNormal.pdf
0
I am getting error ORA-29279: SMTP permanent error: 504 auth type not available (#5.5.1) please help. I am using Oracle 11g R1
0
Problems using Powershell and Active Directory?
LVL 8
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

what is the solution for this problem...
0
I have a one to many oracle table where I have the following:

tblCustomer
CustomerID   CustomerName
1                      ABC
2                      DEF

tblCustomerGroups
CustomerID        GroupID
1                            1
1                            2
2                            5
2                            6

tblGroups
GroupID     Name
1                  Group A
2                  Group B
5                  Group G
6                  Group H

I want to gather a comma separated string similar to a SQL Stuff where I return the tblCustomer details with a Comma such as
Customer ID    Customer Name    Groups
1                        ABC                          Group A, Group B
2                        DEF                           Group G, Group H

What syntax would I use to handle that comma seperated string?
0
I am using MODEL clause to calculate balance and compound interest.
Using generated date converted to YYYYMM as key to dimension I get balance always repeated each 12 months.  
Can someone explain why ?

By using the generated seqence number  directly as the dimension-key fixed the problem.  


Simplified sql of both cases:
--Here balance reapeats every 12 months
SELECT * FROM (
  SELECT    TO_CHAR( ADD_MONTHS(DATE '2000-01-01',  SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual  connect by level <= 100)
ORDER BY YEARMON
)
MODEL
dimension by (YEARMON)
measures (BASE, BAL)
RULES (
      BAL[ANY] ORDER BY YEARMON =   nvl(BASE[CV()],0) +   nvl(BAL[CV()-1],0)
)  ORDER BY YEARMON


--Here balance get increased until lastrow (as disired)
SELECT * FROM (
  SELECT   SEQNR,  TO_CHAR( ADD_MONTHS(DATE '2000-01-01',  SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual  connect by level <= 100)
ORDER BY SEQNR
)
MODEL
dimension by (SEQNR)
measures (YEARMON,BASE, BAL)
RULES (
      BAL[ANY] ORDER BY SEQNR =   nvl(BASE[CV()],0) +   nvl(BAL[CV()-1],0)
)  ORDER BY YEARMON
0
hi,

how to migration Oracle DB 10g/12c content to MariaDB 10.2 ?

any step by steps guide  for it?

we try to migrate part of it as DB size large and see what the problem WILL be to estimate how long the process will be, should we do it ourselves or hire service provider for it.

what tools you guy use for it?
0
Hi,
I have below query which runs against 187 million rows and it eat all temp space .

Can this we rewritten to improve performance

MERGE INTO TEST1 T USING
 (SELECT ROWID RID, WEEK, SIN, DATE_LOADED,
DATE_EXPIRY, DATE_CREATION,
LEAD (CASE WHEN DATE_EXPIRY =TO_DATE('99991231','yyyymmdd') THEN SYSDATE END ) OVER(PARTITION BY WEEK,SIN ORDER BY DATE_LOADED) AS NEXTDATE FROM TEST1) S
ON (T.ROWID = S.RID)
 WHEN MATCHED THEN
 UPDATE SET T.DATE_EXPIRY = TRUNC(S.NEXTDATE)
WHERE T.DATE_EXPIRY =TO_DATE('99991231','yyyymmdd') A
AND S.NEXTDATE <>TO_DATE('99991231','yyyymmdd')
0
After database and apps upgrade adadmin not accepting apps password
Upgrade paths
Db-from 11.1.0.7 to 12.1.0.2
apps-from R12.1.1 to R12.1.3
adadmin show below issues;-
After upgrading database 11.1.07 to 12.1.0.2  adadmin not accepting apps password
ORA-01017: invalid username/password; logon denied


occurred while executing the SQL statement:

CONNECT APPLSYS/*****

Error: The given ORACLE password is not the correct password.
Please re-enter the ORACLE username and password.
0
Hi,
Getting below error in procedure its huge procedure so not sure where it can be .

Any idea what can i do to debug it

v_load_record_count = 14073
SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored.
SP2-0734: unknown command beginning "DBMS_STATS..." - rest of line ignored.

Thanks
0
Hi I have a  table TB_Doc in an Oracle 11g database.  Also I have TB_Doc in an sql database   ( two databases in different DB servers)

I need to run a query select Plant, Count(DocNo) from TB_Doc group by Plant  on both databases

How can I output the result of the two queries in the same  excel file (Kept in a networkpath)
 Excel file has predefined template for first two rows .  So the data (excluding header) need to be written from row 3  
Column A, B to be written from Oracle.  Column D,E to be written from Sql Server
(Currently I do copy paste the results. But there are several count match reports I need to generate of this kind. So It would help, if it can automate)
0
Hi Expert, Hope you are doing Great!

I have a small requirement, where i want to read article from e-Paper and want to store(The Headline and w.r.t. the article image) it in Oracle DB .

FYR::  https://epaper.timesgroup.com/TOI/TimesOfIndia/Index.html

Need to fetch all the relevant data from this source for creation of  database.

Consider creating multiple databases. One will be main database, others will be subsets of the main database to extract data more accurately.

Where, I want to select the article  headline and should reflect the image of the article.

This is not straightforward a i asked here but i just looking for a E-R diagram for this type of requirement.

Please let me know for more info regarding!

Thanks.
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I have an item on the my Oracle APEX page created via APEX_ITEM.SELECT_LIST. The item's name property is f02. I have a dynamic action one page load where I want to check in Client-side condition what is the selected value of that item.

I tried setting the type to Item=Value, then setting Item to f02 and Value propertyto Test. When I run the page I do not get any errors but the condition resolved to False even though the value selected in the select list is Test.

I tried changing f02 to apex.item('f02') but it made no difference.

What is the correct way to access that type of a field?
0
The Oracle SP is returning 2 outputs. One is cursor and another is string. I want to handle both outputs in C# after execution. The 2nd output is error if the query has any issues. If string returned is null or empty I should save the cursor output to a datatable. Please help

Oracle SP is executeSQL(sql_in IN varchar2, p_out package.ref_Cursor, p_err out varchar2)
0
I need to change a SQL query so that the SUM function returns a 0 when there is no data found.

Here's the query:

SELECT
   /*+ INDEX (f FV_INV_SELECTED_DUEDATE_IDX) */
   --Within 15 days
   SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
) "less15days", SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date AND i.terms_date + 15 
      THEN
         1 
      ELSE
         0 
   END
) "15COUNT", 	--Between 15-30 Days
   SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
) "15to30days", SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE BETWEEN i.terms_date + 16 AND i.terms_date + 30 
      THEN
         1 
      ELSE
         0 
   END
) "1530C", 	--After 30 Days
   SUM(
   CASE
      WHEN
         P.ACCOUNTING_DATE > i.terms_date + 30 
      THEN
         i.invoice_amount 
      ELSE
         0 
   END
) "30plusdays", SUM(
   CASE
      WHEN
         P.accounting_date > i.terms_date + 30 
      THEN
         1 
      ELSE
         0 
   END
) "30plusC" 
FROM
   ap.ap_terms_tl t , ap.ap_invoice_payments_all p , ap.ap_invoices_all i , ap.ap_payment_schedules_all s 
WHERE
   t.NAME <> 'PROMPT SB NET 30' 	--take out small business terms
   --and t.NAME not like 'STANDARD%'  -- take out standard terms
   --and 

Open in new window

0
Hi Expert,

I'm getting below Oracle Web-logic application error could anybody please guide me how to fix it!

subsystem is initializing on Server WebWORKS6.>
####<Aug 2, 2018 1:09:38 PM GMT> <Info> <Store> <trpridrps1app6> <WebWORKS6> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1533215378092> <BEA-280008> <Opening the persistent file store "WLS_DIAGNOSTICS" for recovery: directory=D:\Manu\IDRPPSA\srvr6\config\JDADomain\servers\WebWORKS6\data\store\diagnostics requestedWritePolicy="Disabled" fileLockingEnabled=true driver="wlfileio3".>
####<Aug 2, 2018 1:09:38 PM GMT> <Critical> <WebLogicServer> <trpridrps1app6> <WebWORKS6> <Main Thread> <<WLS Kernel>> <> <> <1533215378108> <BEA-000386> <Server subsystem failed. Reason: java.lang.NullPointerException
java.lang.NullPointerException
       at weblogic.store.io.file.StoreFile.close(StoreFile.java:440)
       at weblogic.store.io.file.Heap.open(Heap.java:320)
       at weblogic.store.io.file.FileStoreIO.open(FileStoreIO.java:104)
       at weblogic.store.internal.PersistentStoreImpl.recoverStoreConnections(PersistentStoreImpl.java:431)
       at weblogic.store.internal.PersistentStoreImpl.open(PersistentStoreImpl.java:422)
       at weblogic.store.xa.PersistentStoreManagerXA.createFileStore(PersistentStoreManagerXA.java:117)
       at weblogic.diagnostics.archive.DiagnosticStoreRepository.getStore(DiagnosticStoreRepository.java:91)
       at …
0
I need to generate an XML file with header, detail records. The detail records come from one table and the header record comes from a different table. The generated XML file must be written to a flat file. How do I go about doing this.

Sample XML -
<?xml version="1.0" encoding="UTF-8"?>
<AR_HDR>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<OPRID>INTEG</OPRID>
<AR_DTL>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<TRANS_ID>A820311</TRANS_ID>
<EFFDT>20180721</EFFDT>
<DESCR>Fee</DESCR>
<TRAN_CODE>C</TRAN_CODE>
<BUSINESS_UNIT_GL>xxxx</BUSINESS_UNIT_GL>
<FUND_CODE>90</FUND_CODE>
<DEPTID>1234</DEPTID>
<ACCOUNT>56789</ACCOUNT>
<BUSINESS_UNIT_PC/>
</AR_DTL>
<AR_DTL>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<TRANS_ID>A820311</TRANS_ID>
<EFFDT>20180723</EFFDT>
<DESCR>Payment</DESCR>
<TRAN_CODE>D</TRAN_CODE>
<BUSINESS_UNIT_GL>yyyy</BUSINESS_UNIT_GL>
<FUND_CODE>20</FUND_CODE>
<DEPTID>1234</DEPTID>
<ACCOUNT>56789</ACCOUNT>
<BUSINESS_UNIT_PC/>
</AR_DTL>
</AR_HDR>

The values from the detail records comes from a single table. The header record values can be written out from some variables as part of pl/sql code.
Any suggestions will be helpful.

Thanks
0
Integration with Oracle OPERA. I have a customer who uses Oracle OPERA for their hotel bookings etc. The customer would like to have an email sent out a couple days before their reservation and then upon checkout. I was thinking Microsoft FLOW would be a good candidate, however, I'm not sure if FLOW is able to connect to the Oracle database.
Someone mentioned that OPERA has an XML web service, however, didn't really elaborate as to if this would work for my scenario. I'm open to suggestions and what would be the best or at least a recommended way of getting booking information such as the name, dates and email addresses.
If Microsoft FLOW is not recommended, would a C# application be advisable to use?

Currently, we have someone who creates an email list everyday and sends through Mail Chimp. Thanks for your assistance in advance.
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.