Oracle DatabaseSponsored by Jamf Now

78K

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

i am facing same problem

SQL>  recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 7918209709 generated at 06/20/2018 14:57:16 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256980_926244410.DBF
ORA-00280: change 7918209709 for thread 1 is in sequence #256980


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF
ORA-00279: change 7918242496 generated at 06/20/2018 15:00:28 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256981_926244410.DBF
ORA-00280: change 7918242496 for thread 1 is in sequence #256981
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF'
no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF
ORA-00279: change 7918292819 generated at 06/20/2018 15:02:46 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256982_926244410.DBF
ORA-00280: change 7918292819 for thread 1 is in sequence #256982
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF'
no longer needed for this recovery
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.

Need to omit the timestamp from my results in Oracle. Screenshot attached of my results and my statement is below. Any help will be greatly appreciated.

SELECT        BA_VIEW_WO_SUMMARY.WO_NUMBER, BA_VIEW_WO_SUMMARY.STATUS AS WO_STATUS, BA_VIEW_WO_QUOTE.STATUS AS QUOTE_STATUS, 
                         BA_VIEW_WO_SUMMARY.DEPT_NAME AS SHOP, BA_VIEW_WO_SUMMARY.PN, BA_VIEW_WO_SUMMARY.SERIAL_NUMBER, 
                         BA_VIEW_WO_SUMMARY.DESCRIPTION, BA_VIEW_WO_SUMMARY.CUSTOMER_PO, TRUNC(BA_VIEW_WO_SUMMARY.DUE_DATE) AS NEED_BY, BA_VIEW_WO_SUMMARY.CUSTOMER_NAME, 
                         BA_VIEW_WO_SUMMARY.PBH_CUSTOMER, CAST(BA_VIEW_WO_SUMMARY.ENTRY_DATE AS DATE) AS WO_CREATION, 
                         CAST(BA_VIEW_WO_QUOTE.QUOTE_DATE AS DATE) AS QUOTE_CREATED, CAST(BA_VIEW_WO_QUOTE.SENT_DATE AS DATE) AS QUOTE_SENT,  BA_VIEW_WO_QUOTE.QUOTE_AMOUNT,
                         CAST(BA_VIEW_WO_QUOTE.APPROVED_DATE AS DATE) AS APPROVED_DATE,  
                         BA_VIEW_WO_SUMMARY.MANUAL_ECD AS ESD
FROM            BA_VIEW_WO_SUMMARY LEFT OUTER JOIN
                         BA_VIEW_REPAIR_ORDERS ON BA_VIEW_WO_SUMMARY.WO_NUMBER = BA_VIEW_REPAIR_ORDERS.WO_NUMBER LEFT OUTER JOIN
                         BA_VIEW_WO_QUOTE ON BA_VIEW_WO_SUMMARY.WO_NUMBER = BA_VIEW_WO_QUOTE.WO_NUMBER
                         WHERE        (BA_VIEW_WO_SUMMARY.SYSCM_AUTO_KEY = '1') AND (BA_VIEW_WO_SUMMARY.CLOSE_DATE IS NULL)

Open in new window

Capture.PNG
0
Hi Experts,

IF I WANT TO INSERT 5LAKH RECORDS IN DB
WHILE DOING BULK INSERT IN MIDDLE OF INSERTION IF any of THE EXCEPTion IS THOWN

IS THERE ANY CHANCE TO SKIP THAT RECORD AND CONTINUE REST OF THE RECORDS

Example
i have 5lakh records
at 99th record if i got exception will it skip the execution or it vl continue?
is there any chance to execute rest of the records
0
I am looking for the following Oracle query
   I have a table TB_Docs . The DocNo column has sample values ( Length is not fixed . Also the "_ " can appear anywhere )  
     123_456-23_0
      12-32_1-24567_0
       A1-23_c2_1
      G2/334/1_A

  The "_ " before the last character should be replaced by  "/"  ( For example 123_456-23_0  should be updated as 123_456-23/0 . But it should not update any other  "_ " other than the one before the last character)
0
I had this question after viewing Syntax to import multiple files with Oracle IMP.

I invoke imp by passing parfile=Import_Parfile.txt. Is it still possible in below way? please help

file=file1.dmp,file2.dmp,file3.dmp,file4.dmp
log=file_alll.log
ignore=Y
rows=Y
grants=none
fromuser=SCOTT

Open in new window

0
Hi Experts,

Query:
SELECT DISTINCT hdr1.cust_id          AS CUSTOMER,
  uca.ctry_cd                AS COUNTRY,
  hdr1.created_date          AS DOCUMENT_CREATION_DATE,
  hdr1.modified_date         AS DOCUMENT_MODIFIED_DATE,
  hdr1.doc_date              AS DOCUMENT_DATE,
  hdr1.doc_type              AS DOCUMENT_TYPE,
  hdr1.doc_ref               AS TECHNICAL_REFERENCE,
  hdr1.doc_no             AS DOCUMENT_NUMBER,
  stat.STAT_DESC          AS DOCUMENT_MANAGER_STATUS
  FROM hdr hdr1
LEFT JOIN line_items lin ON hdr1.doc_ref=lin.char_filler_1
JOIN status_master stat ON hdr1.stat_cd = stat.stat_code
JOIN scf_status_master scf_stat ON hdr1.scf_stat_cd = scf_stat.scf_stat_cd
JOIN cust_master uca ON hdr1.cust_id=uca.cust_id
WHERE hdr1.delete_flag ='N'
AND hdr1.latest        ='Y'
AND uca.deleteflag     ='N'
AND uca.status         ='ACT'
AND uca.ctry_cd        = 'HY'
ORDER BY hdr1.modified_date desc

The above select query executed in Database1
then
I have to insert the above selected records in another Database(Database2) test2 table(same columns and datatypes i have created for test2 table)

how to insert using oracle
if data already present i have to do update instead of insert
doc_ref is the unique key in both the tables

Can some one suggest me how to do?
0
hi,

what kind of encryption Oracle is offering? what tier are they doing to protect?
0
I have an Oracle database with Table TB_Documents   I need to get all the Docuemnts ending as _0
While I try  DocNo like   '%_0'  , I am getting more rows than the ones I need . The _ seems to be ignored
How can I run a query to get the right result ?
0
Hello expert,

In previous question titled 'DECLARE CURSOR IS SELECT BEGIN'
The Cursor/Loop form was used to display a list of names and Contact IDs

Informative but not too useful.

In this question, more complex, there are attached texts to create three
tables
TMP_SOURCE_180615 has fields Contact_ID, FirstName and LastName

TMP_SOURCE_180617 has fields Contact_ID, Contact_Phone, FirstName and LastName

TMP_SOURCE_180618 has fields Contact_ID, LastName, and Contact_Phone

PS File names too long for display. Have truncated the names to the last six digits..

Ususally a script is attempted before asking a question because I don't
believe in just asking a completely open ended question like 'How do I do this?'

But have tried a couple of things and it is obvious that I am not even close.

However within the context of the script marked 'Declare Cursor TMP_SOURCE_180615'
copied herewith below what is the approach and tactic:

Where the Contact_ID is a certain value in TMP_SOURCE_180615
Fetch the CONTACT_PHONE that has that CONTACT_ID value from TMP_SOURCE_180617
and insert the CONTACT_PHONE value that matches the Contact_ID in TMP_SOURCE_180618
in the matching CONTACT_Phone field.

Again would like to make question more specific but am in uncharted waters and cannot
find a tutorial that addresses this issue.

Will double point on answer.

Allen in Dallas

---- Declare Cursor TMP_SOURCE_180615 ------
SET SERVEROUTPUT ON
DECLARE
      CURSOR …
0
Hello expert,

Found some old SQL scripts that use DECLARE CURSOR IS SELECT BEGIN LOOP END LOOP END
that need updating but am not familiar with this form.

Have looked online for a simple example so I could experiment with this model and figure it out.
Not finding an example that is simple, if it exists.

So have set out to edify myself.

First, created a table using script  so it is portable. Attached herewith: PL SQL Create Table explicit.txt
Creates a table with three records and twenty rows.

Second, Have been experimenting with code listed herewith below;
from http://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/

The Oracle SQL Developer always returns
Error starting at line : 1 in command
which is not too informative.

Think the issue is in the syntax in the PUT_LINE
but can't quite make it work.

Just working to get a simple example to do something
on which a base can be built.

Thanks.

Allen in Dallas


-----------------begin cursor script------------------
DECLARE
  CURSOR cur_name
  IS
    SELECT
      CONTACT_ID, FIRST_NAME, LAST_NAME
    FROM
      TMP_SOURCE_180615
    ORDER BY
      LAST_NAME DESC;
BEGIN
  FOR TMP_SOURCE_180615 IN cur_name
  LOOP
    DBMS_OUTPUT.PUT_LINE( TMP_SOURCE_180615.FIRST_NAME,
  TMP_SOURCE_180615.LAST_NAME);
  END LOOP;
END;
PL-SQL-CREATE-TABLE-explicit.txt
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi Experts,

HashSet<TestData> hashset = new HashSet<>();

using select query getting data from Test1 table and adding data in TestData Object, then finally adding it in hashset.
now hashset is haviing 1000 records.

i want to insert this data in Test2 table(different database).

in Test2 table might have the same record if the record is available need to do update the values
if no data available in Test2 table need to do insetion.

Can some suggest me best way to do insert or update in Test2 table using jdbc.
0
Hi Expert,

I have following data as source!

ID          NAME
----         ---------
10              A
NULL      B
20              C
NULL      D
NULL      E
30              F

Looking for Output as ::

ID          NAME
----         ---------
10          AB
20          CDE
30          F


Thanks In Advance ...
0
Dear Experts,

I have a field in table type VARCHAR2(255), this field contain a number

I cannot convert this field to number because some values are a negative number with minus (-) sign after the number like: 124-

how do I convert this field to number ?

to_number( field) returns error: invalid number

Thanks
0
hi,

what is the official method to migrate from Oracle to MySQL ?
0
Hi,

can you please anyone let me know how to find the business days between two dates in oracle sql ?

Example :

Start Date               End Date            Total Days      Business Days( Weekends Excluded)
01/01/2018            01/31/2018            31                                 23
0
Hi Gurus,

I have the below table with details.

Table Name : Test

ID    USER        START_DATE           END DATE
---   --------      -------------------------------------------
  1     abc            01/01/2018            01/02/2018  
  1     xyz             01/02/2018           01/29/2018


Desired Output Required is Columns(s) ID, User, Start Date, End Date for Maximum Start Date and End Date i.e

ID  USER        START DATE         END DATE
===============================
 1     xyz            01/02/2018          01/29/2018
0
Hi EE,

Just finished setting up connectors to Oracle from SQL Data Engine standpoint now I need to get it working on SSIS.

Troubleshooting so far:

  • Installed the 32 bit and 64 bit Oracle client drivers on the windows server
  • Created a linked server on the SQL Server database engine instance works fine when I apply the whole EZCONNECT string. Doesn't work if just reference the label of the string in TNSNAMES.ORG file.
  • Installed the latest Microsoft Connectors V4.0 on the SQL server 2016 box.
  • Set ORACLE_HOME as a system environment variable.
  • TNS_ADMIN entry is now an environment variable.

Attached is the error I am getting

Any assistance is welcome.

Thank you.
SSIS-Connecton-Error.PNG
0
hi,

what is the maximum number of oracle RAC node on version 10g, 11g, 12c and 18c ?
0
hi,

When Oracle RAC detect conflict of data, how it handle it ?

any email /alert will send out ?

data gone ?
0
Improve Your Query Performance Tuning
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

hi,

for Oracle, any feature/way to move all oracle DB files on RAM , not just logical table / objects?
0
Hi ,
   I have an oracle 11g r2 table created with union of two tables .    
       TB_User Columns
        =========
         UserID
          Name
           .....

  There are some duplicate UserID ( not caused by union. But I can't change the original source tables  )  .  I need to delete all except one row for a given userID .      I am not able to delete duplicates based on  RowID  as there are duplicate  ROWIDs  after Union
0
Oracle 12c / PHP7

ORA-01008: not all variables bound err
The table has a collumn name with a reserved word. (I know... Unfortunately I cannot change the collumn name)

$query = 'INSERT INTO leads_bu (leads_bu."SOURCE") VALUES (:SSOURCE)';
$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ":SSOURCE", $source, 20, SQLT_CHR); // VARCHAR2 (20)
$ins_result = oci_execute($stmt);

I've tried everything to get past this error. Thanks for your help.
0
I need to refresh a schema using a source and target schemas in the same database.  

Using a parameter file. I know there is one process that requires both an export and an import in separate processes, but cannot remember if that is when you are using only one database, or using two databases with a  db link.  In this case, I am using just one database for both the source and target.  Do I need an export process then an import process, or can data pump get it done with the one import as shown?

I bring metadata only due to restrictions I have to place, and I generally just generate inserts after the import completes.

Thank you..
# impdp parfile = K:\dpump\impdp_ch1_to_sch2.par

userid = USER@db1
schemas = sch1
remap_schema=sch1:sch2
content = metadata_only
directory = db1_dump_dir

exclude = grant

any and all pointers and suggestions are appreciated..
0
Here's my scenario.  There are 4 user accounts

1. SYS (System Admin/owner account )
2. DML_USER(Account that allows users to perform DML operations)
3. ETL _USER(Account for users to perform extract load and transform)
4. Query_USER (Account  for end users - select access only)

Logging in as SYS -  Need to create a package with procedures for each of the below

1.  need to create public synonyms for those objects that do not have a synonym and, for DML, ETL and Query account.  
2. need to grant DML access thru  a role (select, insert, update, delete) to DML and ETL accounts.


The code snippet below perform grants for one of the user, but not thru a role. Any advice on  how to go about it via a role and on creating synonyms based on type of account/access levels ?

SELECT    'GRANT '
                   || CASE
				WHEN object_type IN ('VIEW')
                          THEN
                              'select'
                          WHEN object_type IN ('TABLE')
                          THEN
                              'select,insert,update,delete'
                          WHEN object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TYPE')
                          THEN
                              'execute'
                          WHEN object_type = 'SEQUENCE'
                          THEN
                              'select'
                      END
                   || ' ON "'
                   || owner
                   || '"."'
     

Open in new window

0
Dear Experts,

 I have a table1 containing Sales data for all times
Month | Customer | Product | Quantity | Revenue

I have another staging table contain data for specific interval of months:
Month | Customer | Product | Quantity | Revenue

I want to update the Revenue & Quantity to be 0 in table1 for all the rows which are in the same months interval of staging table & not exist in staging table

each row is identified by Month, Customer & product



I tried the following solution bu unfortunatly ot takes more than half an hour how do I can enhance it ? or provide a better solution


                  update CPDATA.table1 V 
                      SET V.KF_QUANITY = 0 , V.KF_REVENUE = 0 Where ROW_ID in  
                       (
                              Select  ROW_ID FROM CPDATA.STAGINING T Where MONTH >=  StartInterval AND MONTH <= EndInterval  AND
                                   NOT EXISTS   
                                          ( SELECT * FROM CPDATA.STAGINING S  WHERE T.MONTH =S.MONTH  AND T.CUSTOMER = S.CUSTOMER
                                                 AND nvl(T.PRODUCT,'p')  = nvl(S.PRODUCT,'p')  )                                                 
                        );

Open in new window

0

Oracle DatabaseSponsored by Jamf Now

78K

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.