• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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 :)
0
ShaymusBane2
Asked:
ShaymusBane2
  • 5
  • 4
1 Solution
 
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.

0
 
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 ;)
0
 
NetGrooveCommented:
Did you try to pass 0 for fals and 1 for treu without changing the procedure?
Try also -1 for true.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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 :(
0
 
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));

0
 
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));

0
 
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));

0
 
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
0
 
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... ;)
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now