OraFAQ Forum: SQL & PL/SQL » Error when stored proc called from vb.net

Home » SQL & PL/SQL » SQL & PL/SQL » Error when stored proc called from vb.net (Oracle 10g XE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error when stored proc called from vb.net [message #399208]

Tue, 21 April 2009 02:17

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399212 is a reply to message #399209]

Tue, 21 April 2009 02:29

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.

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399214 is a reply to message #399212]

Tue, 21 April 2009 02:47

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;

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399217 is a reply to message #399214]

Tue, 21 April 2009 03:00

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399220 is a reply to message #399217]

Tue, 21 April 2009 03:04

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?

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399224 is a reply to message #399220]

Tue, 21 April 2009 03:17

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 766 times)

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399229 is a reply to message #399224]

Tue, 21 April 2009 03:39

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.

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399263 is a reply to message #399229]

Tue, 21 April 2009 06:06

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'

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399264 is a reply to message #399263]

Tue, 21 April 2009 06:09

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?

Report message to a moderator

Re: Error when stored proc called from vb.net [message #399271 is a reply to message #399264]

Tue, 21 April 2009 06:44

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.

Report message to a moderator

Previous Topic:	Tree View Query
Next Topic:	Parallelize alter table statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 17 10:32:14 CDT 2025