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

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
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
jknj72Author Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
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
jknj72Author Commented:
great job again!!
0
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.