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

Pass NULL to Oracle stored procedure

Hi,

I've got some server-side VBScript passing parameters to an Oracle stored procedure.

What I can't get to work, is passing a NULL value.

Lets say I have a stored procedure defined as:

        CREATE OR REPLACE PROCEDURE my_proc(date_in DATE)

And VBScript calling it:

      Set objCommand = Server.CreateObject("ADODB.Command")
      Set objCommand.ActiveConnection = objConnection
      On Error Resume Next
      With objCommand
            .CommandText = "my_proc"
            .CommandType = 4
            .Parameters.Append .CreateParameter("date_in", 133, 1, 4, date_val)
            .Execute()
      End With

Is it possible to pass a NULL to the stored procedure as the date is optional? Everything I've tried has failed.


Thanks in advance,

Leon
0
leonst
Asked:
leonst
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Set objCommand = Server.CreateObject("ADODB.Command")
     Set objCommand.ActiveConnection = objConnection
     On Error Resume Next
     With objCommand
          .CommandText = "my_proc"
          .CommandType = 4
          .Parameters.Append .CreateParameter("date_in", 133, 1, 4, NULL)
          .Execute()
     End With

0
 
leonstAuthor Commented:
When I execute this (by submitting a form) the browser throbber runs for 10 mins and then I get "This page contains no data".
0
 
FSIFMCommented:
I know that NULL in Oracle is NVL, but I am not sure if this is the same as passing it through into a SP.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hongjunCommented:
Another method is not to first give a default value to your parameter NULL.
    CREATE OR REPLACE PROCEDURE my_proc(date_in DATE=NULL)

Then in your asp, don't pass in a parameter.

hongjun
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>When I execute this (by submitting a form) the browser throbber runs for 10 mins and then I get "This page contains no data".

how long does the procedure run when you run in manually (sql*plus ...)?
0
 
leonstAuthor Commented:
Angellll: It's a simple procedure which takes <1s to run manually.
0
 
leonstAuthor Commented:
Well, I've tried this again and it's working fine now.

Very strange.

The working command is:

objCommand.Parameters.Append objCommand.CreateParameter("date_in", 133, 1, 4, NULL).

So points to Angellll, many thanks!
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!

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