Showing posts with label written. Show all posts
Showing posts with label written. Show all posts

Monday, March 12, 2012

Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) =
upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) = upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Friday, March 9, 2012

oracle procedure help

i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.Originally posted by hnvhelp
i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt|||Originally posted by andrewst
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt

Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.|||Originally posted by hnvhelp
Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.
This could be either a string to number conversion error, or an overflow.
I suspect it is one of the variables declared as CHAR, since the length is not specified and defaults to 1.

The best way to avoid such errors is to "anchor" variables to the cursor or table being processed using %TYPE or (better) %ROWTYPE. I would do this:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
daf_rec disc_amt_fix%ROWTYPE;
BEGIN
OPEN disc_amt_fix;
FETCH disc_amt_fix
INTO daf_rec;
CLOSE disc_amt_fix;

UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END;
/

Actually, this is a strange piece of code, because it only fetches 1 row from the cursor - what if there is more than one? If you meant to process all rows it is better written using a FOR loop:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
FOR daf_rec IN disc_amt_fix LOOP
UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END LOOP;
END;
/|||SQL>exec fix_disc_amt;
or
SQL>execute fix_disc_amt;

semicolon is optional|||hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;|||Originally posted by prashantbist
hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;

In SQL Plus, do this:

SQL> variable c refcursor
SQL> exec :c := test50pack.test50_select(10)
SQL> print c|||Hi ..
i am trying to call a stored function in a package through jdbc.. but i am
getting this exception..

error code ORA-17059
java.sql.SQLException: Fail to convert to internal representation
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:307
)
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:94)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 465)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 593)
at DB2.PerformanceTest50.Oper_PTest50(PerformanceTest 50.java:5064)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.xlmain( PerformanceTest50_Oper_PTe
st50_305_drv.java:58)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.main(Pe rformanceTest50_Oper_PTest
50_305_drv.java:119)

The package and function are as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid
NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
end;
end test50pack;

and my calling code is

cst = con.prepareCall ("{ ? = call test50pack.test50_select (?) }");
cst.registerOutParameter (1, OracleTypes.CURSOR);
cst.setInt (2, h_col1);
cst.execute ();
rs=(ResultSet)cst.getObject(1);|||Hi,

I am just begin to using oracle, now i am using oracle 9.2. Before oracle most of the time i only use MS-Sql Server 2000. I have one problem in here that i want to make some simple stored procedure.Say i just want to select sysdate from dual ,i want to put it into stored proc but when i try :
create or replace Test ()
as
begin
select sysdate from dual
end

that i believe it will work in sqlserver(beside the sysdate function and dual which sqlserver dont have),it not worked in oracle, i using TOAD as 3rd party application to access my oracle. So i hope that u can help me what the different in using stored proc in oracle and sqlserver , for example if i want to have the above querry to run in oracle

thanx|||Berwin,

in Oracle, syntax for your procedure would be something like this:

create or replace procedure test
as
dummy date;
begin
select sysdate into dummy from dual;
end;

You explicitly have to say it is a procedure; if it has no parameters you don't need brackets; you have to have INTO in such an example.