Error executing Oracle Anonymous Block in ASP.NET MVC

1

I have the following anonymous block which runs just fine in Oracle SQL Developer and performs the expected insert but when I run it from my ASP.NET Code it bombs out (exception details below).

DECLARE
  L_PKID      NUMBER;
  P_NOTE_TXT  ICE.NOTE_TEXT.NOTE_TEXT%TYPE;
  P_USERID    ICE.NOTE_TEXT.CREATED_BY%TYPE;
  L_USER_NAME ICE.NOTE.CREATED_BY_NAME%TYPE;

BEGIN
  P_NOTE_TXT  := 'This is a sample note. There are many like it but this one is mine.';
  P_USERID   := 'SIMMONSJ404';
  --
  -- Get primary key, user name:
  --
  SELECT oid_seq.nextval INTO L_PKID FROM dual;

  SELECT FIRST_NAME||' '||LAST_NAME INTO L_USER_NAME
  FROM ICE.USR
  WHERE USR_ID = P_USERID
  AND ROWNUM = 1
  ORDER BY DATE_CREATED DESC;

  --
  -- BEGIN INSERTS: Note, Note_Text:
  --
  INSERT INTO ICE.NOTE (
  NOTE_OID, SYS_GENR_NOTE_IND, CREATED_BY_NAME, MODIFIED_BY_NAME,
  LAST_MOD_DTE, USR_CRTE_DTE, CREATED_BY, DATE_CREATED, RECORD_STATUS,
  DATE_MODIFIED, MODIFIED_BY, WRITE_COUNT, BUS_AREA_NOTE_CAT_ID
  ) VALUES (
  L_PKID, '0', L_USER_NAME, L_USER_NAME,
  sysdate, sysdate, P_USERID, sysdate, 'A',
  sysdate, P_USERID, 1, 3000000);

  INSERT INTO ICE.NOTE_TEXT (
  NOTE_OID, NOTE_LINE_NBR, NOTE_TEXT, RECORD_STATUS,
  DATE_CREATED, DATE_MODIFIED, WRITE_COUNT
  ) VALUES ( 
  L_PKID, 1, P_NOTE_TXT, 'A',
  sysdate, sysdate, 1);


END;       
COMMIT;

The error I get from .NET is:

System.Data.OracleClient.OracleException (0x80131938): ORA-06550: line 1, column 8:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor

The pl/sql above is simply declared in a var sql = @""; block for now with hard-coded values which will be swapped out when I demonstrate I can get the insert to work from C#. Execution is done by building up an OracleCommand object with a connection and the sql then calling ExecuteNonQuery().

What am I missing?

c#
asp.net-mvc
oracle
asked on Stack Overflow Mar 10, 2017 by Mike C. • edited Mar 10, 2017 by Mike C.

2 Answers

1

Ralph W. gave me the answer:

The C# app on Windows terminates lines with Environment.NewLine and Oracle doesn't like this. Doing the following corrects this: sql.Replace(Environment.NewLine,"\n")

This solved the error cited above but then reveals that COMMIT; causes an error because ExecuteNonQuery() implicitly adds a COMMIT. Removed that from the query and now all is working.

answered on Stack Overflow Mar 10, 2017 by Mike C.
0

Just noticed something, and I'm not sure but...maybe you need to escape the ' (single quote) in the comment : -- Get primary key, user''s name:

answered on Stack Overflow Mar 10, 2017 by Ionut Ungureanu

User contributions licensed under CC BY-SA 3.0