ORDER BY, only orders by the first character?

Hi,

I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:

1

100

20

200

clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?

Thank you,

Shep

ORDER BY a numeric data type as opposed to ORDER BY a character data type.

Run the following code in a test database. Note the absence of indexes.

CREATE TABLE dbo.OrderByInt(number int)

GO

INSERT INTO dbo.OrderByInt(number) VALUES(1)

INSERT INTO dbo.OrderByInt(number) VALUES(10)

INSERT INTO dbo.OrderByInt(number) VALUES(100)

INSERT INTO dbo.OrderByInt(number) VALUES(2)

INSERT INTO dbo.OrderByInt(number) VALUES(20)

INSERT INTO dbo.OrderByInt(number) VALUES(200)

SELECT number FROM dbo.OrderByInt

SELECT number FROM dbo.OrderByInt ORDER BY number

CREATE TABLE dbo.OrderByVarchar(number varchar(3))

GO

INSERT INTO dbo.OrderByVarchar(number) VALUES('1')

INSERT INTO dbo.OrderByVarchar(number) VALUES('10')

INSERT INTO dbo.OrderByVarchar(number) VALUES('100')

INSERT INTO dbo.OrderByVarchar(number) VALUES('2')

INSERT INTO dbo.OrderByVarchar(number) VALUES('20')

INSERT INTO dbo.OrderByVarchar(number) VALUES('200')

SELECT number FROM dbo.OrderByVarchar

SELECT number FROM dbo.OrderByVarchar ORDER BY number

SELECT number FROM dbo.OrderByVarchar ORDER BY CAST(number AS int)

|||

It sounds like you're storing numbers as text (in other words, storing "int" values in a "varchar" column). So, SQL Server is doing a text sort, which does indeed go by the first character.

The best solution is to use a data type that matches the data (use "int" for integer numbers, and nvarchar for text).

An alternative solution is to use a CAST or CONVERT in your order by to convert it to "int" before sorting -- ORDER BY CAST(myColumn AS int) -- but any sort of bulk operation on an expression like that cripples SQL Server's query optimizer and will hurt performance on large tables.

-Ryan / Kardax

Edit: "lkh" beat me by seconds, and provides a nice illustration of the problem and solution.

|||

Thank you for your reply lkd, however i am still having trouble:

When I try the above code i get the error message 'TABLE NOT SUPPORTED BY THIS EDITOR'

I am using Visual Web Developer 2005 Express Edition, could this be the problem?

Shep

|||

Sorry I haven't fully explained myself (i missed the most important part, my apologise!)

i am working in units of voltage & power, so there for the listing would be as follows:

1kW

10W

100W

20W

200W

so i dont think its an option to convert the values into integers, given that I have other non-numeric characters in there! Is it a lost cause?

Shep

|||I have never used Visual Web Developer but in a quick tour of the web site I don't see anything about creating objects in a database. You may have to use an editor in your database.|||

My honest though probably not helpful thought is that this is a database design problem. Can you redesign the table to have a column called Watt of int datatype?

Otherwise you could create a complicated CASE statement and sort on that column but that is not ideal.

|||Well the table is supplying the items for the dropdownlist, based on a certain product type. So just using numbers without the corresponding measurements would not look right in the dropdownlist.|||Can you add an int column that wouldn't appear in the UI to the table that would be used in the SELECT statement that populates the dropdown list?|||

If your are only using the single rightmost character ( as in your examples) as alpha, then the following may work for you.

BOTH strip off the last character, AND convert to an Integer ONLY in the ORDER BY clause.

DECLARE @.MyTestTable table
( MyColumn varchar(5) )

INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '2A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '10W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '1A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '100W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '200W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '20W' )

SELECT MyColumn
FROM @.MyTestTable
ORDER BY cast( left( MyColumn, ( len( MyColumn) - 1 )) AS int )

|||

Hi,

thanks to everyone for their help, in the end i have resorted to simply using VB to code in the items depending on the category: e.g.

If Value = 2 Then

ddlPara1.Items.Add(New ListItem("50V", "50V"))

ddlPara1.Items.Add(New ListItem("120V", "120V"))

ddlPara1.Items.Add(New ListItem("150V", "150V"))

ddlPara1.Items.Add(New ListItem("300V", "300V"))

ddlPara1.Items.Add(New ListItem("400V", "400V"))

If Value = 3 Then........

This shouldnt affect the performance of my application should it?

Thanks again,

Shep

Order By Parameter

Is it possible to use a variable like @.SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @.SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

ThanksYou'd either have seperate stored procedures (or a conditional within one stored procedure), or to implement it like your example above you would have to use dynamic SQL (look in Books Online for proper syntax, mine is iffy):

Declare @.strSQL VARCHAR(8000)
SELECT @.strSQL = "SELECT * FROM table ORDER BY " + @.SelectOrderBy
EXEC @.strSQL

It's not advisable to do it this way however, due to security and efficiencty issues. Stick with the conditional within one proc if it's not too complex!|||the other option is to pull the data back into a dataset and then use a dataview with a sort, passing in the column they selected.

cs

ORDER BY error

Hello,

in a complex stored procedure, i get an error when i want to apply an ORDER BY statement :

Here is a part of this Stored Procedure :

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

If I want to apply an ORDER BY statement, i have

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut

I get this error :

Msg 156, Level 15, State 1, Procedure SIE_ListeOperationsFacturablesParClient, Line 46

Incorrect syntax near the keyword 'ORDER'.

Does anyone cab help me ?

Is there anything else after the SELECT statement?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here is the whole code :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SIE_ListeOperationsFacturablesParClient]

@.Id_Client char(5),

@.Cas int

AS

BEGIN

SET NOCOUNT ON;

If @.cas = 0 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 1) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 1 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 0) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 2 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut DESC

)

END

|||T-SQL normally uses the following syntax but you can leave out the BEGIN and END if you are only executing one query (or a single line of code).

IF @.var = x
BEGIN
YOUR CODE
END

Could you try leaving out the ( and ) for the if clauses?

If @.cas = 2

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable,CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+' - '+ TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNERJOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE(TaOperations.Id_Client =CONVERT(int, @.Id_Client))AND(TaTypeOperation.Facturable = 1)AND(TaOperations.IdUserVerrou ISNULL)

ORDERBY TaOperations.HeureDebut DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wonderful !!! it works thanks a lotsql

ORDER BY Command in SQL Stored Procedure

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@.Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode

CASE @.orderBy = 'column1'
ORDER BY column1

that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE @.OrderBy
 WHEN 'CompanyName' THEN CompanyName
 WHEN 'ContactName' THEN ContactName
 END
 ASC

I suggest this method:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
 CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC

The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.

Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.

Order By Case Cast Convert Error

I have created a SQL Stored Procedure that uses a Case statement to
determine the Order By. For one of the Case statements I am trying to
turn a Char field into Datetime in for the Order By, however I can not
get it to work. Can someone please take a look and my code below and
tell me what I am doing wrong. Thank you.

ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End<jguilford@.cybergroup.com> wrote in message
news:1105983724.986065.70140@.c13g2000cwb.googlegro ups.com...
>I have created a SQL Stored Procedure that uses a Case statement to
> determine the Order By. For one of the Case statements I am trying to
> turn a Char field into Datetime in for the Order By, however I can not
> get it to work. Can someone please take a look and my code below and
> tell me what I am doing wrong. Thank you.
> ORDER BY
> CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
> Event_Date1,101) as datetime) End,
> CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
> Emp_firstname End,
> CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
> Emp_firstname End DESC,
> CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End

What does "can not get it to work" mean? Do you get errors, or do you get
unexpected results? The best idea would probably be to post CREATE TABLE and
INSERT statements to set up a test case which illustrates your problem.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||On 17 Jan 2005 09:42:05 -0800, jguilford@.cybergroup.com wrote:

>I have created a SQL Stored Procedure that uses a Case statement to
>determine the Order By. For one of the Case statements I am trying to
>turn a Char field into Datetime in for the Order By, however I can not
>get it to work. Can someone please take a look and my code below and
>tell me what I am doing wrong. Thank you.

Hi jquilford,

I guess that this is the line that's giving you trouble:

>CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
>Event_Date1,101) as datetime) End,

You are converting the datetime variable to the american mm/dd/yyyy
format, which is not very well suited for sorting. Then you are converting
it back to datetime, running alll kinds of risks because this format is
ambiguous - it's easily misinterpreted as dd/mm/yyyy, causing either wrong
sorting or conversion errors.

If your intention is to strip the time part from the datetime column, so
you can order by the date part only, use

CASE WHEN @.SortBy = 'Event_Date1' THEN DATEADD(day, DATEDIFF(day,
'20000101', Event_Date1), '20000101')

Or, if you really want to do it by conversion to string and back, use the
safe yyyymmdd format:

CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
Event_Date1,112) as datetime) End,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Or, if you really want to do it by conversion to string and back, use the
> safe yyyymmdd format:
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
> Event_Date1,112) as datetime) End,

Or simply say:

CASE WHEN @.SortBy = 'Event_Date1'
THEN CONVERT(char(8), Event_Date1,112)
End,

Then again, we have no idea jguildford mean "I can not get it to work".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 17 Jan 2005 22:40:11 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> Or, if you really want to do it by conversion to string and back, use the
>> safe yyyymmdd format:
>>
>> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
>> Event_Date1,112) as datetime) End,
>Or simply say:
> CASE WHEN @.SortBy = 'Event_Date1'
> THEN CONVERT(char(8), Event_Date1,112)
> End,

Hi Erland,

Of course - no need to change it back to datetime in this case. Thanks!

>Then again, we have no idea jguildford mean "I can not get it to work".

Maybe he (she?) will post with more details?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) why are you using a CASE expression in an ORDER BY? Is the
destruction of portable code one of your design goals?

2) The column used for a sort should appear in the output so a human
being can use it to search the list. Basic human factors, etc. Put it
in the SELECT list.

3) Why are you making a date into a string in the first place? You can
sort of temporal datatypes too. And why are you converting it to a
string that is not in temporal order?|||Let me try to better explain my problem. I have created a page in
asp.net that has a datagrid that pulls a few fields form a SQL Server
table. Also on this page there is a dropdown box that allows you to
pick the way the data is sorted. You can pick to sort it by the data
the record was created, name (both ascending and descending), social
security number and by the date of the event. Now here is my problem,
the Event_Date1 field is a 10 character field (00/00/0000) instead of a
datetime field, it was set up like this a long time ago and can not be
changed. Because it is a character field when you use it to sort the
data it is sorted by month then day then year instead of year then
month then day. I tried to use a Cast and Convert statement to change
it to a datetime so it would sort correctly but now when I try to sort
by Event_Date1 I receive this error: "The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime
value." I have included all of the code in my stored procedure below.
I hop this clears up some of the confusion. Let me know if there is
anything else I did not clear up. Thank you.

CREATE PROCEDURE spShowArchives
@.SecurityID int,
@.SortBy varchar(50)
AS
SELECT ID, Emp_lastname + ', ' + Emp_firstname as FullName, Emp_SSN,
Event_Date1, Injury_Illness_Type, Jurisdiction, Injury_Cause_Desc,
SISCO_claim, dttm_stamp FROM omni_table
WHERE security_id = @.SecurityID
AND omni_table.deleted_flag = 0
AND (SISCO_claim <> '' or SISCO_claim <> null or SISCO_claim <>
'Submit')
ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End
GO|||On 18 Jan 2005 11:53:03 -0800, jagguil4d wrote:

>Let me try to better explain my problem.
(snip)
> Now here is my problem,
>the Event_Date1 field is a 10 character field (00/00/0000) instead of a
>datetime field, it was set up like this a long time ago and can not be
>changed.

Hi jagguil4d / jguilford,

This is not correct. Of course it *can* be changed. You probably meant to
write that someone in your organization doesn't *want* it to be changed.
And that someone is most likely the person who's budget is impacted by the
cost of actually imprivong your system, but doesn't suffer from the
(eventually) much higher costs of numerous workarounds, bugfixes and error
recovery.

> Because it is a character field when you use it to sort the
>data it is sorted by month then day then year instead of year then
>month then day. I tried to use a Cast and Convert statement to change
>it to a datetime so it would sort correctly but now when I try to sort
>by Event_Date1 I receive this error: "The conversion of a char data
>type to a datetime data type resulted in an out-of-range datetime
>value."

You might try if you have more luck with
CASE WHEN @.SortBy = 'Event_Date1' THEN CONVERT(datetime, Event_Date1,
101) End,

If that fails as well, you have at least one row in your table with an
invalid date (and boy, are you lucky if it is indeed only one <g>). These
can be hard to find. A good starting point would be
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE ISDATE(Event_Date1) = 0

If the above returns no rows, but you get errors converting Event_Date1 to
datetime, then you probably have rows with a date in DD/MM/YYYY format, or
some other date format. The following will hopefully catch most of these
buggers:
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE Event_Date1 >= '13'
OR Event_Date1 NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

Good luck! (You'll need it...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jagguil4d (jguilford@.cybergroup.com) writes:
> Now here is my problem, the Event_Date1 field is a 10 character field
> (00/00/0000) instead of a datetime field, it was set up like this a long
> time ago and can not be changed.

Of course it can! I hear this lame excuse every time, but seriously,
yes it can be changed. Just why would it be left unchanged?

Anyway, as Hugo points out you have garabge in this column, so if
you insist on that you don't want to change it, do this:

CASE WHEN @.SortBy = 'Event_Date1' THEN
substring(Event_Date1, 7, 4) + substring(Event_Date1, 1, 2) +
substring(Event_Date1, 4, 2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) why are you using a CASE expression in an ORDER BY? Is the
> destruction of portable code one of your design goals?

Because he more cares about serving his users than paying sacrifice
to the Holy Church of Portability.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> he more cares about serving his users than paying sacrifice to the
Holy Church of Portability. <<

When you can get portability for no extra cost, there is no sacrifice
and considerable gains in maintaining the code over the life of the
system. Would really use getdate() instead of CURRENT_TIMESTAMP to
save a few keystrokes? Or ISNULL() instead of COALESCE()?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> When you can get portability for no extra cost, there is no sacrifice
> and considerable gains in maintaining the code over the life of the
> system. Would really use getdate() instead of CURRENT_TIMESTAMP to
> save a few keystrokes? Or ISNULL() instead of COALESCE()?

In this case you questioned the use of CASE in ORDER BY, which
jguilford had added to offer desired functionailty to his application.
So there is a cost to be portable here.

By the way, there is a situations where isnull() works, but not
coalesce().

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

Oracle To SQL

I have an oracle procedure that needs to be converted to ms-sql.

lXMLContext := DBMS_XMLQuery.newContext("select * from table");
-- Setup parameters on how XML is to be constructed.
DBMS_XMLQuery.useNullAttributeIndicator(lXMLContex t,FALSE);
DBMS_XMLQuery.setRaiseNoRowsException(lXMLContext, FALSE);
DBMS_XMLQuery.setRaiseException(lXMLContext,TRUE);
DBMS_XMLQuery.propagateOriginalException(lXMLConte xt,TRUE);
DBMS_XMLQuery.setRowTag(lXMLContext,lWS.wsXMLRecor d.wsViewName);
DBMS_XMLQuery.setDateFormat(lXMLContext,lDateMask) ;

Any one can help in constructing a similar pattern in ms-SQL or atleast tell me what is all these doing?

I am coding this using extended stored procedure in c#.

Thanks,
Venkat.Go thru the below link and see if u get any help...

http://www.experts-exchange.com/Databases/Oracle/Q_21185214.html

Here what i could understand is..

It created the object 'lXMLContext' & set the parameters for this object for creating XML... U just need to concentrate on the last 2 only here I think so 'setRowTag' & 'setDateFormat'... the first 2 is set as 'FALSE' so u need not have to bother... the 3rd & 4th I feel its some sort of exception feature in ORACLE... to intimate the user that an error has occured while creating the XML...

:D Sorry if u could already understand this much and was asking asking for anything more....

lXMLContext := DBMS_XMLQuery.newContext("select * from table");

-- Setup parameters on how XML is to be constructed.

DBMS_XMLQuery.useNullAttributeIndicator(lXMLContex t,FALSE);
DBMS_XMLQuery.setRaiseNoRowsException(lXMLContext, FALSE);
DBMS_XMLQuery.setRaiseException(lXMLContext,TRUE);
DBMS_XMLQuery.propagateOriginalException(lXMLConte xt,TRUE);
DBMS_XMLQuery.setRowTag(lXMLContext,lWS.wsXMLRecor d.wsViewName);
DBMS_XMLQuery.setDateFormat(lXMLContext,lDateMask) ;

Oracle Stored Procedure Syntax

I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>

Oracle Stored Procedure for RDLC Dataset

Is it possible to use an Oracle Stored Procedure for an RDLC report. There are posts I've read that deal with RDL reports that use the data tab and command type of "Stored Procedure", but I don't have that installed. I just create a new dataset that the report uses. I can do reports just fine with SQL statements, but I want to be able to call a stored procedure...

Thanks

Another post that seeks to answer this question is at

http://www.codenewsgroups.net/group/microsoft.public.dotnet.framework.aspnet.webcontrols/topic6588.aspx

"Hi, i have used rdlc with the informix database. What is did was add a empty
dataset to the rdlc and i have a business layer (a vb class) which has a
function which returns a dataset. When you add a report to the page add an
objectdatasource and specify the type as the vb class and the select method
as the funtion. This works for me. "

Is this the only way to accomplish this? Does someone have the definitive answer of "No, an

Oracle Stored Procedure cannot be called by a setting in the dataset wizard for a client-side (RDLC) report."

Also, here is a post that deals with just the RDL report:

http://www.developer.com/db/article.php/3524781

Oracle Stored Procedure flakiness...

I'm on a deadline so any help would be much appreciated.
I am building a Reporting Services report that takes a number of
parameters with which to call a stored procedure. I am using the
Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
provider that I have manually installed does not show up as a choice,
but that's for another day.)
Ultimately, this report will be rendered from an ASP.NET front-end Web
application. But in order to do the formatting etc. I had it working in
MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
for a while. A mixture of dates, floats, varchar2s and numbers.
Now, I'm not sure exactly what happened, but to the best of my
recollection:
When calling the report programmtically, it was trying to do a query
for the first parameter and was failing. I didn't need it to do the
query any longer (the front end was handling that). I modified the
report parameters such that it no longer used a Query.
Now I get "PLS-00306 Wrong number or types of arguments..." when I
attempt to render the report either in MSRS HTML Viewer or through my
ASP.NET application. I've tried narrowing it down and it seems when I
add a third parameter, it fails.
I'm sorry for the long post and lack of details, but has anyone else
experienced flakiness with Report Parameters, Stored Procedures and/or
Oracle?
Regards,
PeterMy guess here is that there is a mismatch between your query parameters and
report parameters. They are two different things but it isn't obvious since
RS creates the report parameters for you. Make sure the report work from
Report Manager.
Another possibility is that the report parameter is a string and your stored
procedure is an integer or something like that. When you add a parameter it
defaults to string. Another area to check.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thanks for the quick reply Bruce.
I wish that were the case :-). I've dumbed it down so I'm passing a
VARCHAR2 and two NUMBERS.
Is there any way to see the command generated by MSRS to be sent to
Oracle? That would really help.
I am in class today, more details later. Gotta run.|||I have had some issues with SP going to Sybase. What helped me was to use
the generic query (there is a button to switch to that). Think of this as
passthrough. Put it in the format that you would if you were using a query
tool from Oracle. For instance I put in this for Sybase:
pr_test ?,?,?
Be sure to map it to the report parameters by clicking on the ... and going
to the parameter tab. I sometimes have to put in the ? on the left column
and then put in expression and then select the report parameter.
I'm not sure if ? is the placeholder for the parameter when doing this with
Oracle, it might not be. When you are in the generic query tab you are using
the managed provider for Oracle.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107372894.354513.125000@.o13g2000cwo.googlegroups.com...
> Thanks for the quick reply Bruce.
> I wish that were the case :-). I've dumbed it down so I'm passing a
> VARCHAR2 and two NUMBERS.
> Is there any way to see the command generated by MSRS to be sent to
> Oracle? That would really help.
> I am in class today, more details later. Gotta run.
>|||It won't work through the OleDB provider at design time. Choose "Oracle" as
data source type which will give you the managed provider for Oracle. Also
make sure that you use the text-based generic query designer (2 panes) and
not the visual query designer (4 panes) - you can switch between them
through an icon on the toolbar in the data view of report designer.
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
Finally, in the generic query designer, just specify the name of the stored
procedure without arguments and the parameters should get detected
automatically.
BTW: it is possible to switch from the managed Oracle to the OleDB provider
once the entire report is designed and done. E.g. after you published the
report to the production environment you can switch to the OleDB provider
using the steps discussed in detail in this related newsgroup thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/782c9647e34cb1f5/8b45a452801e7b77?q=Oracle+REF+CURSORS&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fgroup%3Dmicrosoft.public.sqlserver.reportingsvcs%26q%3DOracle+REF+CURSORS%26qt_g%3D1%26searchnow%3DSearch+this+group%26&_doneTitle=Back+to+Search&&d#8b45a452801e7b77
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thank you for the response Robert.
My stored procedure is being called when I run the Dataset that
references it from the Data pane in the Designer. It's when the stored
procedure is called from the preview pane that I get an error.
Since posting this issue, I've worked around it (somewhat) by passing
my stored procedure only one parameter: an Xml Document containing the
actual 20 or so parameters. I was getting tired of rebuilding the
parameter list (which occasionally fixed the problem).
However, please see my more recent post concerning HTML output caching.
I'm wondering if my single ~1500 character parameter is causing issues
with MSRS determining when to return cached versions of the HTML output
(even though the parameter string differes).
Once again thank you for your help. My company is evaluating moving
from an expensive and really buggy high-end reporting system to MSRS. I
am doing the proof-of-concept work and the HTML issue is causing some
concern about adopting MSRS. I'd really love to whole-heartedly endorse
it! :-)
peter|||Here is an idea. Have a parameter table that you fill with the 20 parameters
and then pass that primary key as the single parameter to RS. The parameter
will definitely be different and your caching problem will go away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107784198.997079.73660@.c13g2000cwb.googlegroups.com...
> Thank you for the response Robert.
> My stored procedure is being called when I run the Dataset that
> references it from the Data pane in the Designer. It's when the stored
> procedure is called from the preview pane that I get an error.
> Since posting this issue, I've worked around it (somewhat) by passing
> my stored procedure only one parameter: an Xml Document containing the
> actual 20 or so parameters. I was getting tired of rebuilding the
> parameter list (which occasionally fixed the problem).
> However, please see my more recent post concerning HTML output caching.
> I'm wondering if my single ~1500 character parameter is causing issues
> with MSRS determining when to return cached versions of the HTML output
> (even though the parameter string differes).
> Once again thank you for your help. My company is evaluating moving
> from an expensive and really buggy high-end reporting system to MSRS. I
> am doing the proof-of-concept work and the HTML issue is causing some
> concern about adopting MSRS. I'd really love to whole-heartedly endorse
> it! :-)
> peter
>|||Thank you Bruce, I might just give that a try. I am still hoping for a
MSFT solution though.|||My feeling is that this will improve in the next version. I just got an
email from Oracle about additional Oracle design tools for VS beta 2. So it
looks like the next version of VS will have better support for Oracle from
Oracle. Plus MS had to work around some tools limitations for this release.
The design tools were designed around oledb, not dotnet. I expect this to
improve dramatically with the next version (hopefully late summer).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107800057.304345.89930@.z14g2000cwz.googlegroups.com...
> Thank you Bruce, I might just give that a try. I am still hoping for a
> MSFT solution though.
>

Oracle Stored Procedure Design limits with MSRS

What are the Oracle Stored Procedure Design limits with MSRS'
I have a complex SP that is using temp table in Oracle.
Frist deleting the data and doing two insert queriess from select queries to
gather and transform data and place in the temp table.
Then a final select query on the temp table to return the data in the oracle
reference cursor.
This works fine in Oracle and tools like PL/SQL Developer and Crystal Report
and Enterprise.
But in the VS.NET IDE I get nothing but an empty result set back in the MSRS
report writing.
No error messages etc...
Quite puzzling...
Does anyone know why this is happening?
Is this a design issue related to a problem iwth the way Oracle and the MS
Data Provider works'
Is there a work around?
HELP'?
ThanksWhen I tried it, I was never able to make PL/SQL package calls directly from
RS. Straight T-SQL code works fine, but calling packages never returned any
result sets for me. I tried both the MS and ORA data providers.
The workaround I used was to redirect the user to an .aspx page, then make
the package call using ADO.NET. When the package is done processing, the
page then redirects to the final report which then renders in the browser.
James
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
> to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
> oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
> Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
> MSRS
> report writing.|||I don't use Oracle, so might be talking out of my arse!
But (or Butt if I am!), could you not use a query on a MS SQL server
with the Oracle server as a linked server, or use an 'openrowset' query
again on an MS SQL Server querying the Oracle server. I think I'm right
in saying that this latter option would do all the processing on the
Oracle server (and hopefully use all it's functionality) just returning
the dataset.
I have my suspicion though it all boils down to the quality of the
OLEDB/ODBC provider used. And most non-MS based systems have a vested
interest in providing poor interconnectivity with MS.
Chris
James Counihan wrote:
> When I tried it, I was never able to make PL/SQL package calls
> directly from RS. Straight T-SQL code works fine, but calling
> packages never returned any result sets for me. I tried both the MS
> and ORA data providers.
> The workaround I used was to redirect the user to an .aspx page, then
> make the package call using ADO.NET. When the package is done
> processing, the page then redirects to the final report which then
> renders in the browser.
> James
> "Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in
> message news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> > What are the Oracle Stored Procedure Design limits with MSRS'
> >
> > I have a complex SP that is using temp table in Oracle.
> >
> > Frist deleting the data and doing two insert queriess from select
> > queries to
> > gather and transform data and place in the temp table.
> >
> > Then a final select query on the temp table to return the data in
> > the oracle
> > reference cursor.
> >
> > This works fine in Oracle and tools like PL/SQL Developer and
> > Crystal Report
> > and Enterprise.
> >
> > But in the VS.NET IDE I get nothing but an empty result set back in
> > the MSRS
> > report writing.|||I know that it is possible to use Oracle Stored procedures. I have not done
it myself but I have seen people work through issues and be successfull.
Have you searched the newsgroup for any posting that refers to Oracle.
One suggestion I do have is to use the generic query window, this makes sure
that you are using the managed provider. Also, start out without parameters
and get that working. I know some people had trouble with some parameters to
Oracle Stored Procedures but they got that to work eventually. That is why I
suggest starting out with no parameters and then go forward from there.
There are certain design limits you have to follow for it to work but if you
do that you should be able to do this. Temp tables should be OK.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
MSRS
> report writing.
> No error messages etc...
> Quite puzzling...
> Does anyone know why this is happening?
> Is this a design issue related to a problem iwth the way Oracle and the MS
> Data Provider works'
> Is there a work around?
> HELP'?
> Thanks
>

Oracle Stored Procedure as datasource

I cannot get an Oracle stored procedure to work as my datasource to save my
life...
I purchased Professional SQL Server Reporting Services book from Wrox -
looking at p.115. Followed instructions exactly - but am obviously missing
something. It's not working.
In the Solution Explorer, I right-clicked "Reports" and chose "Add New
Item". Chose "Report" and named the report "Test_Parcel".
On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
Dataset dialogue appeared.
1) I named the new dataset "rebaproc" (reba is the name of the Oracle
server),
2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
I've been using for other reports - that are built on simply queries
accessing Oracle views),
3) chose "StoredProcedure" in the Command Type dropdown, and
4) typed the name of the stored procedure - sp_Test_Parcel,
5) clicked "OK"
As soon as I click "OK", I get the error:
"Could not generate a list of fields for the query. Check the query syntax
or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
The only two "IN" parameters are:
pnumUPCId IN NUMBER, /* UPC_ID to read
*/
pnumParcelId IN NUMBER, /* Parcel_Id to read
*/
When I click "Refresh Fields", RS pops up a dialog (the "Define Query
Parameters" dialog) which asks for parameter values for the pnumUPCId and
pnumParcelId. I enter values, and click OK, and get the same error
referenced above.
Not at all sure what I'm supposed to be doing... I even tried manually
adding "Report Parameters", but same issue...
Please help.
ThanksI had this same exact issue and finally got it to work. When you hit refresh
fields and it pops up with your two parameters try selecting...I think it is
the NULL at the top of the dropdown instead of actually typing in values.
Once you do that it will finally create your parameters in your report from
your stored procedure. I read the same book you did and followed
instructions on the internet just like the book and had the same results you
did - I too am using oracle. Once it created the parameters in the report it
got rid of the parameter error. If you can't get it to create them the first
time of doing this try it again...it seems to be a bit crazy and one time
took me 3 times of selecting the null to get it to work. Good Luck ;-)
Melissa
"Derek in Richmond" wrote:
> I cannot get an Oracle stored procedure to work as my datasource to save my
> life...
> I purchased Professional SQL Server Reporting Services book from Wrox -
> looking at p.115. Followed instructions exactly - but am obviously missing
> something. It's not working.
> In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> Item". Chose "Report" and named the report "Test_Parcel".
> On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> Dataset dialogue appeared.
> 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> server),
> 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> I've been using for other reports - that are built on simply queries
> accessing Oracle views),
> 3) chose "StoredProcedure" in the Command Type dropdown, and
> 4) typed the name of the stored procedure - sp_Test_Parcel,
> 5) clicked "OK"
> As soon as I click "OK", I get the error:
> "Could not generate a list of fields for the query. Check the query syntax
> or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> The only two "IN" parameters are:
> pnumUPCId IN NUMBER, /* UPC_ID to read
> */
> pnumParcelId IN NUMBER, /* Parcel_Id to read
> */
> When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> Parameters" dialog) which asks for parameter values for the pnumUPCId and
> pnumParcelId. I enter values, and click OK, and get the same error
> referenced above.
> Not at all sure what I'm supposed to be doing... I even tried manually
> adding "Report Parameters", but same issue...
> Please help.
> Thanks
>|||Thanks for responding Melissa. I tried what you suggested, but it still
doesn't seem to work.
---
"Melissa" wrote:
> I had this same exact issue and finally got it to work. When you hit refresh
> fields and it pops up with your two parameters try selecting...I think it is
> the NULL at the top of the dropdown instead of actually typing in values.
> Once you do that it will finally create your parameters in your report from
> your stored procedure. I read the same book you did and followed
> instructions on the internet just like the book and had the same results you
> did - I too am using oracle. Once it created the parameters in the report it
> got rid of the parameter error. If you can't get it to create them the first
> time of doing this try it again...it seems to be a bit crazy and one time
> took me 3 times of selecting the null to get it to work. Good Luck ;-)
> Melissa
> "Derek in Richmond" wrote:
> > I cannot get an Oracle stored procedure to work as my datasource to save my
> > life...
> >
> > I purchased Professional SQL Server Reporting Services book from Wrox -
> > looking at p.115. Followed instructions exactly - but am obviously missing
> > something. It's not working.
> >
> > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > Item". Chose "Report" and named the report "Test_Parcel".
> >
> > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > Dataset dialogue appeared.
> >
> > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > server),
> > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > I've been using for other reports - that are built on simply queries
> > accessing Oracle views),
> > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > 5) clicked "OK"
> >
> > As soon as I click "OK", I get the error:
> > "Could not generate a list of fields for the query. Check the query syntax
> > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> >
> > The only two "IN" parameters are:
> > pnumUPCId IN NUMBER, /* UPC_ID to read
> > */
> > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > */
> >
> > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > pnumParcelId. I enter values, and click OK, and get the same error
> > referenced above.
> >
> > Not at all sure what I'm supposed to be doing... I even tried manually
> > adding "Report Parameters", but same issue...
> >
> > Please help.
> >
> > Thanks
> >|||Sorry I wasn't any help. I am using the oracle OLE DB driver (not MS driver
for Oracle) so I guess that might have something to do with it...you
mentioned you are not. Here are my steps I went through which sound very
similar to yours:
1. Add existing datasource to project
2. Create New Report
3. New Dataset Name is test
4. Datasource hdbprod (same datasource I use in other reports)
5. Command Type StoredProcedure
6. Query String MAKE_ADDRESS_30DAY_TABLE
7. OK
8. OK on scary parameter error
9. Hit refresh fields button
10. Select NULL on two parameters (selecting empty won't work)
11. Report -> Report Parameters to verify two parameters were created
I'll look through my notes and see if there is anything else I can find.
Maybe someone else has some more experience with it.
Melissa
"Derek in Richmond" wrote:
> Thanks for responding Melissa. I tried what you suggested, but it still
> doesn't seem to work.
> ---
> "Melissa" wrote:
> > I had this same exact issue and finally got it to work. When you hit refresh
> > fields and it pops up with your two parameters try selecting...I think it is
> > the NULL at the top of the dropdown instead of actually typing in values.
> > Once you do that it will finally create your parameters in your report from
> > your stored procedure. I read the same book you did and followed
> > instructions on the internet just like the book and had the same results you
> > did - I too am using oracle. Once it created the parameters in the report it
> > got rid of the parameter error. If you can't get it to create them the first
> > time of doing this try it again...it seems to be a bit crazy and one time
> > took me 3 times of selecting the null to get it to work. Good Luck ;-)
> > Melissa
> >
> > "Derek in Richmond" wrote:
> >
> > > I cannot get an Oracle stored procedure to work as my datasource to save my
> > > life...
> > >
> > > I purchased Professional SQL Server Reporting Services book from Wrox -
> > > looking at p.115. Followed instructions exactly - but am obviously missing
> > > something. It's not working.
> > >
> > > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > > Item". Chose "Report" and named the report "Test_Parcel".
> > >
> > > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > > Dataset dialogue appeared.
> > >
> > > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > > server),
> > > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > > I've been using for other reports - that are built on simply queries
> > > accessing Oracle views),
> > > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > > 5) clicked "OK"
> > >
> > > As soon as I click "OK", I get the error:
> > > "Could not generate a list of fields for the query. Check the query syntax
> > > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> > >
> > > The only two "IN" parameters are:
> > > pnumUPCId IN NUMBER, /* UPC_ID to read
> > > */
> > > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > > */
> > >
> > > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > > pnumParcelId. I enter values, and click OK, and get the same error
> > > referenced above.
> > >
> > > Not at all sure what I'm supposed to be doing... I even tried manually
> > > adding "Report Parameters", but same issue...
> > >
> > > Please help.
> > >
> > > Thanks
> > >

Oracle Procedure with OUT Parameters

I get this error message when I try to create a DataSet for an Oracle
procedure that has an OUT Parameter.
PLS-00306: wrong number or types of parameters in call to 'procedure name'
The Error happens when I click on Refresh Fields.
I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
get this message when the procedure has other out types like DATE or CHAR.
Any help would be greatly appreciated.
FabianOnly out ref cursors are supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fabian" <Fabian@.discussions.microsoft.com> wrote in message
news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> I get this error message when I try to create a DataSet for an Oracle
> procedure that has an OUT Parameter.
> PLS-00306: wrong number or types of parameters in call to 'procedure name'
> The Error happens when I click on Refresh Fields.
> I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> get this message when the procedure has other out types like DATE or CHAR.
> Any help would be greatly appreciated.
> Fabian|||Thank you Robert. I wrote a wrapper.
Fabian
"Robert Bruckner [MSFT]" wrote:
> Only out ref cursors are supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Fabian" <Fabian@.discussions.microsoft.com> wrote in message
> news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> > I get this error message when I try to create a DataSet for an Oracle
> > procedure that has an OUT Parameter.
> >
> > PLS-00306: wrong number or types of parameters in call to 'procedure name'
> >
> > The Error happens when I click on Refresh Fields.
> >
> > I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> > get this message when the procedure has other out types like DATE or CHAR.
> >
> > Any help would be greatly appreciated.
> >
> > Fabian
>
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
RemiHi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
Remi
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>
|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

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.