answerstu

oracle - pl/sql trigger alter sequence

I'm really new to pl/sql. I'm trying to do a Trigger like this one (adr = after delete row) but it seems that I can't use ALTER SEQUENCE. Is this the right way of decrementing after a row is deleted or should I use a procedure?CREATE OR REPLACE TRIGGER adr_trgAFTER DELETE ON tableFOR EACH ROWBEGINALTER SEQUENCE table_seq INCREMENT BY -1;END;Edit: Sequence I'm using:CREATE SEQUENCE table_seq INCREMENT BY 1 START WITH 1;Trigger I'm using:CREATE OR REPLACE TRIGGER bir_trg BEFORE INSERT ON table FOR EACH ROWBEGIN IF :new.id IS NULL THEN ...Read more

import - Oracle: Continuing Inserts with failing deferred constraints

Sorry for the long question, but I would prefer it to be complete.My goal is to create a process to populate an Oracle 11 Database with data created by (and from) an MS-SQL Server.I would like to load as much data as I can and create an error report on records that fail constraints so we can test the new application with our data and improve our data gathering and repeat this process.I have NodeJS script that reads directly from the MS-SQL tables (one at a time) and fires INSERT ... EXCEPTION/LOG ERRORS statements to the Oracle database. This ...Read more

oracle - Handling dates in Hadoop

I'm new to the Big Data/Hadoop ecosystem and have noticed that dates are not always handled in standard way across technologies. I plan to be ingesting data from Oracle into Hive tables on an HDFS using Sqoop with Avro and Parquet file formats. Hive continues to import my dates into BIGINT values, I'd prefer TIMESTAMPS. I've tried using the "--map-column-hive" overrides... but it still does not work.Looking for suggestions on the best way to handle dates for this use case....Read more

oracle - Query to retrieve manager before employee

We have list of resources with their managers (not the complete hierarchy). We now need to retrieve the resource list such that resource's manager row should always appear before the resource row. I already tried hierarchical query, however given the data set, I'm not absolutely sure, if it's working correctly or not. Any pointers or clue, please to confirm the order?Edit: Added last row in the data set and the query doesn't seem to be working anymore.sqlfiddleselect level, emp, mgr from empwhere level = 1connect by prior emp = mgr -- and level...Read more

Oracle Stored Procedure posing a prob

[EDIT]Editing the code to reflect changes coming from commentsI have a problem with one of the stored procedures I'm trying to create in an Oracle database.The goal is to update every table which has an indiv column.CREATE OR REPLACE PROCEDURE sp_majUserOnAllK (lastU IN VARCHAR2, newU IN VARCHAR2)AS BEGIN FOR item IN ( select table_name , owner from all_tab_columns where column_name = 'INDIV' AND OWNER ='K' ) LOOP EXECUTE IMMEDIATE 'UPDATE K.' || item.table_name || ' SET indiv = :newValue WH...Read more

oracle - Correlated SQL Query using With Clause

I am trying to modify below query :SELECT START_END.*, START_END.LOAD_TIME_END - START_END.LOAD_TIME_STARTFROM( SELECT START.JOB_ID, START.LOAD_TIME_START, END.LOAD_TIME_END FROM ( SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_START FROM JOB_CTRL_RECON JCR INNER JOIN ( SELECT JOB_ID AS JOB_ID_S, MAX(RECON_ID) AS S_MAX_RECON_ID FROM job_ctrl_recon where job_id in ('1','2') and count_type = 'Source' GROUP BY JOB_ID ) SMAX ON JCR.JOB_ID = SMAX.S_MAX_RECON_ID AND JCR.RECON_ID = SMAX.RECON_ID ) START INNER J...Read more

oracle - ORA-12505, TNS:listener does not currently know of SID given in connect

I'm using JDeveloper when I try to create a database connection with the hr/hr user I get this message:Listener refused the connection with the following error:ORA-12505, TNS:listener does not currently know of SID given in connect descriptorThe Connection descriptor used by the client was:localhost:1521:ORCLthen I go to the command line and I put tnsping ORCLC:\Users\Administrator>tnsping ORCLTNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 20-FEB-2018 15:55:23Copyright (c) 1997, 2016, Oracle. All rights reserved.Use...Read more

database administration - Recover Oracle system01.dbf

When i trying to start oracle db, it saysERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: data file 1: '/u01/oradata/oracle/system01.dbf'When I trying to recover using redo logs, i've gotSQL> recover database using backup controlfile;ORA-00279: change 4925223599 generated at 02/05/2018 10:24:32 needed for thread1ORA-00289: suggestion :/mnt/backup/oracle/ORACLE/archivelog/2018_02_05/o1_mf_1_186975_%u_.arcORA-00280: change 4925223599 for thread 1 is in sequence #186975Specify log: {<RET>=suggested | filename | AUTO | CANC...Read more

How do I convert an oracle timestamp value from UTC to EST in a select statement?

Hi I have a date field (Open_Time) containing timestamp data. The times in this column are in UTC. I want to select that column and convert it to EST and insert it into another table. I want it to be EST for the time at the original timestamp (take into account daylight savings time based on what day month and year it was). I have been reading about the various timezone functions in oracle but most seem to focus on altering the database's timezone which I don't need to do. The (Open_Time) field is always recorded in UTC. BLUF: I need to se...Read more

datetime - Converting Unix epoch to date/timestamp with time zone in Oracle SQL

I'm trying to write SQL expression that will take a unix epoch (seconds since 1970/1/1) to local time in specific time zone and extract the hour value from it. After researching solutions along this time, I'm still not sure how to deal with the time zone. Note that I try to avoid using the NEW_TIME() function as it takes only a limited subset of time zone acronyms and not the full time zone name.select (TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(1464820200,'second')) as ts_utc,(TIMESTAMP '1970-01-01 00:00:00' AT TIME Z...Read more

oracle - Print column2 from row with max(column1) without including column2 in group by clause

I know it is a silly question and may be already answered somewhere, please guide me to the link if it is.I want to print a column which is not included in group by clause. Oracle says that it should be included in group by expression, but I want value to be from the same row from which max() value for the other column was selected.For example: if I have a table with following columns: Employee_Name, Action_code, Action_NameI want to see the name of action with maximum action_code for each employee, also I cannot use subquery in the condition....Read more

Query create table as with inner join select and group by (ORACLE)

This is my query, if i run the error is : Error in query: ORA-00907: missing right parenthesis, anybody can solve my problem?Create table r_tcash_loci_act_tmp AS (SELECT DISTINCTR_TCASH_LOCI_ACT.MSISDN AS MSISDN_LOCI,R_TCASH_ACT_MSISDN.MSISDN AS MSISDN_ACT,R_TCASH_LOCI_ACT.AREA,R_TCASH_LOCI_ACT.REGIONAL,R_TCASH_LOCI_ACT.BRANCH,R_TCASH_LOCI_ACT.SUB_BRANCH,R_TCASH_LOCI_ACT.CLUSTERX,R_TCASH_LOCI_ACT.UPDATED,R_TCASH_ACT_MSISDN.DAILY,R_TCASH_ACT_MSISDN.TOTAL_TRX,R_TCASH_ACT_MSISDN.TOTAL_VOLFROM R_TCASH_ACT_MSISDNINNER JOIN R_TCASH_LOCI_ACTON R_TCASH...Read more