我有以下匿名块,该块在Oracle SQL Developer中可以正常运行并执行预期的插入,但是当我从ASP.NET代码运行该块时,它会爆炸(下面的异常详细信息)。
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;
我从 .NET 得到的错误是:
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
上面的 pl/sql 现在只是在一个var sql = @"";
块中声明了硬编码值,当我证明我可以从 C# 获得插入工作时,这些值将被换出。执行是通过建立一个OracleCommand
带有连接的对象和 sql 然后调用ExecuteNonQuery()
.
我错过了什么?
Ralph W. 给了我答案:
Windows 上的 C# 应用程序使用 Environment.NewLine 终止行,而 Oracle 不喜欢这样。执行以下操作可以更正此问题:sql.Replace(Environment.NewLine,"\n")
这解决了上面引用的错误,但随后揭示了COMMIT;
导致错误的原因,因为ExecuteNonQuery()
隐式添加了 COMMIT。从查询中删除了它,现在一切正常。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句