Go Premium for a chance to win a PS4. Enter to Win

x

Oracle Database

78K

Solutions

25K

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 have a very large Oracle Stored procedure. I wold like to be able to test it in sections. Is there a way deliberately exit an Oracle stored procedure and avoiding any type of rollback? So for example if I just want to test the first 1/3 of the stored procedure, and then exit the stored procedure committing any changes, is there a way to do that? If so, how do I do it?
0
New feature and membership benefit!
LVL 11
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Hi Experts,

I have below data in CLOB column, and I want to get xyz.com from below data. DOMAIN NAME LIST will not change and it will be same all the time

{
    "OBJECT ID":"71864756",
    "OBJECT NAME":"MESSENGER",
    "PARENT ID":"71865529",
    "OBJECT STATUS":"O",
    "WHITE LISTED":"N",
    "LAST UPLOAD":"",
    "PARENT CLASS":"ORDER",
    "IS A LINK OBJECT IND":"N",
    "AOL IM FEDERATION":"false",
    "ATTRIBUTE 1":"0",
    "ATTRIBUTE 2":"0",
    "ATTRIBUTE 3":"0",
    "ATTRIBUTE 4":"0",
    "ATTRIBUTE 5":"0",
    "CLASSIC CENTER INTEGRATION URL":"",
    "COMMENTS":"",
    "DISPLAY NAME":"",
    "DOMAIN NAME LIST":"xyz.com",
    "ICON NAME":"SERVICE_16.PNG",
    "IM LICENSES":"500",
}

Please help me by using dbms_lob.substr.....
0
why we need junction table for many to many.

i am not clear on that concept. can you please elaborate with example tables with data?
i am going through below link but not completely clear
https://www.youtube.com/watch?v=P_nhBKs25DQ
please advise
0
The attached snippet is from a BASH script where I use Oracle SQL*Plus Ver. 11.2 to extract the last 6 hours of values for the KEYs listed.  The variable $DATA_VALUES_CURRMM is the database table for the current month that I pass to sqlplus.  What I would like to do is put the KEYs in a .csv file and pass the file to sqlplus using a BASH script.  The sqlplus portion of the script is attached.  The Experts helped me with this script before.  Any other suggestions would be appreciated.
SQLSnippet.sql
0
hi i have upgrade report to 11gR2 from 6i i what to download my txt report in user worksatation i what a dialog where user can select a location to download report have anyone done that
0
In Nov 4, I exported a full database from an old database (oracle 10gr2), and import the data file into a new database (oracle 12c). After a week, we found that some application still insert the data into the old database. So I give myself a task to find the missing data (about 159 records in a specific table )from the old database and put it into the new database.

First, I tried the COPY command:

COPY FROM someuser/oldpaswd@//192.168.1.46:1521/OLDORCL to someuser/newpaswd@//192.168.1.26:1521/NEWORCL
 CREATE plus_circular_profile_new USING (select * from plus_circular_profile where sys_date >= to_date('05-NOV-17','DD-MON-YY' ));

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

SQL> desc plus_circular_profile;
 
 ID_OF_ROW              NOT NULL RAW(16)         <------ this is the culpit
 ID_OF_CIRCULAR    NOT NULL NUMBER(18)
 ID_OF_CONSUMER NOT NULL NUMBER(18)
 FIRST_NAME            VARCHAR2(50)
 .........

Open in new window


Due to I have the raw type in the table, this is certainly not possible to do.

Then I turn around to use imp/exp utilites.
exp tables=plus_circular_profile query="""where sys_date > to_date('04-NOV-17','DD-MON-YY' )"""
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table          PLUS_CIRCULAR_PROFILE        159 rows exported

Open in new window


But I have trouble to import the data into the new table:

imp someuser/newpaswd fromuser=someuser touser=someuser file=EXPDAT.DMP rows=Y ignore=Y log=import111617.log

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_PK) violated
Column 1 EA6AEC75335D4B1F9DCECF852D2165ED
...........

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_UK) violated
Column 1 DDA0613DE4354DC5B03C4FCC47FF3726

Open in new window


For the primary key violation:
ID_OF_ROW                       NOT NULL RAW(16)         <------ this is the primary key

For the Unique Key violation:
ID_OF_CIRCULAR              NOT NULL NUMBER(18)  <------- this is the unique key

I know the RAW type record could be possibly duplicated from one physical server to another after the database migration.
For the duplicated ID_OF_CIRCULAR it is because the production of the past week had generated the same id number because it always find the currently biggest number in the table and increment by 1 when needed. so there must have some record in the new database have exactly same ID_OF_CIRCULAR.

My question is: How can I get these 159 records into the new database?

Thank you for any help.
0
Hi Experts,

I need help on update the existing sql based on below requirement.

I have a three tables

1) Shield
2) OL
3) Cross_Ref

I need to create the new table ' Result' by using above table data. Here i am sharing the table data, and the SQL i have created .
Basically i need help on update that sql to get final output showed in the excel file.

Please let me know if  any questions.
test_data.xlsx
0
hi,
What is normalization concept


if a table called Student as below with 4 columns and data as below

Student table

StudentName YearOfStudy Subject TeacherName
john                    10thGrade    Maths    Ashley

why it is better to move away Teacher information to separate table as below

Teacher Table

TeacherName TeachingSubject
Ashley                 Maths


what is various types of normalizations?
Please advise
0
Hi,
I have date_created datatype date showing in sql developer as 06-09-29 can I display it in yyyy-mm-dd format.

Thanks
0
The following errors appear when running backups and Oracle instance must be restarted:

Wed Nov 15 03:08:01 Hora estándar romance 2017
Thread 1 advanced to log sequence 10606 (LGWR switch)
  Current log# 4 seq# 10606 mem# 0: K:\SORANA1\SORANA1\REDO04.LOG
Wed Nov 15 04:00:03 Hora estándar romance 2017
Errors in file k:\sorana1\admin\sorana1\bdump\sorana1_lgwr_25960.trc:
ORA-00345: redo log write error block 4295 count 1
ORA-00312: online log 4 thread 1: 'K:\SORANA1\SORANA1\REDO04.LOG'
ORA-27070: async read/write failed
OSD-04016: Error al poner en la cola una solicitud de E/S asíncrona.
O/S-Error: (OS 1784) El búfer del usuario no es válido para esta operación.

Wed Nov 15 04:00:03 Hora estándar romance 2017
Errors in file k:\sorana1\admin\sorana1\bdump\sorana1_lgwr_25960.trc:
ORA-00340: IO error processing online log 4 of thread 1
ORA-00345: redo log write error block 4295 count 1
ORA-00312: online log 4 thread 1: 'K:\SORANA1\SORANA1\REDO04.LOG'
ORA-27070: async read/write failed
OSD-04016: Error al poner en la cola una solicitud de E/S asíncrona.
O/S-Error: (OS 1784) El búfer del usuario no es válido para esta operación.

Wed Nov 15 04:00:03 Hora estándar romance 2017
LGWR: terminating instance due to error 340
Instance terminated by LGWR, pid = 25960
Wed Nov 15 06:44:02 Hora estándar romance 2017
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Wed Nov 15 06:44:02 Hora …
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi,
what is best way to find dups data looks like below:

id   cntry
123 ca
123 ca
123 us
456  ca
456 ca
657  mx
657  mx
657 us
657 us

i want results like
123 ca
123 us
657  mx
657 us
0
Hello experts,

Can you please suggest any good performance tuning tools for oracle database which will also help sql and plsql code tuning too.
0
I need to print a screen or oracle database 12c security settings.

Is there a tool or a screen that shows that? if not is there a view or table that can show the settings.
0
I want to convert the date to a year/month.  An example would be the date is 01/10/2017 and converted to 201701.

And so any date within January would convert to 201701.  Any date within February would be 201702 etc.

Can this be done and if yes, how?

Thanks, Scott
0
does it go for full table scan or what?
0
I have the following SQL which really well to pull the sum of the registrations by week

SELECT
 EVT_CFG_ITEM.DESCRIPTION,
  sum(EVT_REG_ITEM.QTY),
  cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-09-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-16-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 2'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-23-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 3'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('11-30-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 4'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-07-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 5'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-14-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 6'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   to_date('12-21-2017 23:59:00','MM-DD-YYYY HH24:MI:SS') THEN 'Week 7'
WHEN EVT_REG_ITEM.TRANS_DT BETWEEN   to_date('11-03-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND   

Open in new window

0
Hi,

I have a two tables and one reference table.
STABLE
OTABLE
REFERENCE

We need to create the sql that should create  a new table based on the above table.
in the given data
stable have 10 rows
otable have 10 rows
and matched 3 rows in reference table

in the final output table , I need the all the rows from the stable and otable columns along with one new column
will show like this
matched = Y  OR ' IN STABLE'  OR  'IN OTABLE'
0
Dear All,

Iam working on oracle form personalization.

It has a block RES_PRICING_MODIFIERS and it has max 1 or more rows. Some time no rows.

The block name is RES_PRICING_MODIFIERS and it has

list_line_id, list_name, discount_type
and so on till last records.

I have a requirement .

There is a button in the form. If i press the button .it has to loop through the block and get the value of the list_line_id field and store the values in a global variable

like g_temp := 1,2,3,...;

You help is highly appreciated .

Thanks
0
I've just installed Oracle 10g Express on my home computer but when I try to go to the Database Home Page,
I get this error:  "This site is inaccessible 127.0.0.1 does not allow the connection.".
 I'm using Windows 10.  

  I've run "lsnrctl status" and here is the lsnrctl  output:

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-NOV. -2017 17:57:18

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUT du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Date de dÚpart                       11-NOV. -2017 16:03:51
DurÚe d'activitÚ                    0 jours 2 heures 1 min. 52 sec
Niveau de trace           off
SÚcuritÚ                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramÞtres du processus d'Úcoute     C:\app\USER\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Fichier journal du processus d'Úcoute             C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 1 …
1
How to Use the Help Bell
LVL 11
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

I am trying to install Oracle on my computer and I'm having some issues.  I unzipped the file and it brought up the same window again.  I was wondering if anyone had some directions on how to get this properly installed, it would be greatly appreciated.  Attached are screenshots of what is in my download folder.

Thanks.
Oracle-Unzip.JPG
Oracle-Installation.JPG
0
Hello All,
 
This is regarding Oracle Alerts,
 
1. I wanted to create AFTER UPDATE event base alert, But i want to fire alert only if specific column is updated.
For eg ; Table TABLE_ABC and if column COL1 is updated, then only alert should run and notification should be sent,
If any other column of same table is updated, then notification should not be sent.
 
2. Can we get old and new value in oracle alert AFTER UPDATE event.
 
Could you please help to get this done through Oracle Alerts.?
 
Thanks In advanced.
0
We have a hosted database (Oracle 12c) that I am trying to connect my old 6i forms/reports too. Please guide me as to what to ask our network manager based on the following trace file below: (Oddly enough, I have no problem connecting to the database using 32bit toad on Windows 10)

Thanks in advance!
--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
New trace stream is "C:\orant\bin\sqlnetf4.trc"
New trace level is 16
--- TRACE CONFIGURATION INFORMATION ENDS ---
ntvllt: entry
ntvllt: No PROTOCOL.ORA file is found
ntvllt: exit
nigini: entry
nigini: Count in NI global area now: 1
nigini: Count in NI global area now: 1
nrigbi: entry
nrigbni: entry
nrigbni: Unable to get data from navigation file tnsnav.ora
nrigbni: exit
nrigbi: exit
nigini: exit
niqname: Using nnfsn2a() to build connect descriptor for (possibly remote) database.
nnftboot: entry
nnftboot: exit
nnfoboot: entry
nnfoboot: exit
nnfoboot: entry
nnfoboot: exit
nnfhboot: entry
nnfhboot: exit
nncpmlf_make_local_addrfile: construction of local names file failed
nncpmsf_make_sys_addrfile: system names file is C:\orant\NET80\admin\tnsnames.ora
nncpcin_maybe_init: first request sent to name server will have ID 0
nncpcin_maybe_init: initial retry timeout for all name servers is 1500 csecs
nncpcin_maybe_init: max request retries per name server is 1
nngsini_init_streams: initializing stream subsystem, cache size is 10
: entry
: about to malloc 1
: about to call sltsini
: about to create mutex_nsgbl
: about to call 

Open in new window

0
Hi expert,

i'm facing below issue while executing my query since object is exists there.

DECLARE
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P078
ORA-08103: object no longer exists
ORA-06512: at "SUPERUSER.F_IDRP_WRITE_TO_FILE", line 33
ORA-06512: at line 1
ORA-06512: at line 25

Please suggest for optimal way to avoid this kind of error.

# Please let me know if required any additional info .

thanks in advance
0
Hi All,

I have been working on a sql query and quite close to what I want. If you look at Results 2 below everything is correct except for the first column 'RT_FROM_REQ_ID' as I want this to be RQ_REQ_NAME as shown in Results1. The RT_FROM_REQ_ID is equal to the RQ_REQ_ID in the REQ table. I can easily get the RQ_REQ_NAME from Query 1 but not sure how to have main main query show the equivalent name for the 'RT_FROM_REQ_ID'. If I add RQ_REQ_NAME to the second select in Query 2 I get a too many values error. I hope this makes sense. Any ideas on how I can

Query 1
SELECT RQ_REQ_ID,RQ_REQ_NAME
FROM REQ
WHERE RQ_TYPE_ID = 2
AND RQ_FATHER_ID NOT IN (8880,9104,9165,)

Open in new window


Results 1
Results1
Query 2
Select * FROM
(
Select RT_FROM_REQ_ID,RQ_REQ_STATUS
FROM REQ_TRACE,REQ
WHERE RT_TO_REQ_ID = RQ_REQ_ID
AND RT_FROM_REQ_ID IN
(
SELECT RQ_REQ_ID
FROM REQ
WHERE RQ_TYPE_ID = 2
AND RQ_FATHER_ID NOT IN (8880,9104,9165)
)
)
pivot
(
Count(RQ_REQ_STATUS)
for RQ_REQ_STATUS IN ('Not Covered','Not Completed','Failed','No Run','Passed','Blocked')
)

Results2
Results2
0
getting this error trying to run a full rman backup
any ideas?

oracle 11.2.0.4
aix 7

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/07/2017 14:55:51
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
0

Oracle Database

78K

Solutions

25K

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.