Oracle Stored Procedure problem

I have an oracle stored procedure that looks something like this...

grant_role(
inEmployee IN NUMBER,
inRole IN VARCHAR2,
inGrant IN BOOLEAN,
status OUT NUMBER)

I want to run this in an ASP page using JScript...this is what I have so far...

var conn = Server.CreateObject("ADODB.Connection");
var comm = Server.CreateObject("ADODB.Command");
conn.Open(myConnection);  // This is setup in an include file

comm.CommandText = "GRANT_ROLE";
comm.CommandType = 4;
comm.Name="GRANT_ROLE";

comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));
comm.ActiveConnection = conn;
comm.Execute();

I keep getting a Type Mismatch error on the line that has 'role' being appended.  Not sure if that's because 'role' is problematic, or boolean though, as I've done a bunch of reading on forums lately that said there were problems with using / passing boolean to oracle from JScript :(

Any suggestions would be GREATLY appreciated.  Thanks in advance :)
ShaymusBane2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

NetGrooveCommented:
If you know that, why then do you not change your stored procedure to take a number for grant?
Lats say, zero is false and one is true.

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
ShaymusBane2Author Commented:
The application that I'm creating has to run in conjunction with an existing application, and if I make changes to the database the existing application will no longer function.  

Soooo...my hands are somewhat tied.  I wish I could just change the database though....but the easy way is seldom available ;)
NetGrooveCommented:
Did you try to pass 0 for fals and 1 for treu without changing the procedure?
Try also -1 for true.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ShaymusBane2Author Commented:
I had tried 1 and 0 for true / false values (hadn't thought to try -1) buy they didn't work (neither did the -1).

As I said though, the error keeps saying the mismatch is occurring on the line where 'role' is declared though.  I have used the same (similar) syntax as I posted on other stored procedures, EXCEPT, none of them have had a return value, a varchar2 or a boolean in them before...all just numbers.  So not sure if maybe that is where the problem lies.  

Thanks for the suggestions though NetGroove...as I said, I hadn't thought of trying -1...shame it didn't work out though :(
NetGrooveCommented:
Uhps, the position where you specify the value is the optional position for SIZE.

Look here:
http://www.w3schools.com/ado/met_comm_createparameter.asp

That say:
comm.Parameters.Append(comm.CreateParameter("return", 3, null, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, null, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, null, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, null, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, null, 2));

NetGrooveCommented:
Or you provide the length.

comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 0, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, 32, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, 0, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));

NetGrooveCommented:
Perhaps this:

userName = "admin";
comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 0, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, userName.length+1, userName);
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, 0, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));

ShaymusBane2Author Commented:
None of those seemed to work :(   One thing I did, was in PL/SQL, I retested to make sure that the actual stored procedure was working...it ONLY works when I place single quotes '  ' around the role....as a value that is.  If I do double quotes in Oracle it fails...wondering if this may be part of the reason why?  

Sorry for the delay on any responses, but I have been ill the past week+ and havn't had a chance to come in and try all the suggestions....thank you VERY much for them all, by the way
ShaymusBane2Author Commented:
After sitting down and talking (read 'bribing') my DBA, I was able to get a second stored procedure written that took out the boolean!  

SEEMS to be working now (no errors are occurring), but the procedure hangs.  It looks like the script isn't running, but as far as I know, the web server has scripts enabled...anything further would be appreciated...once again, sorry for the long response delay, but working around here can take a LONG time to get things done... ;)
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
JavaScript

From novice to tech pro — start learning today.