MySQL Forums - Oracle

NEED YOUR HELP VERY MUCH (no replies)

Gabby Takash — Sat, 21 Nov 2009 14:31:56 +0000

Dear Database Support Professional,

I am Database Administrator and I am writing to ask for your help in responding to a 10-12 minute survey for a degree requirement on relational database support professionals in the USA. Please note that the respondent is completely anonymous. If I don’t get this portion of my degree completed by the end of this semester, then I would have to wait for another year which entails additional costs and time on me. By completing the survey, you will be contributing to research in our field and also there is the satisfaction of having helped a fellow professional and a fellow human being attain his goals. I believe a good turn always bring another in some way in our lives. I therefore kindly beg for your help to a fellow database professional by completing this survey. The link to the survey is provided below. If possible please forward to other database professionals or groups:
http://www.surveymonkey.com/s.aspx?sm=a1JqU7GVt1g6rCkIPyL23w_3d_3d

Thank you earnestly for your kindness.
Gabriel

Every derived table must have an alias (1 reply)

John Dunn — Thu, 19 Nov 2009 16:07:49 +0000

Trying to port the following SQL from Oracle to MYSQL and it gives the error

Every derived table must have an alias

SELECT COUNT(job_id) number_of_jobs FROM
(SELECT distinct(jobs.job_id) FROM active_jobs, jobs
WHERE active_jobs.stage = 'DELETION' AND jobs.deletion_status = 'ERROR' AND jobs.job_id = active_jobs.job_id);

Any idea what the correct syntax is. I need it to run againgst Oracle and MYSQL

Can't call commit when autocommit=true (no replies)

John Dunn — Tue, 17 Nov 2009 16:28:49 +0000

Am trying to convert an application that currently runs with Oracle database to run with MYSQL.

I get the error "Can't call commit when autocommit=true". This appears to be happening on a Select statement(but may be on a commit somewhere)

Can I set autocommit off at some global level(i.e outside the code at a database level??). I do not want to amend the code as it still needs to run with Oracle databases.

JD Edwards Data Archiving Solution (no replies)

Anand Kumar — Fri, 06 Nov 2009 09:46:14 +0000

Organizations can mitigate the impact of data growth by running batch reports during downtimes, purchasing faster hardware or purge data from the application.

None of these strategies fully address the reality of application growth coupled with the need for long term data retention. To maintain performance of JD Edwards database, while ensuring compliance, organizations must Analyze JD Edwards data for a given module or the sum total of the Application database. For more information visit http://www.solix.com/jd_edwards_data_archiving_solution.htm

Oracle ACCEPT Keyword in Mysql (no replies)

Palash Chatterjee — Tue, 03 Nov 2009 05:44:12 +0000

I am new to Mysql,
I was wondering how the ACCEPT keyword of Oracle SQL script will be replaced
in Mysql script, So that user will be asked for runtime input during execution

*******************
ACCEPT Colx PROMPT'Please enter column name:'
ACCEPT cond PROMPT'Please enter lowest salary value:'
SELECT empno, eName, &Colx
FROM Emp
WHERE &Colx > &cond
ORDER BY &Colx;
*******************

regards,
Palash

Error during Migration (no replies)

Aruna Raghunam — Thu, 22 Oct 2009 20:18:56 +0000

While trying to migrate from Oracle to Migration and while Genreating target objects phase, I am gettign the following error

Create object online or write a SQL create script? (1.Online 2. Script 3. both 0.Abort) 1

Create database objects,
Initializing JDBC driver...
Driver class MySQL JDBC Driver 5.0
Opening connection ...
Connection jdbc:mysql://hostname:3306/?user=root&password=abcd &useServerPrepStmts=false&characterEncoding=UTF-8
The SQL create script cannot be executed
"The function TransformationMysql:splitSqlCommands() returned the following error:
You have to submit a SQL script as parameter."
:com.mysql.grt.Grt.callGrtFunction(Unknown Source)
com.mysql.grt.modules.TransformationMysqlJdbc.executeSqlStatements(Unknown Source)

Let me know how this can be resolved.
Thanks

organic search engine marketing (no replies)

gracy smith — Sun, 04 Oct 2009 04:12:20 +0000

Internet marketing's newest thing is organic search engine optimization. Almost 50% of consumers start the buying process with an online search using Google, Yahoo, or similar. Potential customers are letting the search engine fingers do all the walking these days.
Search engine marketing (SEM) and search engine optimization (SEO) are nothing new. Both of those concepts have been around for a few years. As these disciplines have developed and grown more nuanced it was necessary to distinguish them further and add the organic SEO designation.
Not everyone knows what "organic search" is.
Listings that result from a search that are not paid advertisements are considered to be organic. For instance, when a user types in a term or phrase in the empty Google box what results are some sponsored links and advertisements, usually on the right side of the browser or along the top of the page, and a list of links to various websites, usually on the left side of the browser window. These links could be to news reports, personal blogs, article directories, or many other types of websites. As opposed to being paid advertisements, links make this list due to their relevancy to the search topic or words.
Acording to Google, many more people click through these links than those of the "pay per click" links. Not surprising since most people would rather go to a site that was especially relevant to what they were looking for. Customers are understandably skeptical when it comes to advertising language. They do not want to be manipulated or feel like they are being lied to.
That doesn't mean that paid advertising links are dishonest. Just because a link makes the top of the page in a list of organic search results doesn't mean it can be trusted. In the past it was possible to inflate a page ranking simply by repeating the key words or phrases over and over. This is known as "key word stuffing." It is certainly not a good strategy. Besides, neither Google nor customers are stupid.
Searchers find it helpful to see a brief excerpt or description below the link. This allows for quick scanning of the list to see which link is best. Although all the links are relevant, some will be more relevant than others.
Organic search engine optimization attempts to make the most out of every web page. It is a completely legitimate method that seeks to maximize the impact of content that a business has made available to would-be customers. What good is it to have a great product if knows one knows about it? Good practitioners of this approach will stay on top of how search engines like Google determine where a page should rank in an organic search. It is an ever changing algorithm so just because something was effective last year doesn't mean it will work this year.
Shortcuts or tricks usually won't work for very long. Smart, honest organic search engine optimization will always be more effective.
For more information on Organic Search and Indianapolis SEO , contact the professionals at DeepRipples.com
rl=http://www.davepye.com]humor blog[/url]
organic search engine marketing

IT efficiency can be improved with automated cloning (no replies)

Anand Kumar — Fri, 02 Oct 2009 16:40:28 +0000

It is well known that creating clones manually is labor intensive process. But with the assistance of Solix's product we've been creating automated clones where clones are created and configured faster and with fewer errors. Solix also provides products and solutions for data archiving, etc. For more information visit solix.com

Delete all rows in table but keep the last recent 2 (4 replies)

yvonne kire — Tue, 29 Sep 2009 23:54:06 +0000

I want to keep the last two most recent records in a table and delete anything else - any ideas how this would be acomplished?

I have a ID column and CREATE_DATE column
I can either sort by CREATE_DATE - and keep the last two

All other data that needs to be purged out.
How can accomplish this?

max(ID) or max(CREATE_DATE) find the newest record
how do I find the following record?
select (max(ID) -1 ) doesnot work since the ID are not sequential

| 26821 |
| 27963 |
| 28007 |
| 28131 |
| 28197 |
+-------------------------+

IT efficiency can be improved with automated cloning (no replies)

Anand Kumar — Sat, 19 Sep 2009 07:43:39 +0000

UTL_FILE - anything equivalent in MYSQL? (1 reply)

steve emmett — Wed, 16 Sep 2009 20:51:21 +0000

Does MySql have any functionality like the UTL_FILE procedure from Oracle? I need to write files from within MySQL and don't see any way to accomplish this. Tee, notee do not provide the functionality required.

Thanks
Steve

Data Transfer From MySQL to ORACLE (1 reply)

Ansaar Ahmed Shaik — Fri, 30 Oct 2009 11:07:21 +0000

Hi,

We have an application which uses Oracle Database Server as primary database. Now, the client machine is running an application and in case of network breakdown the application should still work and online transactions should be processed.

So, what we are planning is to create a copy of database (MySQL as a copy) in client machine and perform the transaction. When the network is up the data has to be updated to the Oracle database.

Can any one suggest how to achieve this or is there any other better way?

Regards

Data Migration is an important aspect of organizing data (no replies)

Anand Kumar — Wed, 02 Sep 2009 10:22:18 +0000

Data migration is an integral part of organizing data and we are using Solix Application Sunsetting and Migration solution which allows companies to map metadata between applications and create conversion rules for faster and seamless data migration. For more information visit solix.com

MySQL equivalent Oracle Lag, Partition By - Query Help (1 reply)

Si Han Tang — Thu, 03 Sep 2009 19:59:59 +0000

I need to retrieve invoices from 3 tables.
Table Project_Detail has my project details
Table Project_Item storing items for my project. each project have 1 or more items.
Table Project_Item_Invoice will store invoices for each items in each project. 1 item may have more than 1 invoice also.

Query:
SELECT A.Project_No, B.Item_ID, B.Item_Name,
C.Invoice_ID, C.Date, C.Amount, C.Invoice_No
FROM Project_Detail A
LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No AND
B.Item_ID = C.Item_ID
WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed'
ORDER BY A.Project_No DESC, B.Item_ID, C.Invoice_ID

The selected data from database:
___________________________________________________________________________
|Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
---------------------------------------------------------------------------
|WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
|WPJ-0906-1693 | 1 | TNA0052941 | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
|WPJ-0906-1693 | 1 | TNA0052941 | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
|WPJ-0906-1693 | 2 | TNA0052942 | 1 | 2009-08-26 | 5678.90 | 0906-1150 |
|WPJ-0906-1693 | 2 | TNA0052942 | 2 | 2009-08-27 | 4356.20 | 0906-1158 |

The result I want is like this:
___________________________________________________________________________
|Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
---------------------------------------------------------------------------
|WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
|___(blank)____ | _ | __(blank)___ | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
|___(blank)____ | _ | __(blank)___ | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
|___(blank)____ | 2 | TNA0052942 | 1 | 2009-08-23 | 5678.90 | 0906-1150 |
|___(blank)____ | _ | __(blank)___ | 2 | 2009-08-25 | 4356.20 | 0906-1158 |

In Oracle, I can select the data with decode, lag, partition by:

select
decode (t.Project_No,t.Prev_Project_No,'') Project_No,
decode (t.Item_ID,t.Prev_Item_Id,'') Item_Id,
decode (t.Item_Name,t.Prev_Item_Name,'') Item_Name,
t.Invoice_ID, t.Date, t.Amount, t.Invoice_No
from
(
SELECT
A.Project_No, B.Item_ID, B.Item_Name,
C.Invoice_ID, C.Date, C.Amount, C.Invoice_No,
lag(A.Project_No,1,0)
over (partition by A.Project_No
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Project_No,
lag(B.Item_ID,1,0)
over (partition by B.Item_ID
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Id,
lag(B.Item_Name,1,0)
over (partition by C.Item_Name
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Name,
FROM Project_Detail A
LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No
AND B.Item_ID = C.Item_ID
WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed'
) t

How to do this in MySQL? I only know MySQL have 'if' (equal to 'decode' in Oracle) statement. How about lag and partition?

Tungsten = MySQL and Oracle Data Availability and Scalability (no replies)

Petri Virsunen — Fri, 28 Aug 2009 22:03:28 +0000

Live webcast: Thursday, September 17 '09, 1 pm ET/10 am PT
Register at: http://continuent.webex.com

Continuent Tungsten Enterprise offers simple, economical, and flexible data replication to all MySQL and Oracle users, regardless of the Oracle edition they are using. Continuent Tungsten works with Oracle Xpress Edition, Oracle Standard Edition One, Oracle Standard Edition, and Oracle Enterprise Edtion.

This enables solutions to a number of important business problems:

- Ensuring database high availability: Tungsten as an alternative for Oracle Streams
- Recovery from site failures: Tungsten as an alternative for Oracle Data Guard
- Replication to MySQL
- Replication to other JDBC databases
- Zero-downtime upgrades.

Continuent Tungsten Enterprise capabilities permit users to run 24x7 businesses and gain maximum benefits from data stored in Oracle.

Join us for a live webcast on September 17, 2009, to learn more!
Register at http://continuent.webex.com

RAISE_APPLICATION_ERROR (1 reply)

Andy Paton — Wed, 26 Aug 2009 14:43:42 +0000

Is there an equivilant of Oracles RASIE_APPLICATION_ERROR in MySQL?

I am converting some PL/SQL procedures over to MySQL, and this would be a handy addition.

If not what are the alternatives?

Regards,

Andy

Help Order by (1 reply)

Fabio Castillo — Wed, 26 Aug 2009 07:43:34 +0000

hello all,

I have a problem and not how he would deal. Make a query to a table as follows

select id from menu order by id asc

+----------+
| id |
+----------+
| 1,2 |
| 1,2,1 |<-----------------
| 1,2,1,1 |
| 1,2,1,2 |
| 1,2,10 |
| 1,2,14 |
| 1,2,2 |<-----------------
| 1,2,2,1 |
| 1,2,2,2 |
| 1,2,3 |<-----------------
| 1,2,5,1 |<-----------------
| 1,2,5,10 |
| 1,2,5,19 |
| 1,2,5,2 |<-----------------
| 1,2,5,20 |
| 1,2,90 |
+----------+

The field is text and not organized well.

How do I get my order but the list for any level?

+-------------------------------------------------+
| id |
+-------------------------------------------------+
+-------------------------------------------------+
level1, level2, level3, Level4 ,... ..., nivel n |
+-------------------------------------------------+

Thanks

Migration tool Oracle stored procedures to MySQL (3 replies)

Reynald Poidevin — Mon, 12 Oct 2009 10:22:15 +0000

Hello,

Do you know if migration tools exist in order to automatically convert PL/SQL to MySQL ?

Thanks

Convert Oracle View to MySql (1 reply)

pravin vaidya — Sun, 23 Aug 2009 23:28:59 +0000

Dear Firends...
I m new to MySql pls help to convert this view to MySql
The oracle View as below..

CREATE OR REPLACE VIEW VW_BASE_STATION_V21
(R_RADIO, BASE)
AS
SELECT r_radio, 'Y' base
FROM rec_now
WHERE headding = 'Y'
AND TRUNC (r_time) = TRUNC (SYSDATE)
AND r_radio NOT IN (
SELECT r_radio
FROM (SELECT COUNT (1) c, group_main, GROUP_ID, r_radio
FROM (SELECT o.group_main, o.GROUP_ID, r.r_radio,
e.r_station
FROM m_group o,
m_vehicle v,
r_datatemp e,
rec_now r
WHERE v.radio_id = r.r_radio
AND r.r_radio = e.r_radio(+)
AND o.GROUP_ID = v.GROUP_ID
AND v.under_repair = 'N'
AND v.rec_stat = 'Y'
AND r.headding = 'Y'
AND TRUNC (r.r_time) = TRUNC (SYSDATE)
GROUP BY o.group_main,
o.GROUP_ID,
r.r_radio,
e.r_station)
GROUP BY group_main, GROUP_ID, r_radio)
WHERE c = 1)
/

Import Data from MySql to Oracle (3 replies)

Shahab Ahmed Khan — Fri, 28 Aug 2009 22:11:09 +0000

Dear community;

I am new in MySql Server I have no any knowledge about it but I have a problem that; How I can import MySql's Data in Oracle 10g.

Can anyone help me?

Thanks in advance.

Shahab Ahmed Khan

Funcion INSTR (4 replies)

Fabio Castillo — Tue, 18 Aug 2009 22:32:12 +0000

Hola a todos soy nuevo en Mysql como obtengo este resultado pero en MYSQL

Esto es Oracle

i ejecutamos:

SELECT INSTR('1,2,3',',',1,2) FROM CONTENIDO;

Devuelve
encontrò 2 comas por este registro
2

Ahora hay que tener en cuenta que yo tengo una tabla y la idea seria asì

SELECT INSTR(ID,',',1,2) FROM CONTENIDO;

Me retorne en nuemro de comas por cada registro

Como se hace pero con MySQL?

Gracias

MySQL Installation stopped Oracle (1 reply)

Rafael de Faria — Sun, 23 Aug 2009 23:44:01 +0000

Hi, i have a server running Windows Server 2003 with Oracle, MS SQL Server 2005 and MySQL 5.0 running and i had to install MySQL 5.1 to this server to use Drupal CMS. In my local machine i already had the Drupal setup running fine, so i tried to move the database from my local machine to the server. Problem is that i couldn't import my 5.1 database in the 5.0 database that was in the server. So i tried to uninstall the MySQL 5.0 and install the MySQL 5.1 in the server, i made the changes and restarted the server. After that, the Oracle database stopped to work, the Oracle server is running but i cannot access the data in the database, i only can access the database backups. I want to know if there is some know issue with MySQL and Oracle ( i really don't have MySQL experience ), and what's the best way to trace problems like this one.

Thanks.

Transfer Data from Mysql To Oracle by code or scrpit (no replies)

nani k — Wed, 12 Aug 2009 10:02:59 +0000

Hi
i am new to mysql caon any one let me knnow how to transfer the data from mysql to oracle by code(or)script and is there any changes that to be made in the listener files of oracle .

Thanks
Sqluser

Transfer Data from Mysql To Oracle (no replies)

nani k — Wed, 12 Aug 2009 10:01:18 +0000

Oracle to Mysql (1 reply)

dhruv verma — Fri, 28 Aug 2009 22:09:23 +0000

How data from Oracle can be transfered to MySql by writing some sripts in oracle or Mysql
----------
Tummy Tuck
health juice

Oracle to MySQL migration - Upgrade and Optimized Execution Plans (no replies)

petr kovar — Wed, 05 Aug 2009 11:40:09 +0000

Hi,
I am wondering if it is possible to replace/update stored procedure in MySQL Cluster during Upgrade?
The second question is: Oracle has some optimization algorithms (called ”optimized execution plans”), that adapts transaction processing according to the transaction traffic passed so far. The analysis of the past traffic and adaptation must be executed explicitly. Does MySQL have something similar?

Thanks Petr

importing using LOAD DATA INFILE (1 reply)

Sridhar Ramalingam — Sat, 19 Sep 2009 14:50:40 +0000

Hello

I am in middle of transferring data from Oracle tables to MySQL (5.1.37). I have a flat file with 3 columns in it...

"XXXX",3/18/1998,3/3/2004 3:28:59 PM
"YYYY",3/23/1998,3/3/2004 3:29:01 PM
"ZZZZ",5/18/1998,3/3/2004 3:30:45 PM
"AAAA",5/19/1998,3/3/2004 3:30:47 PM

The destination table in MySQL looks like this...

+---------------+-------------+
| Field | Type |
+---------------+-------------+
| SOMEXX_ID | varchar(20) |
| SUBMIT_DATE | date |
| CREATION_DATE | timestamp |
+---------------+-------------+

And, here's the load command i used...

LOAD DATA INFILE '/data-dump/TEST.txt'
INTO TABLE DATE_TEST
FIELDS TERMINATED BY ',' optionally enclosed by '"' lines terminated by '\n'
(SOMEXX_ID, @var1, @var2)
SET SUBMIT_DATE = date_format(str_to_date(@var1, '%m/%d/%Y'), '%Y-%m-%d'),
CREATION_DATE = date_format(str_to_date(@var2, '%m/%d/%Y %l:%i:%s %p'), '%Y-%m-%d %l:%i:%s %p');

All the rows were imported successfully into MySQL table, the data checkout ok, but it had a bunch of warning....

+---------+------+-----------------------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'CREATION_DATE' at row 1 |
| Warning | 1265 | Data truncated for column 'CREATION_DATE' at row 2 |
| Warning | 1265 | Data truncated for column 'CREATION_DATE' at row 3 |
.....
.....
.....

I am not able to figure out why there are warnings even though the data was imported successfully and the data looked accurate.

Can someone please advise?

Thanks
sid

Data Security for Oracle E-Business Suite (no replies)

Anand Kumar — Tue, 04 Aug 2009 05:55:51 +0000

Recently we had a bad experience of losing live data. So with the help of Solix we started creating non-production clones of Oracle E-Business Suite for testing, development, and even training. It really helped us a lot in E-Business Suite Archiving. or more information visit http://www.solix.com/

Oracle to MySql DB link (1 reply)

Praveen Soni — Sat, 01 Aug 2009 20:47:36 +0000

Hello,

We are currently migrating from Oracle to MySql.
I know that a DB LINK between Oracle to MySql is possible. Have anyone used it, please share, because I get the below error from Oracle when do a describe:

SQL> desc test.employee@mysql;
ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQL

Although the tnsping is working fine.

Thanks
Nitin

Include a script from another script (1 reply)

Thomas Manson — Sat, 25 Jul 2009 21:08:36 +0000

Hi,

In my oracle script I can include some other scripts with the @ symbol :

example :

@..\..\package\PACKAGE_ARIANE.pks

How can I do this with MySQL ? I've googled this subject, find a reference to a "source" commande that doesn't work...

I need this function because I need to execute bunch of sql script located at different places on linux and windows. (Ideally from the MySQL Query browser).

Regards,
Thomas.