CODEWITHSUNDEEP
oracle-databasestored-proceduresasp-classic
Madam Zu Zu
Madam Zu Zu

Reputation: 6615

Calling Oracle Stored Procedure From Classic ASP

here is a snippet of my code:

  Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = objCon
       cmd.CommandType = 4
    cmd.CommandText = "SP_USERLOGIN"

       response.Write(p_user_id)
       cmd.Parameters.Append cmd.CreateParameter ("User_locked",adVarChar, adParamInput)
        cmd.parameters(0).value = p_user_id
       cmd.Execute

When i try to Append the parameter, i get the following error:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

my stored procedure only takes 1 parameter: 

create or replace PROCEDURE        SP_USERLOGIN ( User_locked VARCHAR2 ) as
BEGIN
update tusers Set user_account_status='status' where user_id = User_locked;
commit;
END;

Upvotes: 1

Views: 3950

Answers (2)

user1945586
user1945586

Reputation: 11

A tiny (Oracle) workaround for replacing the clumsy cmd object and its parameter handling. Suppose you want to execute PROC1, a stored procedure and pass it two parameters. You make a dummy table called DUMMY with two fields corresponding in type to the parameters. You insert one row with values whatever. You make a before-update trigger to DUMMY. The trigger must do exactly the same thing as PROC1 would and reads its parameters as fields of the "updated" record (:new.a, :new.b). In ASP you use an "update schema.dummy set a=..., b=..." statement and that's it. Disadvantage: unorthodoxy. Advantage: cleaner ASP code; easy debugging. + return parameters possible the same way backwards, reading the dummy table (any table, by the way).

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

The problem turned out to be that the constants such as adVarChar, etc. weren't defined. There may have been a second problem with the length of the parameter not defined (that's the 4th argument)

There's a file name ADOVBS.INC that defines these, and if it isn't included then the constant values must be used instead.

To problem went away when this...

cmd.Parameters.Append cmd.CreateParameter ("User_locked",adVarChar, adParamInput)

... was changed to this:

cmd.Parameters.Append cmd.CreateParameter ("User_locked",200, 1, 30)

The 30 above is a length value for the varchar.

Upvotes: 2

Related Questions