存储过程里面能包含多个语句吧?
我代码如下create table tmp_ny_cfjc_1 as
select distinct serv_id
from acct.acct_item_owe@dblink_comm_qry_135
where state = '5JB'
and billing_cycle_id = '111' || to_char(sysdate, 'mm') - 1;--OK,17mindelete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');--OK
commit;delete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');
commit;--OKend evd_go;每一句都是可以执行成功的,
创建存储过程也成功,但execute的时候提示错误
ORA-06550: line 2, column 7:
PLS-00905: object INTERFACE.EVD_GO is invalid
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored初学的菜鸟,请大家帮忙指正。
我代码如下create table tmp_ny_cfjc_1 as
select distinct serv_id
from acct.acct_item_owe@dblink_comm_qry_135
where state = '5JB'
and billing_cycle_id = '111' || to_char(sysdate, 'mm') - 1;--OK,17mindelete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');--OK
commit;delete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');
commit;--OKend evd_go;每一句都是可以执行成功的,
创建存储过程也成功,但execute的时候提示错误
ORA-06550: line 2, column 7:
PLS-00905: object INTERFACE.EVD_GO is invalid
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored初学的菜鸟,请大家帮忙指正。
解决方案 »
- 怎么使用C#读取数据库视图user_views的text字段?数据类型为long,现在读取到C#后显示空
- 同机子跨数据库导入的问题。
- linux下的oracle11g,进不去em,求教
- oracle to_date()函数
- oracle 透明网关 取sybase数据库时报的错误
- Toad Quest Code Tester For Oracle
- 请问可以用exp将dmp文件导入指定的表空间中吗?
- 如何创建用户和该用户能够操作的表空间!(急,在线等!)
- LINUX sqlplus怎么不能访问WINDOWS 2000 SERVER ORACLE服务器?
- oracle导入.dmp的activiti数据出现问题
- 关于oracle数据库连接
- oracle 10g, 自定义类型报错求指教
sqlstr:='create table tmp_ny_cfjc_1 as
select distinct serv_id
from acct.acct_item_owe@dblink_comm_qry_135
where state = '5JB'
and billing_cycle_id = '111' || to_char(sysdate, 'mm') - 1';
execute immediate sqlstr;
具体的动态SQL 自己拼
begindrop table tmp_ny_cfjc_1;create table tmp_ny_cfjc_1 as
select distinct serv_id
from acct.acct_item_owe@dblink_comm_qry_135
where state = '5JB'
and billing_cycle_id = '111' || to_char(sysdate, 'mm') - 1;--OKdelete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');--OK
commit;delete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');
commit;--OKend evd_go;
都写在存储过程的里面吗?
凡是drop、create这些,都要变成一个字符串然后再用execute命令来操作是吗?
execute immediate 'DDL语句'
能给一个完整的写法么?
麻烦各位了
能给一个完整的写法么?
麻烦各位了
create or replace procedure evd_go as
begindeclare str1 string,str2 string;str1:='drop table tmp_ny_cfjc_1';
execute immediate str1;str2:='create table tmp_ny_cfjc_1 as
select distinct serv_id
from acct.acct_item_owe@dblink_comm_qry_135
where state = '5JB'
and billing_cycle_id = '111' || to_char(sysdate, 'mm') - 1';
execute immediate str2;delete from dun_gencflist_1112 where serv_id
in (select serv_id from tmp_ny_cfjc_1);
commit;delete from dun_gencflist_1112 where serv_id
in (
select serv_id from acct.a_owe_special_serv@dblink_comm_qry_135
where exp_date>=sysdate
and owe_business_type_id='1');
commit;end;这样对么?(现在没办法测试,只好先发上来问问,请别拍我砖)