Error occurs while executing stored procedure

Hi

I'm using following code to insert record in a table in oracle 10g. Stored procedure compiles and executes properly on oracle server. I checked with anonymous block. But when I execute following code I get exception as follows. Initially I did not use out parameter and this code worked fine. When I added out parameter, it is giving error.

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
   <> 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
   <> 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
   <> 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
   <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between || multiset

Exception Source: Microsoft OLE DB Provider for Oracle

 
1    Dim iCatId As Integer = -1
2            commandLibrarian = New OleDbCommand("PLIB_LIBRARIAN_PKG.INS_PLIB_CATALOG", connectLibrarian)
3            commandLibrarian.CommandType = CommandType.StoredProcedure
4            Try
5                commandLibrarian.Parameters.Add("P_CAT_TITLE", OleDbType.VarChar).Value = title
6                commandLibrarian.Parameters.Add("P_CAT_DESCRIPTION", OleDbType.VarChar).Value = description
7                commandLibrarian.Parameters.Add("P_CATEGORY_ID", OleDbType.VarChar).Value = categoryId
8                commandLibrarian.Parameters.Add("P_CAT_AUTHOR", OleDbType.VarChar).Value = catAuthor
9                commandLibrarian.Parameters.Add("P_CAT_PUBLISHED_DATE", OleDbType.VarChar).Value = catPublishedDate
10               commandLibrarian.Parameters.Add("P_CAT_PUBLISHER", OleDbType.VarChar).Value = catPublisher
11               commandLibrarian.Parameters.Add("P_CAT_EVAL_RELEVANT_KEYWORDS", OleDbType.VarChar).Value = eval_relevant_keywords
12               commandLibrarian.Parameters.Add("P_CAT_REG_NUMBER", OleDbType.VarChar).Value = reg_number
13               commandLibrarian.Parameters.Add("P_CAT_REG_SUBNUMBER", OleDbType.VarChar).Value = reg_subnumber
14               commandLibrarian.Parameters.Add("P_CAT_CREATED_BY", OleDbType.VarChar).Value = createdBy
15               commandLibrarian.Parameters.Add("P_CAT_CREATED_DATE", OleDbType.VarChar).Value = createdDate
16               commandLibrarian.Parameters.Add("P_CAT_MODIFIED_BY", OleDbType.VarChar).Value = modifiedBy
17               commandLibrarian.Parameters.Add("P_CAT_MODIFIED_DATE", OleDbType.VarChar).Value = modifiedDate
18               commandLibrarian.Parameters.Add("P_CAT_RESTRICTION_LEVEL", OleDbType.VarChar).Value = restrictionLevel
19               commandLibrarian.Parameters.Add("P_LOCN_SITE", OleDbType.VarChar).Value = locnSite
20   
21               Dim param As New OleDbParameter("P_CAT_ID", OleDbType.Integer)
22               param.Direction = ParameterDirection.Output
23               commandLibrarian.Parameters.Add(param)
24   
25               connectLibrarian.Open()
26               commandLibrarian.ExecuteNonQuery()
27               iCatId = param.Value
28               connectLibrarian.Close()
29           Catch oraEx As OleDb.OleDbException
30               objErrorLog.writeError(oraEx)
31   
32           Catch ex As Exception
33               MsgBox(ex.Message)
34               objErrorLog.writeError(ex)
35               iCatId = -1
36           End Try
37           Return iCatId

 WHat is wrong with code?

 Regards,

Leena

0
Puneri_25
4/21/2009 7:47:24 AM
asp.net.oracle 2269 articles. 0 followers. Follow

5 Replies
1769 Views

Similar Articles

[PageSpeed] 26

Try to use an 'OracleCommand' instead. I also suffered from using 'OleDbCommand' and was not able to solve the similar problem like this.


Many Thanks & Best Regards,
HuaMin Chen

(Mark it as answer if it does help you!)
0
wmec
4/21/2009 8:13:24 AM

Thanks.

line no 17 was causing error. modifiedDate variable was null. So when I tried to insert null value in date colunm (which is nullable col) it was causing error.

when I checked with Today(), row was inserted. If I use Oraclecommand can I insert null value in date colunm.

0
Puneri_25
4/21/2009 8:21:13 AM

It depends on whether that column is set as 'Not null' or not. Or you can use nvl for avoiding any potential problem.

(use this in your Oracle stored proc, like nvl(date_field, 2nd_value) and put the date value you want for the 2nd value when date_field is null)


Many Thanks & Best Regards,
HuaMin Chen

(Mark it as answer if it does help you!)
0
wmec
4/21/2009 8:23:42 AM

can we use nvl in insert statement? generally i've seen in select statements only.

The column is nullable. in vb.net i used date = nothing to set date as null

It seems this is wrong way to do. But which is right way to set date null?

0
Puneri_25
4/21/2009 9:04:37 AM

You can use the way in bold above in your Oracle stored proc.


Many Thanks & Best Regards,
HuaMin Chen

(Mark it as answer if it does help you!)
0
wmec
4/21/2009 9:10:59 AM
Reply:

Similar Artilces:

Oracle Error Executing Stored Procedure
 HiI'm invoking a stored procedure from Oracle Database. The procedure is running Ok in oracle, but when i run the procedure from .NET, failed. The code when stop is :                  [CODE]    Reader = cmdReader.ExecuteReader()[/CODE]                          The error is:[CODE]A first chance exception of type 'System.Data.OracleClient.OracleException' occu...

Oracle Stored Procedure Execution Error
Hi ,   I am trying a simple code to execute a oracle stored procedure. I encountered the below error when executing the web page :-            ERRORERROR [42000] [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement My stored procedure can be successfully executed from  SQLPLUS command.  I have enlisted my codes below. Really appreciate your help as I have no idea what is the problem. I have also looked for any similar problems in the Forum but can't get a solution. Thanks !. Regards, Thana. ----------------------...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

Oracle stored procedure/execute immediate error.
I'm getting the following runtime error calling an Oracle stored procedure from within Powerbuilder(though that procedure works when run Oracle-side): SQLSTATE = S1000 [Oracle][ODBC][Ora]ORA-00922: missing or invalid option ORA-06512: at "SYS1.PK_SYS1_USER_MAINT", line 33 ORA-06512: at line 1 The code is as follows: string proc_statement, ls_user ls_user = sle_user.text proc_statement = "begin pk_sys1_user_maint.add_user('" + ls_user + "','" + gs_default_password + "'); end;" execute immediate :proc_statement using sys1;...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Newbie
 Hello Everyone, I had recently started my job as an asp.net developer. I am a newbie. I am getting an error while executing more than one oracle stored procedure with both oracle client and oledb client. While I m using sqlserver everything is smooth. But I dont why it is giving me errors with Oracle. When I m using Oledb command object, after successful execution of the first stored procedure I had to dispose the command object and had to proceed to another one. If I m not disposing then it will throw an error as "wrong type or number of arguements". But when it comes to Ora...

Error while calling .Net web service from Oracle Stored Procedure
Hi,     We are getting the below error while calling an .net web service ( WCF) from Oracle Stored procedure.  Type: System.Exception, mscorlib, version = 2.0.0.0, culture = neutral,publickeytoken=b77a5c561934e089 Message: No client Registry found Data: System.Collections.ListDictionaryInternal Any information on the above will be helpful. Thanks. Sowmya Jayappa  ...

Does ADO.Net throw an exception if error occurs within a stored procedure?
If I have a stored procedure that encounters an error (which means @@ERROR > 0 within this SQL Server 2000 stored procedure) while executing, then will ADO.Net throw any exception, if the stored procedure was called using 'ExecuteNonQuery' or 'ExecuteReader' or 'ExecuteScalar' methods of a command object?sun21170 Yes, for example i use the following SQL query to raise the error raiserror ("Error Message here",16 ,1)return I can catch it like this Trycom,executenonquery()Catch ex as exception ' could be sqlexceptionResponse.write(ex.message) End Try I think tmorton will reply t...

Error ORA-20002 executing Oracle 7.3 remote stored procedure
Hi, I get error ORA-20002 when executing a stored procedure in a remote Oracle 7.3 database (through a datalink). I know this is caused by the odessp()function of the Oracle Call Interface (OCI) API which is called by the Powerbuilder Oracle driver (PBO73050.DLL) to describe the parameters of a PL/SQL procedure or function stored in an Oracle database. The error is explained in the Oracle documentation: -20002: A database link was specified in objnam, either explicitly or by means of a synonym. My code is as follows: String sMyParam ... lValue = SQLCA.MY_REMOTE_...

Error ORA-20002 executing Oracle 7.3 remote stored procedure
Hi, I get error ORA-20002 when executing a stored procedure in a remote Oracle 7.3 database (through a datalink). I know this is caused by the odessp() function of the Oracle Call Interface (OCI) API which is called by the Powerbuilder Oracle driver (PBO73050.DLL) to describe the parameters of a PL/SQL procedure or function stored in an Oracle database. The error is explained in the Oracle documentation: -20002: A database link was specified in objnam, either explicitly or by means of a synonym. My code is as follows: String sMyParam .... lValue = SQLCA.MY_REMOTE_PROC...

Error ORA-20002 executing Oracle 7.3 remote stored procedure
Hi, I get error ORA-20002 when executing a stored procedure in a remote Oracle 7.3 database (through a datalink). I know this is caused by the odessp()function of the Oracle Call Interface (OCI) API which is called by the Powerbuilder Oracle driver (PBO73050.DLL) to describe the parameters of a PL/SQL procedure or function stored in an Oracle database. The error is explained in the Oracle documentation: -20002: A database link was specified in objnam, either explicitly or by means of a synonym. My code is as follows: String sMyParam ... lValue = SQLCA.MY_REMOTE_...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

ADO Command Object Error when Errors Occur in Stored Procedure
Has anyone experienced this (apparent) bug? If a stored procedure encounters an error which is trapped using @@ERROR (say, for example, an insert violates a foreign key constraint, but this was anticipated and worked into the logic of the procedure), any Input/Output parameters and/or the standard RETURN value are not updateable? Return always appears as empty when queried at the ADO level, and the parameter retains its very first value, regardless of what they were set to in the body of the procedure. I'm using VB 6, ADO 2.5 Command Object, and Sybase ASE 11.0.3. This erro...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

Web resources about - Error occurs while executing stored procedure - asp.net.oracle

Self-executing rule - Wikipedia, the free encyclopedia
The self-executing rule , also known as " deem and pass ", is procedural measure used by the U.S. House of Representatives to approve legislation. ...

Saudi Arabia condemned for executing prominent Shiite cleric on terrorism charges
Saudi Arabia executes a prominent Shiite Muslim cleric alongside dozens of al-Qaeda members, stirring a rise in sectarian tensions across the ...

Islamic State video purports to show boy executing two Russian 'spies'
Executions of captives by the Sunni Muslim militants of the Islamic State have become grimly commonplace, but a newly surfaced video featured ...

Parramatta shooting: police leave empty-handed after executing search warrant at mosque
Police executed a search warrant at a Parramatta mosque overnight as part of their investigations into the fatal shooting on Friday of a civilian ...

US states having trouble executing their citizens
Despite a Supreme Court ruling allowing a controversial drug to be used for lethal injections in Oklahoma, death-penalty states are finding it ...


Islamic State video purports to show boy executing 'Russian spies'
Islamic State releases video that purports to show a young boy executing two men accused of working for Russian intelligence.

Boko Haram executing elderly in northeast Nigeria
Islamic extremists in northeast Nigeria are turning their guns on elderly people, killing more than 50 this week in a new tactic that has instilled ...

New iOS security exploit lets apps read users’ information by executing unsigned code
Security expert Charlie Miller has found a flaw in code signing on iOS devices (via Forbes) that allows developers to sneak malware apps onto ...

Law Journal Apologizes For Article About Executing Law Professors, Professor Resigns
Insane law review article prompts professor's resignation... but how did he get hired in the first place?

Resources last updated: 1/16/2016 9:36:49 PM