Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Unanswered: How to pass NULL values in a stored procedure

    Hi there,
    I want to execute a stored procedure having some parameters, the procedure is simply executing an insert query for a table, now I want to pass NULL value for some of the fields, I had tried this, but it gave the error
    -----------------

    execute SP_INS_CMN_CASH_RECEIPT_DET(1,1,0,50,'A',428086,'1 3-APR-2004',1,,,2,2004,'2312-1010-0082',1,9000,,'N','N','01-JAN-2002','01-JAN-2002','N')
    ----------------
    I had left blank the values which I wanna to be NULL, The error is
    --------------
    BEGIN SP_INS_CMN_CASH_RECEIPT_DET(1,1,0,50,'A',428086,'1 3-APR-2004',1,,,2,2004,'2312-1010-0082',1,90

    *
    ERROR at line 1:
    ORA-06550: line 1, column 71:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute cast trim forall
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>
    The symbol "null was inserted before "," to continue.
    ORA-06550: line 1, column 104:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum
    -----------------------------
    The stored procedure is attached as a .sql file.
    Please help.
    Thanking u in anticipation
    RGDS
    Amit
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to pass NULL values in a stored procedure

    You must pass the keyword NULL, like this:

    execute SP_INS_CMN_CASH_RECEIPT_DET(1,1,0,50,'A',428086,'1 3-APR-2004',1,NULL,NULL,2,2004,'2312-1010-0082',1,9000,NULL,'N','N','01-JAN-2002','01-JAN-2002','N')

  3. #3
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Re: How to pass NULL values in a stored procedure

    Hi,
    Thanx for ur response, But I wanna to call the procedure from the VB application and in doing so, How will I pass the NULL, coz at that side there will be all variables which either have some value or have NULL.
    Plz do reply
    Byee
    RGDS
    Amit

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to pass NULL values in a stored procedure

    Just pass the variables: if they are NULL, then that's what Oracle will receive.

    You should use bind variables if this is going to be performed many times with different inputs.

  5. #5
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Re: How to pass NULL values in a stored procedure

    Thanx but on VB I pass NULL as a parameter in the procedure n it gave the error----
    ---------------------------------
    ERROR at line 1:
    ORA-06550: line 1, column 71:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute cast trim forall
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>
    The symbol "null was inserted before "," to continue.
    ORA-06550: line 1, column 104:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum
    ---------------------------------------
    this means that Oracle procedure does not receive the same NULL as it required , as it supplied from VB
    thanks
    RGDS
    Amit

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    There is one thing to remember when passing nulls. This will override the column's DEFAULT setting. For example,

    SQL> CREATE TABLE TEST (COL1 NUMBER, COL2 DATE DEFAULT SYSDATE);
    SQL> INSERT INTO TEST (COL1, COL2) VALUES (1, NULL);
    SQL> SELECT * FROM TEST;

    COL1 COL2
    --- ----
    1

    This is because DEFAULT is only applied when the user passes no data, but you passed the explicit value of NULL. Oracle expects you to only pass values for columns you want to set, not every column in the table. It's a lot more work, I know, but it's the way Oracle is.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  7. #7
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Re: How to pass NULL values in a stored procedure

    This is the function which I am using in VB to call the procedure of oracle
    ---------------------------------------------
    Sub CallStoredProcedure1(strProcedureName As Variant, _
    intTotalParameters As Integer, _
    ParamArray ProcedureArguments() As Variant)

    On Error GoTo ErrPara

    For i = 1 To intTotalParameters
    cmd.Parameters.Append cmd.CreateParameter(, adVariant, adParamInput)
    Next

    'cmd.Parameters.Append cmd.CreateParameter(, adVariant, adParamOutput)

    For i = 0 To intTotalParameters - 1
    cmd.Parameters(i).Value = ProcedureArguments(i)
    Next
    cmd.Execute
    ' MsgBox cmd.Parameters(14).Value
    Exit Sub
    ErrPara:
    MsgBox err.Description

    End Sub
    ---------------------------------------------------------------------
    This is how I call the stored procedure from VB
    All the command objects n connection objects have been properly defined
    and declared.
    -------------------------------------------------------------------

    dim cmd as New Adodb.Command

    cmd.ActiveConnection = myCon
    cmd.CommandText = "SP_INS_CMN_CASH_RECEIPT_DET"
    cmd.CommandType = adCmdStoredProcedure


    CallStoredProcedure1 "SP_INS_CMN_CASH_RECEIPT_DET", 21, IIf(IsNull(icsccode), Null, icsccode), IIf(IsNull(commongrid!departmentcd), Null, commongrid!departmentcd), IIf(IsNull(windownum), Null, windownum), IIf(IsNull(usercode), Null, usercode), "A", IIf(IsNull(receipt), Null, receipt), IIf(IsNull(userdate), Null, userdate), IIf(IsNull(commongrid!S_NO), Null, commongrid!S_NO), Null, Null, IIf(IsNull(commongrid!bill_month), Null, commongrid!bill_month), IIf(IsNull(commongrid!bill_year), Null, commongrid!bill_year), IIf(IsNull(Trim(commongrid!primarykey)), Null, Trim(commongrid!primarykey)), IIf(IsNull(commongrid!paymentcd), Null, commongrid!paymentcd), IIf(IsNull(commongrid!Amount), Null, commongrid!Amount), IIf(IsNull(commongrid!remarks), Null, commongrid!remarks), "N", "N", "01/01/2002", "01/01/2002", "N"

  8. #8
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    Thanks but in my case there is no coloumn with DEFAULT VALUE and if there is no value corresponding to the field then it expects that there should stored NULL.
    RGDS
    Amit

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to pass NULL values in a stored procedure

    So really your issue is not about how to pass NULLs to Oracle stored procedures (your CallStoredProcedure1 subroutine is doing that), it is how to pass NULLs to a VB subroutine!

    I don't know VB very well, but I suspect the answer may be "" for strings at least.

    You would probably do better to ask this in a VB forum.

  10. #10
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10

    Re: How to pass NULL values in a stored procedure

    Originally posted by andrewst
    So really your issue is not about how to pass NULLs to Oracle stored procedures (your CallStoredProcedure1 subroutine is doing that), it is how to pass NULLs to a VB subroutine!

    I don't know VB very well, but I suspect the answer may be "" for strings at least.

    You would probably do better to ask this in a VB forum.

    try in this way:

    Dim param1
    ---
    cmd.Parameters.Append cmd.CreateParameter("param1", adVarChar, adParamInput, Len(param1), vbNullString)
    ---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •