[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 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
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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 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
Please delete, the script does work - it was my bad data. Thanks!
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
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,

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
I want to create a excel through forms using forms 6i to migrate into forms 12c
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 am working on forms 6i i have two table one is database block and one is control block i want a approval form in which three radio buttons are created 1 approval 2 back 3 cancel and a table in which check the hierarchy for approval
0
Hello,
Iam running the below query on oraccle:

select distinct m.sc_msds_id as sc_msds_src,m.msds_num as msds_num_src
from (
select distinct sc_msds_id,msds_num,
 TO_CHAR(REVISION_DATE, 'MM/DD/YYYY')  as revision_date_lookup,created,status
from sc_msds 
)m
where 
(revision_date_lookup = ('04/19/2018 00:00:00') or revision_date_lookup is null )  
AND status='Rejected' and TRUNC(created) = '05-OCT-2018';

Open in new window

The query is not returning value as  revisiondatelookup value on the db is 04/19/2008. How do I update the query so it can return the data row matching the revision date
0
hi how can i display the top ten record based on the value seected in menu is oracle form 11g
https://drive.google.com/open?id=1oYWbm5Oeb8mV71VWaNtaV8-Y_X95n0PM sample is hear
0
hi am downloading the file using webutil how can i set the context to ASCII or Text when i download the file in my window is puting everything in one line am in oracle form 11gR2  the application is running in solaris this is what is happening when i download the file https://www.editpadpro.com/tricklinebreak.html i what to do this in my code https://community.oracle.com/thread/2370244
0
I'm experimenting with near real time context indexes at the moment but there seems to be a lack of any comprehensive documentation on how these should be effectively implemented and managed.

I believe I now have all the prerequisites in place, but I am sure that the index should be configured as system managed rather than user managed.

DOMIDX_MANAGEMENT is currently set to USER_MANAGED. Presumably all I need to do is change this to SYSTEM_MANAGED to complete the process? What is the best way to achieve this for this single index? Is there a parameter that needs to be set at the point of index creation?

Thanks in advance for your contributions.
0
I have an oracle 12.2 database.. I set the audit trail setting to DB, EXTENDED to enable standard auditing and capture sql statements.

My understanding there are a dozen privileges that oracle will audit by default like CREATE TABLE, DROP TABLE, CREATE PROCEDURE, etc.

I have a main application admin account "hradmin" and I ran the sample schema script to create "OE" user and tables..

When I log in as "hradmin" and I created test table, dropped it, delete data, everything was recorded in the audit trail log.

When I logged in as "OE" and ran same steps nothing was recorded in the audit trail.

Do you know why?

I also tried logging in as SYS and running commands like this and still did not do it.

AUDIT CREATE ANY TABLE;
AUDIT DROP ANY TABLE;
AUDIT ALTER ANY TABLE;
0
Dear Experts,

 How do I know in Oracle SQL for specific table when ( the time ) it was last updated or changed ?

Thanks
0
hi am downloading the file using webutil how can i set the context to ASCII or Text when i download the file in my window is puting everything in one line am in oracle form 11gR2  the application is running in solaris
1
Dear Experts

We had one large query and it has lots of join and was having full scan. So we applied the /*+PARALLEL(10)*/ hint. And this server has lcpu=12 (No of processor 3). And the query performed very well (8hours came down to 6 mins). Then it worked nicely and yesterday got the below error:

Caused by: java.lang.RuntimeException: java.lang.RuntimeException: com.quipoz.framework.util.jdbc.SQLRuntimeException: ORA-12801: error signaled in parallel query server P00I
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Our DBA reported that yes the TEMP space has touched the limit. So, to solve it, what I am going to try is to reduce the PARALLE degree to 4 or AUTO.
Do you have any other idea to manage this kind of problem?
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.

Hello,

I am getting below error while connecting Oracle DB from Visual Studio 2015 - SSIS. Can you help me to fix below error?
MS SQL Version: MS SQL 2016 Ent Edition
Oracle client version: Oracle 11g 64 bit (Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) )
ODAC version: ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) (32 bit)

I have also tried with 64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64, but still same error. There are No multiple 32-bit client libraries (SQAORA32.dll) on the same machine.

I am able ro connect oracle DB using SQL plus utility but geeting error in SSIS.
0
Hi ,

I am coding a HHT program which manages stocks and create picklists for pickers.
Orders will be imported by user .

I have stock table , keeps Product_Code, Location, Stock, Blocked_Stock
Order Table , keeps OrderCode, Product Code , OrderQty (will be imported by user)
Picklist Table , keeps OrderCode ,Location , ProductCode, OrderQty, PickedQty

There will be a textbox and button in program , user will enter order ordercode and press "Generate Picklist" button

need help here : when user presses button in database side a function will start to work it will start to check items in ORD1 and for example first will get Product1 and will check stocks one by one by location
it will check available stock as Stock - Blocked Stock for example if one product need to pick 40 pieces , it will check for location as Stock(50) - BlockedStock(20) = 30 , it will block this 30 pieces and look for next location until it completes all order quantity 40. It must update blocked quantity too when this Product1 blocked this 30 , so blocked stock should be 50 now .

ıt needs to check this for all items and create a picklist as in attached file at last, so regarding this created picklist program will guide user to pick for necessary quantities from correct locations.

not so much experienced in cursor management in oracle side , thanks in advance.

table_view.jpg
0
Hi Expert,

Please look into the code and guide me where is the blockage.

Email is coming without attachment. i think issue in email piece.

CREATE OR REPLACE PROCEDURE AMPSADM.emil_test
AS

v_dir_path         VARCHAR2(100) := Amps_Common.get_output_dir;



v_data_file    VARCHAR2 (10000)
              := 'AMPS_SHIPMENT_REJ_REC_' || TO_CHAR (SYSDATE, 'YYYYMMDD')
                 || '.csv';
      v_output_file      utl_file.file_type;
     
     

      --attachment varchar2(10000) :=v_output_file;
      --l_output            varchar2(32767);
   
      v_loop_num         NUMBER := 0;
     -- v_output_file varchar2(10000);
 
  --v_sql VARCHAR2(100000000):=NULL;
 
 -- clobsample CLOB;
--vLine VARCHAR2(100);
--attachments PKG_SEND_MAIL.ARRAY_ATTACHMENTS := PKG_SEND_MAIL.ARRAY_ATTACHMENTS();
 

    cursor AMPS_SHIPMENT is  
  SELECT distinct  '="'||to_char(cust_id_nr_cd)||'"' CUST_ID_NR_CD,
  '="'||to_char(ind_acct_nr_cd)||'"' IND_ACCT_NR_CD,
  '="'||to_char(brnd_nr_cd)||'"' brnd_nr_cd,
  '="'||to_char(PROMO_PLN_YR_CD)||'"' PROMO_PLN_YR_CD,
  '="'||to_char(FMLY_NR_CD)||'"' FMLY_NR_CD,
  '="'||to_char(CS_UPC_NR_CD)||'"' CS_UPC_NR_CD,
  '="'||to_char(CS_UPC_VRSN_NR_CD)||'"' CS_UPC_VRSN_NR_CD,
  '="'||to_char(PROCESS_STATUS_DESC)||'"' PROCESS_STATUS_DESC
FROM AMPSADM.SPIN_DATA_SM_STG
WHERE PROCESS_STATUS IN ('N','E1')
AND ROWNUM<=1000;       

begin
     
v_output_file := utl_file.fopen (v_dir_path, v_data_file, 'w');  
            
         UTL_FILE.put_line 

Open in new window

0
The following SQL statement is throwing the following error and I don't know how to fix it: ERROR: ORA-01848: day of year must be between 1 and 365 (366 for leap year)


select
null as "Document Type",
null as "Document Co",
null as "Record Type",
elig.CEY55CONID "Contract Id",
cntr.CHY55CMFGID "Manufacturer Contract Id",
elig.CEY55CTNUM "Series Number",
addr.CAAN8 "Account Number",
elig.CEILEL "Include/Exclude",
TO_DATE (TO_CHAR(elig.CEEFFF+1900000), 'YYYYDDD') "Eligibility Start Date",
TO_DATE (TO_CHAR(elig.CEEFFT+1900000), 'YYYYDDD') "Eligibility End Date",
null as "Vendor’s Customer Number",
CA$C10 "Active Flag",
CABADT "Address Type",
addr.CAAN81 "Bill To Acct",
attr.CEY55GPON "GPO Number",
attr.CEAC02 "Sub Cat",
attr.CEAC01 "Class of Trd",
attr.CEY55POLID "Pool ID"
 
from MMSDM910.SRC_E1_MMS_F5521070 elig
inner join MMSDM910.SRC_E1_MMS_F5521051 addr
on elig.CEAN8 = addr.CAAN81
inner join MMSDM910.SRC_E1_MMS_F5521010 cntr
on elig.CEY55CONID = cntr.CHY55CONID
inner join MMSDM910.SRC_E1_MMS_F5521060 attr
on elig.CEY55CONID = attr.CEY55CONID
where addr.CA$C10 ='Y'
and addr.CABADT <> 'B'
and attr.CEAC01 IS NOT NULL
and TO_DATE(TO_CHAR(elig.CEEFFT+1900000),'YYYYDDD')> sysdate and CHY55VEND IN ('3904111')
and TO_DATE(TO_CHAR(cntr.CHY55VEFFT+1900000), 'YYYYDDD')> sysdate and CHY55VEND IN ('3904111')
Order By '7,4,5'
0
How many instences creating in oracle 11g , ihave single core processor and 16Gb RAM,5ooGb hard disk
1
we need to extract value from below xml tag

select extractValue(value(vy), '//NARRATIVE/NA_LOAD_DT') dateloaded,
                               extractValue(value(vy), '//NARRATIVE/NA_LOADED_BY') loadedby,
                               extractValue(value(vy), '//NARRATIVE/NA_TYPE') nartype,
                               extractValue(value(vy), '//NARRATIVE/NA_TEXT') text
                          from table(XMLSequence(Extract(lx_xml,
                                                         '//RESPONSE/MESSAGE/ITEM/RSP_REPORT/CONSUMER/NARRATIVES/NARRATIVE type="M"'))) vy)
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.