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