MySQL Forums - Oracle

How to update Unique Key Column? (1 reply)

Sathiya Moorthy — Tue, 18 Jun 2013 04:32:23 +0000

Hi All,

I have Unique key column for `Mobile`. I am getting the Duplicate key entry Error.
Infact, i am not going to enter the duplicate value into the unique column, but i entered the same value in update query.

For ex:
if Table looks like

Username | Mobile(UniqueKey)
abc | 123
bbc | 456

if I am going to execute the following query means, i will get an error like 'Duplicate key '123' for `Mobile`

Update table1 set `username`='TEST'
MOBILE = '123'
WHERE `USERNAME`='abc'

PLEASE ANYONE CLARIFY REGARDING THIS ISSUE

THANKS IN ADVANCE.
SATHIYAMOORTHY

NESTED ERROR when making admin server a windows service (1 reply)

Erick Ortiz — Wed, 08 May 2013 14:08:56 +0000

I created a script to make my admin server a windows service. I successfully made it a service but when it starts it then shuts itself down. i ran a debug and found a error. I dont get what to do. I have tried changing the variables in the script and try it over and over again .....here is my script code and here is the error i got from debug output:

script

SETLOCAL

set DOMAIN_NAME=FRClassicDomain

set USERDOMAIN_HOME=C:/Oracle/Middleware/user_projects/domains/FRClassicDomain

set SERVER_NAME=FRweblogic

set WLS_USER=FRweblogic

set WLS_PW=hidden

set ADMIN_URL=http://hidden:7001

set JAVA_VENDOR=Sun

set JAVA_HOME=C:/Program Files/Java/jdk1.7.0

set PRODUCTION_MODE=true

set MEM_ARGS=-Xms512m –Xmx512m

call "C:/Oracle/Middleware/user_projects/domains/FRClassicDomain/bin/setDomainEnv.cmd"

call "C:/Oracle/Middleware/wlserver_10.3/server/bin/installSvc.cmd"

ENDLOCAL

my debug output:

...............

There are 1 nested errors:

weblogic.management.ManagementException: [Management:141223]The server name FRwe
blogic specified with -Dweblogic.Name does not exist. The configuration includes
the following servers {AdminServer,WLS_FORMS,WLS_REPORTS,FRasserver}.
at weblogic.management.provider.internal.RuntimeAccessImpl.(Runtim
eAccessImpl.java:149)
at weblogic.management.provider.internal.RuntimeAccessService.start(Runt
imeAccessService.java:41)
at weblogic.t3.srvr.ServerServicesManager.startService(ServerServicesMan
ager.java:461)
at weblogic.t3.srvr.ServerServicesManager.startInStandbyState(ServerServ
icesManager.java:166)
at weblogic.t3.srvr.T3Srvr.initializeStandby(T3Srvr.java:881)
at weblogic.t3.srvr.T3Srvr.startup(T3Srvr.java:568)
at weblogic.t3.srvr.T3Srvr.run(T3Srvr.java:469)
at weblogic.Server.main(Server.java:71)

te changed to FAILED>

te changed to FORCE_SHUTTING_DOWN>

C:\Oracle\Middleware\wlserver_10.3\server\bin>

Replication for Oracle and MySQL - Now 100% Open Source (no replies)

Petri Virsunen — Mon, 29 Apr 2013 18:42:44 +0000

Oracle is the most powerful DBMS in the world. However, Oracle's expensive and complex replication makes it difficult to build highly available applications or move data in real-time to data warehouses and popular databases like MySQL.

In this webinar you will learn how Continuent Tungsten solves problems with Oracle replication at a fraction of the cost of other solutions and with less management overhead too – picture Oracle GoldenGate without the price tag! We will demo constructing a highly available site using Oracle-to-Oracle replication. We will then show you how to replicate data in real time from Oracle to MySQL as well as load a data warehouse.

You may obtain open source Tungsten Replicator at http://code.google.com/p/tungsten-replicator. Alternatively you may also obtain Continuent Tungsten, full-featured solution with 24/7 support, from Continuent at sales@continuent.com

Register for a session now by clicking a date below:

• Thursday, May 2, 2013 @ 10:00 AM PDT/11 AM EDT, https://www1.gotomeeting.com/register/256313281
• Tuesday, May 7, 2013 @ 10:00 AM EDT/15:00 BST/16:00 CEST, https://www1.gotomeeting.com/register/394798433

Once registered you will receive an email confirming your registration with information you need to join the webinar.

how to start oracle DB (2 replies)

sudarshan patil — Tue, 28 May 2013 05:40:06 +0000

ways to start oracle sid

startup nomount
startup mount
start open

Turn INSERT INTO query into UPDATE (2 replies)

Jack Hard — Sat, 25 May 2013 23:04:27 +0000

I have a piece of software that runs an INSERT INTO query in my MySQL database. I'd like this software to update a row in the table it is accessing instead of creating a new row. I have no access to change the query in the software, so I'm wondering if there is some way to make this happen by other means. Is there a setting I can change on my table, or can I create a new view that the software can use that will make this work? Is this even possible?

Migration of Oracle Stored Procedure to MySQL (1 reply)

Anirudha Jalgaonkar — Sat, 25 May 2013 23:04:04 +0000

Hello,

We are trying to migrate the Oracle stored procedures/functions in MySQL.

However, we are having confusion/doubts about few utilities/standard functions that Oracle provided that are available in MySQL or not.
For example, we defined few table types, REF CURSOR, %ROWTYPE variables, using EXECUTE IMMEDIATE function, and to write the data in file we are using UTL_FILE.file_type.

Since we are new to MySQL and do not have buffer time to study and modify the existing logic that in Oracle. So, requesting to share any document or any standard tool that will help us to convert the existing Oracle logic to MySQL.

Thank you.

Migrate from Oracle to MySQL (3 replies)

Nassyam Basha — Mon, 25 Feb 2013 16:39:13 +0000

Oracle version: 10.2.0.1.0
OS: RedhatLinux 4.1.2-14

Application information: VB
Database API : ODBC

We are planning to migrate Oracle databases to MySQL, Below are my questions please clarify
1) What are the pre-requisites to check before migration?
2) All MySQL versions are supported to migrate?
3) What is the best way to migrate database?
4) Is there any tool to migrate or we have to do manually?
5) If migrations tools available, they are open source?

Thanks.

Convert oracle SQL statement to MySQL (2 replies)

super sobbie — Thu, 15 Nov 2012 14:45:57 +0000

Good day. I am converting an oracle database to a mysql database. One of the scripts that is currently running on the oracle system is as follows:

UPDATE AR_CUST_MASTER a
SET ARCM_SHORT_NAME =
(SELECT ARCM_SHORT_NAME
FROM AR_CUST_MASTER b
WHERE b.ARCM_CUSTOMER_NO =
LPAD (
TO_CHAR (TRUNC (a.ARCM_CUSTOMER_NO / 1000) * 1000),
9,
'0')
AND MOD (b.ARCM_CUSTOMER_NO, 1000) = 0
AND a.ARCM_COMPANY = b.ARCM_COMPANY)
WHERE MOD (a.ARCM_CUSTOMER_NO, 1000) != 0;

I am having issues converting this to mySQL. Any help would be greatly appreciated.

Kind Regards,
supersobbie

Convert a SP containing "connect by" and "TYPE" (no replies)

Tony ding — Fri, 05 Oct 2012 21:21:10 +0000

Hello, i have a SP in Oracle doing the following things that needs to be migrated to MySQL.

Not sure if MySQL can accomplish the things we asked here. Please give some advice.

1. Requirement
There is a table with 3 columns

ID |Last_Modified | Flag
1 |2011-12-11 02:00:00| 0
2 |2011-12-13 02:00:00| 0
3 |2011-12-02 02:00:00| 0
4 |2011-12-12 02:00:00| 0

the stored procedure “getback(String pId, String pLast_Modified). E.g. pId =”1,2,3” and pLast_Modified = “2011-12-11 02:00:00, 2011-12-13 02:00:00, 2011-12-01 02:00:00”.

The logic is that, for the same ID, if the last_modified in the table is later than the passed last_modified, then set the flag for that record to “1”.

2. Stored procedure in Oracle

--procedure
create or replace procedure test1(
p_ids varchar2,
p_dates varchar2
) IS
TYPE id_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
TYPE date_type IS TABLE OF DATE INDEX BY VARCHAR2(64);

ids id_type;
last_updated date_type;
lvar1 varchar2(200);
lvar2 varchar2(200);

cursor c1 is
With ids as (select rownum id, regexp_substr(p_ids,'[^,]+', 1, level) val from dual
connect by regexp_substr(p_ids, '[^,]+', 1, level) is not null),
lupdated as (select rownum id, regexp_substr(p_dates,'[^,]+', 1, level) val from dual
connect by regexp_substr(p_dates, '[^,]+', 1, level) is not null)
select lut.id,
case when lut.last_updated > to_date(lu.val,'yyyy-mm-dd hh24:mi:ss') then 1
else 0
end flag
from last_updated_tbl lut
,ids
,lupdated lu
where lut.id = ids.val
and ids.id = lu.id
;

BEGIN

open c1;
loop
fetch c1 into lvar1,lvar2;
exit when c1%notfound;

dbms_output.put_line(lvar1||' flag:'||lvar2);

end loop;

close c1;

END;

3. Issues

MySQL doesn't support "Connect By" and "TYPE" and others here. Is there anything i can do to workaround those?

Thanks

Tony

Real-time Replication Between Oracle and Oracle, and Oracle and MySQL (no replies)

Petri Virsunen — Mon, 01 Oct 2012 18:52:02 +0000

Thursday, October 18, 2012 10 am PDT
https://www1.gotomeeting.com/register/780401048

Oracle is the most powerful database system in the world. However, Oracle's expensive and complex replication makes it difficult to build highly available applications or move data in real-time to data warehouses and popular databases like MySQL. In this webinar you will learn how Continuent Tungsten solves problems with Oracle replication at a fraction of the cost of other solutions and with less management overhead too – picture Oracle GoldenGate without the price tag!

We will demo constructing a highly available site using Oracle-to-Oracle replication. We will then show you how to replicate data in real time from Oracle to MySQL as well as load a data warehouse. Continuent Tungsten is a very well-known solution for advanced, easy to use and fast replication for MySQL. Join us to see how we can help you with Oracle as well!

sum(case when max(date) then 1 else 0 end) (no replies)

Hal Campbell — Sat, 15 Sep 2012 13:43:56 +0000

I am new to MySQL so I thank you in advance for your help.

I have a subscription table that contains subscriptions for a user. Each user could have multiple rows indicating their subscription history. Each rows in the subscription table contain "Date Created"=created_date and "Expiration Date" = exp_date.

I need to calculate the total number of rows where created_date is within a date range and number of rows that contains exp_Date within a date range.

For exp_date I need to check the oldest or MAX exp_date.

Here is what I've tried but using Max within the case doesn't work.

Select
SUM(Case when created_date between 'yyyy-mm-dd' and 'yyyy-mm-dd' then 1 else 0 end) as Created_Count
sum(case when max(Exp_Date between 'yyyy-mm-dd' and 'yyyy-mm-dd' then 1 else 0 end) as Exp_Count

High Availability and More with Tungsten Oracle Replication (no replies)

Petri Virsunen — Wed, 05 Sep 2012 23:16:15 +0000

Oracle is the most powerful database system in the world. However, Oracle's expensive and complex replication makes it difficult to build highly available applications or move data in real-time to data warehouses and popular databases like MySQL.

In this webinar you will learn how Continuent Tungsten solves problems with Oracle replication at a fraction of the cost of other solutions and with less management overhead too – picture Oracle GoldenGate without the price tag!

We will demo constructing a highly available site using Oracle-to-Oracle replication. We will then show you how to replicate date in real time from Oracle to MySQL as well as load a data warehouse. Continuent Tungsten is a very well-known solution for advanced, easy to use and fast replication for MySQL.

Join us to see how we can help you with Oracle as well!

Thursday, September 13th
10:00 am PT / 1:00 pm ET/ 19:00 CET
https://www1.gotomeeting.com/register/655721393

CONNECT BY in MYSQL - Help me plz!! (no replies)

Utkarsh Varma — Tue, 21 Aug 2012 14:58:12 +0000

can i convert this in to Mysql

SELECT
count(dt)
INTO
p_days
FROM(select to_date(v_start_date, 'DD-MM-YYYY') + rownum -1 dt from dual connect by level <= to_date(v_end_date, 'DD-MM-YYYY') - to_date(v_start_date, 'DD-MM-YYYY') + 1) where to_char(dt,'fmday') not in ('sunday','saturday');

help me plz !!

Convert Oracle stored procedure using REF_CURSOR to MySQL - Help me plz (no replies)

Utkarsh Varma — Thu, 16 Aug 2012 11:41:32 +0000

Hi,

I am trying to convert Oracle Stored Procedure using REF_CURSOR to MYSQL, but facing lots of issues. Can you produce the equivalent code for MySQL?

Here is my procedure in Oracle:

create or replace
PROCEDURE SP_CAPPLAN
(
p_attid IN String,
p_pmt IN String,
p_phase IN String,
p_application IN String,
p_out OUT String
)

AS
v_start_date String(30);
v_end_date String(30);
v_date String(30);
v_tot_days NUMBER;
v_allocated_hrs NUMBER;
v_per_allocation NUMBER;
v_location String(30);
v_hrs String(30);
query_str VARCHAR2(1000);
p_days VARCHAR2(5000);
TYPE cur_typ IS REF CURSOR;
c cur_typ;
BEGIN

SELECT
to_char(STARTDATE, 'DD-MM-YYYY'),to_char(ENDDATE, 'DD-MM-YYYY'), actual_allocated_hrs
INTO
v_start_date,
v_end_date,
v_allocated_hrs
FROM
RESOURCE_ALLOCATION_TABLE
WHERE
attuid = p_attid AND
pmt_work = p_pmt AND
phase = p_phase AND
application = p_application;

SELECT
unique(location)
INTO
v_location
FROM
resource_details
WHERE
attuid = p_attid;

SELECT
PERCENT_ALLOCATION
INTO
v_per_allocation
FROM
ITUP_MILESTONE_DETAILS
WHERE
PHASE_DESC = p_phase;

DELETE
FROM
RESOURCE_WORK_ALLOCATION_DET
WHERE
ATTUID = p_attid AND
PMT = p_pmt AND
PHASE = p_phase AND
APPLICATION =p_application;
COMMIT;

IF v_location is null THEN
v_location := 'Offshore' ;
END IF;

SELECT
count(dt)
INTO
p_days
FROM(select to_date(v_start_date, 'DD-MM-YYYY') + rownum -1 dt from dual connect by level <= to_date(v_end_date, 'DD-MM-YYYY') - to_date(v_start_date, 'DD-MM-YYYY') + 1) where to_char(dt,'fmday') not in ('sunday','saturday');

query_str :='select dt from(select to_date('''||v_start_date||''', ''DD-MM-YYYY'') + rownum -1 dt from dual connect by level <= to_date('''||v_end_date||''', ''DD-MM-YYYY'') - to_date('''||v_start_date||''', ''DD-MM-YYYY'') + 1) where to_char(dt,''fmday'') not in (''sunday'',''saturday'')';
OPEN c FOR query_str ;
LOOP
FETCH c INTO v_date;
EXIT WHEN c%NOTFOUND;
--v_hrs := ROUND(v_allocated_hrs*v_per_allocation*0.01/p_days,2)||'' ;
--Actual allocated hours added by team lead for a particular phase particular resource
--start date and end date added by team lead for woriking of the actual allocated hours
v_hrs := ROUND(v_allocated_hrs/p_days,2)||'' ;
-- v_hrs is what a resource will work per day
INSERT INTO RESOURCE_WORK_ALLOCATION_DET(ALL_DATE,ATTUID,PMT,PHASE,APPLICATION,HOURS,LOCATION) VALUES(v_date,p_attid,p_pmt,p_phase,p_application,v_hrs,v_location);
COMMIT;
END LOOP;
CLOSE c;
p_out := 'Success';
END SP_CAPPLAN;

Thanks in advance!

Oracle (2 replies)

Selva Karthi — Tue, 18 Sep 2012 06:15:18 +0000

how to convert oracle database to mysql?

Regards,
Selva.
http://natarajmasala.com/

Migration of Oracle 9i to MySQL 5.5 (3 replies)

Larry Cronin — Tue, 18 Sep 2012 06:11:04 +0000

Hello

I am pretty sure you can do this but I want to make sure. It IS possible to migrate from Oracle 9i to MySQL 5.5? 9i is a few revs behind and I just want to make sure before we tackle the project. Most of the posts regarding this are a few years old.

Thanks

Larry C

Replicate between MySQL and Oracle - without expense of GoldenGate (no replies)

Petri Virsunen — Fri, 22 Jun 2012 21:13:52 +0000

http://flyingclusters.blogspot.com/2012/06/replicate-from-oracle-to-mysql-without.html

Is there any way to push the data from MYSQL to ORACLE ? (6 replies)

RENGNATHAN M G — Thu, 27 Sep 2012 09:04:43 +0000

Hi All,

i want to push bulk data from mysql tp oracle. Is there any wutomatic way to do it?

is there any connection link we can create between oracle and mysql.

Data synchronization between Oracle & MySQL databases (1 reply)

Tammy Doan — Fri, 22 Jun 2012 21:15:59 +0000

Hello,

I'm looking for a DB sync software that can run on Linux to perform data synchronization between one Oracle master DB & multiple MySQL DB's. I've searched & found DBSync but don't know how good it is & whether or not it would run on Linux. Has anyone used DBSync or any other DB sync software & would recommend?

Thanks.

How can you compare a part of the name rather than the entire name? (no replies)

Andrew Jackson — Tue, 29 May 2012 10:50:51 +0000

SELECT * FROM people WHERE empname LIKE '%ab%'Would return a recordset with records consisting empname the sequence 'ab' in empname.

How to convert some Oracle to MYSQL Queries (no replies)

Luisa Schulze — Tue, 08 May 2012 08:51:38 +0000

Hello,
i want to convert Oracle Queries to MYSQL Queries, but i have problems with some of them.

The Queries are like this:

1) MERGE INTO tab1 d1
USING tab d2
ON (d1.id = d2.id)
WHEN MATCHED THEN
UPDATE SET d1.zufriedenheit = 'Merge Update'
WHEN NOT MATCHED THEN
INSERT (d1.id, d1.typ, d1.active, d1.zufriedenheit)
VALUES (d2.id, d2.typ, d2.dpr_scd_active,'Merge Insert');

2) SELECT
REGEXP_REPLACE(tab1, '(.)', '\1 ') "REGEXP_REPLACE",
REGEXP_SUBSTR(tab1,'([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR", tab2
FROM tab2
WHERE REGEXP_LIKE (tab2, '([8])\1', 'i');

3) SELECT *
FROM
(select tab1, tab2, avg(tab3),
DENSE_RANK() OVER ( PARTITION BY tab1 ORDER BY(tab3)) as Rang
FROM
.
.
.
where Rang < 11
..;

4) SELECT * FROM (
......)
WHERE rownum <= 100;
(I have found a alternative for rownum, but with the constraint rownum <= 100 it doesn´t work.)

5) SELECT tab1, tab2,
SUM(tab2) OVER (PARTITION BY tab1) as res1,
tab3,
SUM(tab3) OVER (PARTITION BY tab1) as res2,
tab4,
SUM(tab4) OVER (PARTITION BY tab1) as res3,
from (
.
.
.

Maybe you can help me with some of them.

Create db link in MySQL (no replies)

Sudhanshu Umalkar — Wed, 02 May 2012 03:33:15 +0000

I have 2 MySql db instances, let's say one on server1 and the other on server2. The servers are at two different physical locations. Is it possible to create a db link between these two db instances so that I can access data from both these databases using a single query?

If no db links, then what other options do I have?

Thanks.

Is there a command in MySQL to capture the name of the client applications that are open session in the database? (no replies)

William Mometti — Tue, 10 Apr 2012 19:02:23 +0000

Hello,

I wanted to ask if anyone has tried the command Oracle "sys_context ('USERENV', 'MODULE')" equivalent in MySQL?

In other words, wanted to know if I can capture the name of the client application that the session opened in MySQL?

Thank you for listening and sorry for english I used a translator ...

How to send a list of user defined objects to stored procedure? (no replies)

Lithwin Justin — Thu, 22 Mar 2012 11:53:40 +0000

Hi,

I use Springs and hibernate.

How to send a list of user defined objects to a stored procedure?

Here's my scenario:
I have a table 'TEST' with two columns 'ID' and 'NAME'
From java, I have a list of Domain objects with two variables 'id' and 'name' that are mapped to this 'TEST' table by hibernate.
I want to insert the data that are available in the list of mapped domain objects into the 'TEST' table and also do some other operation on the DB side for each data in the list. So I have chosen the stored procedure to do the insert and do the rest of the needed operation. As there will be millions of records (in the list) to be inserted, I dont want to do my iteration in java and call my stored procedure every time considering the fact that my DB layer stays on a different machine.

Is there any way that MYSQL can understand these objects if I send them to the stored procedure?
Is there any way to BIND them so that they can understand the domain objects and start inserting?

Any lights on this would be highly appreciated.

How to insert data into Oracle db from MySQL db through PHP? (1 reply)

pratik kanawade — Fri, 09 Mar 2012 16:24:20 +0000

Hi,

I want to insert my MySQL data into Oracle database through PHP.
I can access Mysql database using mysql_conect() & Oracle database using oci_connect() through PHP.
Now How can I insert my data which is in MySQL into Oracle table. Both table structure are exactly same.
So I can use
insert into Oracle_Table(Oracle_columns....) values(Select * from MySQL_Table);
But again problem is I can't open both connections at the same time.

So has anyone done this before or anyone having any other idea..

Plz guide me...

Moving Data in Real-Time between Oracle and MySQL (no replies)

Petri Virsunen — Thu, 08 Mar 2012 22:25:37 +0000

https://www1.gotomeeting.com/register/981722992
Webcast, Thursday, 3/15 @ 10 am PT/1pm ET

Moving data between legacy Oracle databases and cheap, flexible MySQL-based applications is an increasingly important problem for open source-based businesses. Do you need to move sales requests from MySQL-based web applications to an Oracle-based procurement system? How about from your Oracle ERP applications back to MySQL? Tungsten Replicator leverages open source to move data in real-time at a fraction of the cost of solutions from expensive enterprise vendors.

In this webinar we will show you how to use Tungsten to replicate transactions from Oracle to MySQL as well as MySQL to Oracle. We will describe the setup on each side of the fence and show demos of data movement in both directions. Finally, we will introduce a few of the other replication combinations Tungsten Replicator handles, including MongoDB, Vertica, and PostgreSQL.

Tungsten Replicator makes MySQL data plug-and-play with Oracle without application changes or complex configuration -- Think Oracle GoldenGate™ without the price tag! And Continuent offers the support you need to get replication projects deployed quickly and efficiently. Join us to learn how our heterogenous replication solutions can help you today!

https://www1.gotomeeting.com/register/981722992

character set and collation (1 reply)

jongse park — Fri, 09 Mar 2012 15:23:12 +0000

How can I find collation and character set in oralce?

Is there utf8 and utf8_general_ci in oralce?

COUNT (*) OVER () (1 reply)

jongse park — Wed, 29 Feb 2012 16:56:12 +0000

How can I convert Oralce COUNT(*) OVER() to MySQL?

Need help (no replies)

star config — Mon, 27 Feb 2012 11:42:19 +0000

I am using the following query:

SELECT *
FROM TABLE(GBS_P_B_SPOC_RPRT_FNC('10','D84', '041', q'['BR','NA']' , '', '','703015498'))

I want to use ‘BR’,’NA’ as sdu_code in (‘BR’,’NA’)

But its taking the ‘BR’,’NA’ as a total string and giving wrong output.

Please help
----------------------------------------------
Website Design Sydney | Web Design Sydney | Cheap Web Design Sydney

export Oracle data in MySQL through PHP (2 replies)

pratik kanawade — Sat, 25 Feb 2012 07:13:21 +0000

HI,

Can anybody tried to export Oracle data & fed it to MySQL db through PHP coding?
Plz guide me or tell me how we can achieve this?

I have write code in php to connect to both Mysql & Oracle databases. Now want to move Oracle data into Mysql (using PHP)..

Plz help me..
Thanks..