Home » SQL & PL/SQL » SQL & PL/SQL » Error when stored proc called from vb.net (Oracle 10g XE)
Error when stored proc called from vb.net [message #399208] Tue, 21 April 2009 02:17 Go to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
I've simple procedure to insert record in a table. It compiles on oracle. But I call from vb.net setting all in and out parameters I get error as below,
Exception: ORA-06550: line 1, column 388:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

. ( ) , * @ % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "( was inserted before ">" to continue.
ORA-06550: line 1, column 465:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( ) , * % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset
ORA-06550: line 1, column 388:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

. ( ) , * @ % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "( was inserted before ">" to continue.
ORA-06550: line 1, column 465:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( ) , * % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset

Exception Source: Microsoft OLE DB Provider for Oracle


procedure INS_PLIB_CATALOG (
   P_CAT_TITLE                  in varchar2,
   P_CAT_DESCRIPTION            in varchar2 default null,
   P_CATEGORY_ID                in varchar2,
   P_CAT_AUTHOR                 in varchar2 default null,
   P_CAT_PUBLISHED_DATE         in varchar2 default null,
   P_CAT_PUBLISHER              in varchar2 default null,
   P_CAT_EVAL_RELEVANT_KEYWORDS in varchar2,
   P_CAT_REG_NUMBER             in varchar2,
   P_CAT_REG_SUBNUMBER          in varchar2,
   P_CAT_CREATED_BY             in varchar2,
   P_CAT_CREATED_DATE           in varchar2,
   P_CAT_MODIFIED_BY            in varchar2 default null,
   P_CAT_MODIFIED_DATE          in varchar2 default null,
   P_CAT_RESTRICTION_LEVEL      in varchar2,
   P_LOCN_SITE                  in varchar2,
   P_CAT_ID                     out number
) is 

begin

  insert into PLIB_CATALOG 
(CAT_TITLE, CAT_DESCRIPTION, CATEGORY_ID, CAT_AUTHOR, 
CAT_PUBLISHED_DATE, CAT_PUBLISHER, CAT_EVAL_RELEVANT_KEYWORDS, 
  	CAT_REG_NUMBER, CAT_REG_SUBNUMBER, CAT_CREATED_BY, 
CAT_CREATED_DATE, CAT_MODIFIED_BY, CAT_MODIFIED_DATE, 
CAT_RESTRICTION_LEVEL, LOCN_SITE) 
values (P_CAT_TITLE, 
P_CAT_DESCRIPTION, 
P_CATEGORY_ID, 
P_CAT_AUTHOR, 
to_date(P_CAT_PUBLISHED_DATE, 'YY-MM-DD'), 
P_CAT_PUBLISHER, 
P_CAT_EVAL_RELEVANT_KEYWORDS,
P_CAT_REG_NUMBER, 
P_CAT_REG_SUBNUMBER, 
P_CAT_CREATED_BY, 
to_date(P_CAT_CREATED_DATE, 'YY-MM-DD'), 
P_CAT_MODIFIED_BY,
to_date( P_CAT_MODIFIED_DATE, 'YY-MM-DD'), 
P_CAT_RESTRICTION_LEVEL, 
P_LOCN_SITE);
  select max(cat_id) into P_CAT_ID from PLIB_CATALOG;				  
end INS_PLIB_CATALOG;


How to correct this error?

[Updated on: Tue, 21 April 2009 02:18] by Moderator

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399209 is a reply to message #399208] Tue, 21 April 2009 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

There is NO > in what you posted.
If you do not post the actual code, we can't help.

And keep your lines in 80 characters width.

Regards
Michel
Re: Error when stored proc called from vb.net [message #399212 is a reply to message #399209] Tue, 21 April 2009 02:29 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
this is the actual code I've copy pasted. as i said this procedure compiles on oracle, because there is no > symbol in it. But when I call from vb.net this is the error i'm getting.
I dont know where to look for error.
Re: Error when stored proc called from vb.net [message #399214 is a reply to message #399212] Tue, 21 April 2009 02:47 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
following anonymous block works perfectly.

declare
i number(5);
begin
PLIB_LIBRARIAN_PKG.INS_PLIB_CATALOG('P_CAT_TITLE', 'P_CAT_desc', 'Common', 'P_CAT_AUTHOR', sysdate, 'P_CAT_PUBLISHER', 'P_CAT_EVAL_RELEVANT_KEYWORDS',
'123', '1', 'leena', sysdate, null,null, 'public', 'QNMP', i);
DBMS_OUTPUT.PUT_LINE(i);
end;
Re: Error when stored proc called from vb.net [message #399217 is a reply to message #399214] Tue, 21 April 2009 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle find a > then there is a > somewhere and you don't call what you think you call.
In addition, this is a compilation error and not and execution one. You can't have this error during execution of a package.

Regards
Michel
Re: Error when stored proc called from vb.net [message #399220 is a reply to message #399217] Tue, 21 April 2009 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is most likely to be an error in the code that .NET is passing to Oracle.

Can you post the calling code?
Re: Error when stored proc called from vb.net [message #399224 is a reply to message #399220] Tue, 21 April 2009 03:17 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
PFA .Net code. I've found the error.
commandLibrarian.Parameters.Add("P_CAT_MODIFIED_DATE", OleDbType.VarChar).Value = Nothing

This line was causing error. when I changed it to
commandLibrarian.Parameters.Add("P_CAT_MODIFIED_DATE", OleDbType.VarChar).Value = Today()

This worked properly. But now how do I insert null in date column. I can not add sysdate to every null date column.
  • Attachment: code.txt
    (Size: 2.07KB, Downloaded 139 times)
Re: Error when stored proc called from vb.net [message #399229 is a reply to message #399224] Tue, 21 April 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't know how you'd represent a null date in VB.

The P_Cat_Modified_Date parameter has a default value of null, so you could just omit the parameter entirely if you want to pass null into it.
Re: Error when stored proc called from vb.net [message #399263 is a reply to message #399229] Tue, 21 April 2009 06:06 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
if i omit that parameter completely i'm getting
wrong number or types of arguments in call to 'INS_PLIB_CATALOG'
Re: Error when stored proc called from vb.net [message #399264 is a reply to message #399263] Tue, 21 April 2009 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It must be building a call to the procedure using positional notation rather than named notation - that's a shame.

Is this any use to you?
Re: Error when stored proc called from vb.net [message #399271 is a reply to message #399264] Tue, 21 April 2009 06:44 Go to previous message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
maybe it is problem with Oracle 10g XE.
Because it does not support overloading in package.
Previous Topic: Tree View Query
Next Topic: Parallelize alter table statement
Goto Forum:
  


Current Time: Mon Dec 05 02:39:02 CST 2016

Total time taken to generate the page: 0.09154 seconds