存储过程 编译错误:PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/liuao107329/article/details/54135348

必须使用动态的语句, DDL 语句,在存储过程中, 需要改为动态语句  :

execute immediate 'sql语句' ; 

我的是因为执行了  

truncate table t_pvbdp_comprehensiv_statistic
换成  
 execute immediate 'truncate table t_pvbdp_comprehensiv_statistic' ; 

就可以了



DDL 语句指 : 

DDL     
create table 创建表   
alter table  修改表  
drop table 删除表  
truncate table 删除表中所有行   
create index 创建索引  
drop index  删除索引
当执行DDL语句时,在每一条语句前后,oracle都将提交当前的事务。如果用户使用insert命令将记录插入到数据库后,执行了一条DDL语句(如create table),此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时,DDL语句会被自动提交,不能回滚。

PLS-00103:Encountered the symbol "IS" when expecting one of the following :=(@%;

09-20

大家好!rnOracle存储过程创建时提示:rnPLS-00103:Encountered the symbol "IS" when expecting one of the following : :=(@%;rn但是始终没有看到是何处缺少什么符号,现在代码中一共有5处"IS",请各位帮助看看是哪里不对,感谢!rn难道是第50行游标这部分不对?rn存储过程代码如下:rn[code=sql]CREATE OR REPLACErnPROCEDURE "SJJ_ORDER_QUERY" (rnrn V_JSON_STR IN CLOB,rn V_OUT_JSON OUT CLOB,rn --V_OU_TABLE OUT CLOB rn V_OU_CUR OUT sys_refcursorrn)rn ISrnrnV_NUM NUMBER;--根据ORDER_NO查询EOS_ORDER表关联EOS_ORDER_ITEM表的记录数rnV_PAGE_NO VARCHAR(100);rnV_FUN_ID VARCHAR(100);rnV_ORDER_NO VARCHAR(100);rnV_OUT_RETURN VARCHAR(100);rnV_OU_MSG VARCHAR(1000);rnV_TEMP V_OU_CUR%ROWTYPE; rnBEGINrn select JSON_VALUE(V_JSON_STR, '$.V_PAGE_NO') INTO V_PAGE_NO from dual;--从输入参数V_JSON_STR中读取V_PAGE_NOrn select JSON_VALUE(V_JSON_STR, '$.V_FUN_ID') INTO V_FUN_ID from dual;--从输入参数V_JSON_STR中读取V_FUN_IDrn select JSON_VALUE(V_JSON_STR, '$.V_ORDER_NO') INTO V_ORDER_NO from dual;--从输入参数V_JSON_STR中读取V_ORDER_NOrn rn IF V_PAGE_NO ='' OR V_PAGE_NO IS NULL OR V_PAGE_NO !='003' rn OR V_FUN_ID ='' OR V_FUN_ID IS NULL OR V_FUN_ID !='select'rn OR V_ORDER_NO ='' OR V_ORDER_NO IS NULLrn THENrn dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOBrn V_OUT_JSON := '"SATUS":"ERROR","MESSAGE":"V_PAGE_N不是003或者V_FUN_ID不是select或者V_ORDER_NO是空"';rn RETURN;rn rn ELSE rn dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOBrn SELECT COUNT(1) INTO V_NUM FROM V_EOS_ORDER Arn LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NOrn WHERE A.ORDER_NO = V_ORDER_NO;rn V_OUT_JSON := '"SATUS":"OK","MESSAGE":"成功"'; rn dbms_lob.append(V_OUT_JSON, ',');rn rn dbms_lob.append(V_OUT_JSON, '"total":');rn dbms_lob.append(V_OUT_JSON, to_char(V_NUM));--根据V_ORDER_NO查询出的记录数 rn dbms_lob.append(V_OUT_JSON, ','); rn rn dbms_lob.append(V_OUT_JSON, 'rn "footer": null,rn "columns": null,rn "frozenColumns": null,rn "AttachRemark": null,rn "Attach": null'); rn rn V_OU_CUR IS SELECT rn A.ORDER_NO,--EOS_ORDER表表订单号rn A.MEMBER_NAME,rn A.CONSIGNEE,rn A.CONSIGNEE_MOBILE,rn A.SHOP_NAME,rn A.CONSIGNEE_AREA,rn A.CONSIGNEE_ADDR,rn A.GOODS_AMOUNT,rn A.ORDER_CREATE_TIME,rn A.BRANCH_NAME,rn A.PMT_AMOUNT,rn B.NAME,rn B.BARCODE,rn B.NUMSrn FROM V_EOS_ORDER A LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NOrn WHERE A.ORDER_NO = V_ORDER_NO;rn OPEN V_OU_CUR;rn LOOPrn FETCH V_OU_CUR INTO V_TEMP;rn END LOOP;rn CLOSE V_OU_CUR;rn rn RETURN;rn END IF;rn rn EXCEPTIONrn WHEN OTHERS THENrn V_OUT_RETURN := 'ERROR';rn V_OU_MSG := '失败,原因是:' || SQLERRM;rn ROLLBACK;rn INSERT INTO SYS_ERROR_LOGrn (ROW_ID, PR_NAME, ERROR_DESC, INSDT)rn VALUESrn (SYS_ERROR_LOG_SEQ.NEXTVAL, 'SJJ_ORDER_QUERY', V_OU_MSG, SYSDATE);rn COMMIT;rnEND SJJ_ORDER_QUERY;[/code] 论坛

PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the

01-31

Connected to:rnOracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionrnWith the Partitioning, OLAP and Data Mining optionsrnrnSQL> Declare rn 2 Type ename_table_Type Is Varchar2(100) of emp.ename %Type;rn 3 ename_table ename_table_Type:=ename_table_type('&name1','&name2','&name3');rn 4 sql_stat varchar2(100):='update emp Set sal=sal*1.1'rn 5 ||'where Lower(ename)=Lower(:1)';rn 6 Beginrn 7 ForAll i In .. ename_table.Countrn 8 Execute Immediate sql_stat Using ename_table (i);rn 9 End;rn 10 /rnEnter value for name1: yaqrnEnter value for name2: yaqrnEnter value for name3: yaqrnold 3: ename_table ename_table_Type:=ename_table_type('&name1','&name2','&name3');rnnew 3: ename_table ename_table_Type:=ename_table_type('yaq','yaq','yaq');rnType ename_table_Type Is Varchar2(100) of emp.ename %Type;rn *rnERROR at line 2:rnORA-06550: line 2, column 26:rnPLS-00103: Encountered the symbol "VARCHAR2" when expecting one of thernfollowing:rn( array limited new private range record VARRAY_ char_basernnumber_base decimal date_base clob_base blob_base bfile_baserntable ref object fixed varying opaque sparsernThe symbol "range" was substituted for "VARCHAR2" to continue.rnORA-06550: line 2, column 40:rnPLS-00103: Encountered the symbol "OF" when expecting one of the following:rn. ( * % & - + ; / at mod remainder rem .. rn|| multisetrnORA-06550: line 7, column 13:rnPLS-00103: Encountered the symbol "." when expecting one of the following:rn( - + case mod new null rn avgrncount current max min prior sql stddev sum values varnrnrnSQL>请问上面的问题怎么解决呀??谢rnrnrnrnrn2.rnSQL> insert into emp(empno,ename,hiredate) values(9005,'Ppss','2008-5-28');rn insert into emp(empno,ename,hiredate) values(9005,'Ppss','2008-5-28')rn *rnERROR at line 1:rnORA-01438: value larger than specified precision allows for this columnrnHiredate --------- datern 这种情况下是用转换函数还是重新再用个时间类型呀?谢rn 论坛

没有更多推荐了,返回首页