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?
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.
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:
User contributions licensed under CC BY-SA 3.0