oracle 数据库存储过程编译报错PLS-00103出现符号 end-of-file在需要下列之一时问题
场景描述:1.kettle执行报表跑批任务2.需要给kettle增加依赖,等核心跑完后在执行报表的加工逻辑核心代码如下:declareV_BEGIN VARCHAR2(30);V_ENDVARCHAR2(30);V_DATE_EMP VARCHAR2(30);V_FLAG VARCHAR2(30);V_FLAG1 VARCHAR2(30);V_DAY VARCHAR2(30);beginV_BEG
场景描述:
1.kettle执行报表跑批任务
2.需要给kettle增加依赖,等核心跑完后在执行报表的加工逻辑
核心代码如下:
declare
V_BEGIN VARCHAR2(30);
V_END VARCHAR2(30);
V_DATE_EMP VARCHAR2(30);
V_FLAG VARCHAR2(30);
V_FLAG1 VARCHAR2(30);
V_DAY VARCHAR2(30);
begin
V_BEGIN := 'N';
V_END := 'Y';
V_DATE_EMP := V_BEGIN;
V_FLAG := '';
V_FLAG1 := '';
V_DAY := TO_CHAR(SYSDATE-1, 'YYYY-MM-DD');
while V_DATE_EMP <> V_END
loop
-- 判断日期
DBMS_OUTPUT.put_line('当日'||to_char(sysdate-1,'yyyy-mm-dd hh24:mi:ss')||'跑批未完成!');
select max(eod_dt) into V_FLAG from glloans.EOD_TASK_INFO ;
select EOD_STS INTO V_FLAG1 from glloans.EOD_TASK_INFO where EOD_DT = V_DAY ;
-- 跑批完成标志
IF V_FLAG1 = 'FIN' and V_FLAG = V_DAY THEN
-- 切换标志为'Y'完成
SELECT 'Y' INTO V_DATE_EMP FROM DUAL;
DBMS_OUTPUT.put_line('当日'||to_char(sysdate-1,'yyyy-mm-dd hh24:mi:ss')||'跑批完成!');
END IF;
DBMS_LOCK.sleep(60);
end loop;
end;
问题1:
2021/02/07 09:28:04 - Spoon - 正在开始任务…
2021/02/07 09:28:04 - report_all - 开始执行任务
2021/02/07 09:28:04 - report_all - 开始项[SQL]
2021/02/07 09:28:06 - SQL - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : 执行该作业项时发生了一个错误:
2021/02/07 09:28:06 - SQL - Couldn’t execute SQL: DECLARE
2021/02/07 09:28:06 - SQL -
2021/02/07 09:28:06 - SQL - TEST string
2021/02/07 09:28:06 - SQL -
2021/02/07 09:28:06 - SQL - ORA-06550: 第 3 行, 第 14 列:
2021/02/07 09:28:06 - SQL - PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
2021/02/07 09:28:06 - SQL - := . ( @ % ; not
2021/02/07 09:28:06 - SQL - null range default character:
2021/02/07 09:28:04 - Spoon - 正在开始任务...
2021/02/07 09:28:04 - report_all - 开始执行任务
2021/02/07 09:28:04 - report_all - 开始项[SQL]
2021/02/07 09:28:06 - SQL - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : 执行该作业项时发生了一个错误:
2021/02/07 09:28:06 - SQL - Couldn't execute SQL: DECLARE
2021/02/07 09:28:06 - SQL -
2021/02/07 09:28:06 - SQL - TEST string
2021/02/07 09:28:06 - SQL -
2021/02/07 09:28:06 - SQL - ORA-06550: 第 3 行, 第 14 列:
2021/02/07 09:28:06 - SQL - PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
2021/02/07 09:28:06 - SQL - := . ( @ % ; not
2021/02/07 09:28:06 - SQL - null range default character
解决方法:
先将代码放在oracle中执行,待执行成功后,确保不是sql语法问题,这个时候就可以确定是格式的问题
了,可以将代码块放到文本编辑器中,调整格式为unix格式
问题2:
PLS-00201: 必须声明标识符 ‘DBMS_LOCK’
2021/02/07 10:10:09 - batch_info.0 - ORA-06550: 第 28 行, 第 9 列:
2021/02/07 10:10:09 - batch_info.0 - PLS-00201: 必须声明标识符 'DBMS_LOCK'
2021/02/07 10:10:09 - batch_info.0 - ORA-06550: 第 28 行, 第 9 列:
2021/02/07 10:10:09 - batch_info.0 - PL/SQL: Statement ignored
2021/02/07 10:10:09 - batch_info.0 -
2021/02/07 10:10:09 - batch_info.0 -
2021/02/07 10:10:09 - batch_info.0 - at org.pentaho.di.core.database.Database.execStatement(Database.java:1561)
2021/02/07 10:10:09 - batch_info.0 - at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:209)
2021/02/07 10:10:09 - batch_info.0 - ... 2 more
2021/02/07 10:10:09 - batch_info.0 - Caused by: java.sql.SQLException: ORA-06550: 第 28 行, 第 9 列:
2021/02/07 10:10:09 - batch_info.0 - PLS-00201: 必须声明标识符 'DBMS_LOCK'
2021/02/07 10:10:09 - batch_info.0 - ORA-06550: 第 28 行, 第 9 列:
2021/02/07 10:10:09 - batch_info.0 - PL/SQL: Statement ignored
2021/02/07 10:10:09 - batch_info.0 -
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
2021/02/07 10:10:09 - batch_info.0 - at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
2021/02/07 10:10:09 - batch_info.0 - at org.pentaho.di.core.database.Database.execStatement(Database.java:1535)
2021/02/07 10:10:09 - batch_info.0 - ... 3 more
这个问题是因为dbms_lock是有权限的,需要给执行的用户赋予包的权限:
grant execute on dbms_lock to user;
ok,代码执行成功,kettle增加核心跑批依赖功能实现!
更多推荐
所有评论(0)