MySQL Forums
Forum List  »  Oracle

Re: How to convert the sql statement from Oracle to MySQL
Posted by: Josh Chamas
Date: November 17, 2004 11:54AM

jenny chen wrote:
> Hi,
>
> I'm running some sql statements to create table in
> the database. The statements are working fine in
> Oracle DB server. However, if I run those sql
> statement in MySQL DB server, it just simply throw
> the syntax error. Could you please let me know why
> these sql statement cannot work on MySQL and how
> to migrate them from Oracle to MySQL?
>
> Thanks in advance for the reply!
>

There are somethings to change here, but your script was close already... NUMBER -> NUMERIC, RAW -> BINARY, and some misc tweaks on the stored procedure. See below for more...

> Here are the statements:
>
> create table Bigint_Tab (MAX_VAL NUMBER(19,0),
> MIN_VAL NUMBER(19,0), NULL_VAL NUMBER(19,0) NULL)
>
> create table Binary_Tab (BINARY_VAL RAW(24) NULL)
>
> create table Varbinary_Tab (VARBINARY_VAL RAW(48)
> NULL)
>
> create procedure Numeric_Proc (MAX_PARAM out
> NUMBER, MIN_PARAM out NUMBER, NULL_PARAM out
> NUMBER) as begin select MAX_VAL, MIN_VAL, NULL_VAL
> into MAX_PARAM, MIN_PARAM, NULL_PARAM from
> Numeric_Tab; end;

I converted your sample script to do mostly the same thing. When I run my version, I get this output:

$ ./mysql test < test.sql
MAX_PARAM MIN_PARAM NULL_PARAM
5 3 NULL

and test.sql is:


delimiter /

drop table if exists Bigint_Tab /
create table Bigint_Tab (MAX_VAL NUMERIC(19,0), MIN_VAL NUMERIC(19,0), NULL_VAL NUMERIC(19,0) NULL)
/
insert into Bigint_Tab values(3,3,3),(4,4,4),(5,5,NULL)
/

drop table if exists Binary_Tab /
create table Binary_Tab (BINARY_VAL BINARY(24) NULL)
/

drop table if exists Varbinary_Tab /
create table Varbinary_Tab (VARBINARY_VAL BINARY(48) NULL)
/

drop procedure if exists Numeric_Proc /
create procedure Numeric_Proc (MAX_PARAM NUMERIC, MIN_PARAM NUMERIC, NULL_PARAM NUMERIC)
begin
select max(MAX_VAL), min(MIN_VAL), NULL into MAX_PARAM, MIN_PARAM, NULL_PARAM from Bigint_Tab;
select MAX_PARAM, MIN_PARAM, NULL_PARAM;
end;
/

call Numeric_Proc(3,3,3)
/

delimiter ;

============

Regards,

Josh

Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL! http://www.mysql.com/consulting

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to convert the sql statement from Oracle to MySQL
3905
November 17, 2004 11:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.