?
Solved

Calling a stored procedure In Oracle passing in a sort variable not working

Posted on 2014-07-14
6
Medium Priority
?
829 Views
Last Modified: 2014-07-14
I have a stored proc that Im calling from my ASP.Net application and Im passing in a sortfield and a cursor for my return values. So when I click on a column header Im calling the proc and passing in the header value as the sort field. It doesnt seem to be working though. I want to see if the proc is the problem first and then I will go into the application to possibily find the problem.

Here is the proc and the call

create or replace
procedure getallvendors (vSortField in varchar2, c_cursor OUT SYS_REFCURSOR) as
begin
open c_cursor for
SELECT Vendors.VENDORID, Vendors.COMPANY, Vendors.ADDRESS1, VENDORS.ADDRESS2, Vendors.CITY,
Vendors.STATE, Vendors.ZIP, Vendors.TELEPHONE, Vendors.FAX,
Vendors.SupportWebsite
FROM Vendors
ORDER BY vSortField
;
end getallvendors;


And here is the call in my code just in case:

    Sub LoadGvVendors()
        'Load the grid
        Try
            Dim cmdVendors = GenericDbAccess.CreateStoredProcCommand("GetAllVendors")
            With cmdVendors
                If SortField = "" Then
                    SortField = "Company"
                End If
                .Parameters.Add(New OracleParameter("vSortField", OracleDbType.Varchar2)).Value = SortField
                .Parameters.Add(New OracleParameter("c_cursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
            End With

            Dim dsVendors = GenericDbAccess.CreateDataSet(cmdVendors, "GetAllVendors")
            Dim dtVendors As New DataTable
            dtVendors = dsVendors.Tables("GetAllVendors")

            'Persist the table in the Session object.
            Session("dtVendors") = dtVendors

            'Bind the GridView control to the data source.
            gvVendors.DataSource = Session("dtVendors")
            gvVendors.DataBind()

        Catch ex As Exception
            Throw ex
            'Response.Redirect("UnderContruction.aspx")
        End Try
    End Sub
0
Comment
Question by:jknj72
  • 3
  • 3
6 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40194471
Make the select a string and concatenate the vSortField to it.  Otherwise you are ordering by the 'string' itself.

Try this:
create or replace
 procedure getallvendors (vSortField in varchar2, c_cursor OUT SYS_REFCURSOR) as
 begin
 open c_cursor for
 'SELECT Vendors.VENDORID, Vendors.COMPANY, Vendors.ADDRESS1, VENDORS.ADDRESS2, Vendors.CITY, 
 Vendors.STATE, Vendors.ZIP, Vendors.TELEPHONE, Vendors.FAX, 
 Vendors.SupportWebsite
 FROM Vendors
 ORDER BY ' || vSortField
 ;
 end getallvendors;

Open in new window

0
 

Author Comment

by:jknj72
ID: 40194526
I see what you mean and I corrected it but it is not sorting correctly...IIt doesnt seem to be doing any kind of sorting since Ive added this. Any other thoughts?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40194535
Works for me.

Below is my test case I used in sqlplus.

drop table tab1 purge;
create table tab1(col1 char(1),col2 char(1));

insert into tab1 values('a','3');
insert into tab1 values('b','2');
insert into tab1 values('c','1');
commit;

create or replace procedure myproc(vSortField in varchar2, c_cursor out sys_refcursor)
is
begin
	open c_cursor for 'select col1,
		col2 from tab1
		order by ' || vSortField;
end;
/

show errors 

--test it with sqlplus variables
var mycur refcursor
exec myproc('col1',:mycur);
print mycur


exec myproc('col2',:mycur);
print mycur

Open in new window

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:jknj72
ID: 40194578
I see that you treat the select statement as a string and then execute it. I dont have it being done that way. Should I try it like that?

Fast forward 10 mins later......

I tried it and it worked. Seems like the way you call your statement within the Procedure worked for me.

Once again slight...THANK YOU!!!
0
 

Author Closing Comment

by:jknj72
ID: 40194579
great job again!!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40194602
No problem.  Glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question