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 form that has 6 data blocks and several items. When a user enters/queries an item, just before the user changes any item I would like to save the old values to a database table by using a trigger. Then I will later figure out how to save the changes to another table by using a trigger.
0
Get free NFR key for Veeam Availability Suite 9.5
LVL 1
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

I have a large stored procedure and at the end I have a  "COMMIT;" statement.

How do I rollback if the COMMIT statement fails?
0
In Oracle Sql developer, how do you script out a table definition, so that when you want to create the table in another database, you don't have to create the table manually?
0
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
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
I am getting an ORA-00984: column not allowed here error message?
I just have a table named "SIMPLE" with one 30 character column named "SM" can someone tell me why I am getting the error message when I run the code below?

declare
lvQuery varchar2(100);
lala varachar2(10);
begin
lala:='Smile';

lvQuery := 'INSERT INTO SIMPLE(SM) VALUES  ('   || lala || ')' ;

END;
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
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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
Write an update statement to remove double-quotes and carriage-return, line-feeds out of a columns data.

--I thought this would work to remove the carriage-returns, line feeds but get a
--ora-01722:invalid number error where the datatype for column1 is varchar2(50).
Update mytable
set column1 = replace(column1, chr(13) + chr(10), '')

--tried for removing the double-quotes and did not remove them.
Update mytable
set column1 = replace(column1, '"', '')

Using Oracle 11g

Thanks
0
I am calling a stored from within another stored procedure. When I execute the calling stored procedure I get the error message:

Error PLS-00222: no function with name 'Generator' exists in this scope

I don't have a function. My stored procedure is named "Generator". Why does it think I am executing a function?


EXECUTE IMMEDIATE Generator ('tTable',inParam) INTO newVal;
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
I have a letter America
how may letter s of A we have in this
how to find it using a select statement
0
Suppose I am in Sql developer and open a new window to perform a sql query. How should I declare a variable? My first line of code below is causing the issue and I don't know why? All I need is to declare a variable on the fly, and then use it. Apparently the Declare statement is not liked by the compiler.


DECLARE MY_NUMBER(10);

SELECT COUNT(1) FROM THE_TABLE;
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
How can I store my count value when I execute the dynamic query at line 6 below?

1  queryString VARCHAR(200);
2  FINALVALUE int;
3  COUNTER int;

4   SELECT COUNT(1) INTO COUNTER FROM CLIENT -- gets the number of records -- Hurrah This works !!!!


5   queryString:='SELECT COUNT(1) INTO COUNTER FROM CLIENT';
6   FINALVALUE := execute immediate queryString;
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

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.