I am migrating MySQL query's to oracle (sqlplus). Tell me what is the below code doing and equivalent for this code in oracle.
declare @start_date datetime
select @start_date='$first_date'
declare @end_date datetime
select @end_date='$end_date'
This is followed by
select distinct ' ', column from my_table;
I tried a lot of ways (set @start_date etc)but nothing really works.
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
I`m migrating a mysql 5.0 database to Oracle 11g. I used Oracle's Sql developer and with a workaround i could convert tables, constraints, indexes, some triggers and created sequences.
My question is, what happen with stored procedures and functions, they didn't pass. I've been searching and i found here hey have to be rewritten. Is there any way to convert those objects ? I can't afford to do it manually because the mysql database has almost 400 of them and they aren't small.
Is it possible to convert oracle data base to MySql?
View 1 Replies View RelatedI need to migrate MySQL and Access databases over to Oracle 9i. Is there a tool that will do this and also migrate sql scripts?
I heard of Oracle Workbench as one tool I could use?
I need to migrate data from oracle server to MySql.
View 14 Replies View Relatedtell me what software is the best to backup Oracle, MySQL and MSSQL?
View 2 Replies View RelatedI need to created DB link between oracle (10g on linux) and mysql 5.0 to configure a job to fetch data after every hour. I need solution that does not involve:
ODBC Connection
Oracle GateWay
Golden Gate
Oracle 11gR2 on Redhat5
I need to export a table from ORACLE to MySQL. My table have more than 1billion rows and more than 140G.
i tried for 3 days to setup a connection between my oracle database 11.2 (resides on 2003,64 bit Microsoft windows) and a mysql database (resides on 2003,32 bit Microsoft widows).
i used dg4odbc 64 bit which was installed on oracle server, my steps was as follows :
1- configure ODBC driver (MYSQL 3.51 odbc) on oracle server that connect to mysql database and the connection was tested successfully (the name of odbc dsn is DG4ODBC).
2- configure the listener.ora file (which resides on dg4odbc installation path) that contains the following :
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxx)(PORT = 1511))
[code]....
3- configure the tnsnames.ora file (which resides on oracle database path) that contains the follwoing :
DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxxxx)(PORT=1521))
(CONNECT_DATA = ( SERVICE_NAME = DG4ODBC ))
(HS=OK)
)
4- configure that initdg4odbc.ora file that resides on the dg4odbc installation.
HS_FDS_CONNECT_INFO = DG4ODBC
HS_FDS_TRACE_LEVEL = off
5- restart the gateway listener.
6- test DG4ODBC service by tnsping which was successful .when i try to get some data from mysql database by SELECT * FROM MDL_COURSE@DG4ODBC query,the query take some time (15 second !!) and return
ORA-12154: TNS:could not resolve the connect identifier specified
It is possible to migrating oracle(Forms& Reports) 6i into Oracle 11g?
View 2 Replies View RelatedI'm trying to migrate an Oracle Enterprise Server database v9.2.0.1.0 deployed on Windows 2000 Server 32-bit to v10.2.0.5.0 on Windows 2008 R2 64-bit.Basically, I'm following instructions provided by this document:[URL],,,
1) Updated v9.2.0.1.0 to v9.2.0.6.0 on the old server
2) Backed up the database as follow:
SQLPLUS /NOLOG
CONNECT / AS SYSDBA
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SHUTDOWN IMMEDIATE;
3) Installed Oracle Enterprise Server 10.2.0.4 on the new server and updated to v10.2.0.5
4) Copied trace files, data files, control files, archive logs and init.ora to the new server. Redo logs have been not copied since the ARCHIVELOGMODE is enabled on the old server.
5) Created an Oracle service on the new server as follow:
[code]....
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
[code]..\
I am using oracle 8i (8.1.7) with forms 5 and reports 3 on a windows 2000 platform. Now I want to migrate from 8i to 10 g. Will the applications in form 5 and reports 3 work or should I have to upgrade the forms and reports as well.
View 4 Replies View RelatedWhat are reported issues when migratin Oracle 11g from Solairs to Linux?
View 1 Replies View RelatedI have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.
I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.
We are planning to migrate the forms application from windows to linux.
View 1 Replies View RelatedWe are in process of Migrating our database from Oracle 9i to Oracle 10g.
I am getting below error while parsing XML in 10g.
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//soap:Envelope/soap:Header/coHeader/company/text()'
Same code works fine in Oracle 9i database with same XML. Is there any difference in XML TYPE functionality in Oracle 9i and 10g?
I am migrating data from a Solid Database to Oracle, I am using Flat Files to do that.
1.- I download the data to flat files from Solid
2.- I move the files to Oracle server
3.- I upload the data to Oracle
Now, I have done the 90% of the data base, but I have found some tables that has description columns and in this description the users writes enters, so when I try to upload the data to Oracle SQL loader cannot recognize this characters.
Example:
'25','0.','5.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'26','0.','2.','0.','0.','0.','0.','3.','0.','0.','0.','0.','0.','',''
'27','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'28','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'29','0.','38.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'30','0.','13.','0.','0.','0.','0.','0.','6.','0.','6.','0.','0.','|SE RECHAZA B20CS50SNW ^M
^M
SE RECHAZAN CINCO PZAS ^M
DOS MOD. HSC15I41EH,DOS MOD. HSK15I41EH |Agregó: 06/06/2009 12:22:50
|','DEV. A PROV.'
'31','0.','50.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'32','0.','9.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'33','0.','2.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
How can I solve this ?
I'm trying to migrate a mySQL database into an Oracle database.
View 7 Replies View RelatedI have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries
1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name
2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')
3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)
I have installed oracle 10g on opensuse. After i logon as normal user and type sqlplus, it was display an error message as below:-
login: nv2am
Password for nv2am:
login: Configuration file does not specify default realm when parsing name nv2am
Last login: Tue Aug 3 16:33:19 from 10.69.20.25
Have a lot of fun...
Directory: /home/nv2am
Tue Aug 3 17:36:23 MYT 2010
nv2am@ARMSDEV:/home/nv2am> sqlplus
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> sqlplus "/ as sysdba"
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> set ORACLE_SID="ARMSDEV"
nv2am@ARMSDEV:/home/nv2am> svrmgrl
-ksh: svrmgrl: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am>
I have oracle db server 9.2.0.2 , I have a client connected with this server, the client has sqlplus 8.0.6.0
I adjust the tnsnames in the client with the following parameters :
myconn =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = my_sid)
)
)
this connection was successful
when I tried the same connection with oracle db server 11g (11.2.0.1)
this connection failed
how can i connect wih the new server 11g (11.2.0.1)
I'm not sure is parallel query supported in xe ?
View 2 Replies View RelatedI each user in my table to have a unique username and user_id. So the table cant contain 2 users with the same user id and it cant contain 2 users with the same username.
oracle doesn't support more than one primary key in a table
so what could I use a secondary key?creating alternate keys in sql plus?
the reason for not availability of ISQLPLUS & SQLPLUSW window from oracle 11g.
View 3 Replies View RelatedI need to login remote DB writing a shell script where our DB is on a separate node, i have to test the script only with
SCHEMA USERNAME
PASSWORD
HOSTNAME
PORT NO
SID/SERVICE NAME
these details. May be am wrong to ask this question but i need to login and check the requirement. Is there any other anyway to login without sqlplus utility?
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies View RelatedI am using Windows 7 and oracle 10g; From sqlplus* I can not use editor (using ed). but when I see the variable from DEFINE.. It shows that default editor is notepad..
I can use editor in winxp.How to get (use) editor ?
I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.
My sql file has 3 lines 3 are correct and 1 is incorrect. For example:
INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);
Lets say file is called 1.sql
My shell script invokes this SQL as follows: (Where $File1 = 1.sql)
$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
connect $DB_USER/$Password1@$Database1
SET AUTOCOMMIT OFF
@$File1
WHENEVER SQLERROR EXIT ROLLBACK;
EOF
[code]......
So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.
I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.
Is there any method to convert following output in to ASCII format ??
select ename from scott.emp;
ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS