CREATE OR REPLACE PROCEDURE XIANGDAN.DAORUFROMSHEQU
AS
BEGIN
drop table shequ.COMPLETED_DETAIL;
commit;
create table shequ.COMPLETED_DETAIL as select * from ODS.COMPLETED_DETAIL@ssqq;
commit;
END
这回变成两个错误提示了:4 6 PLS-00103: 出现符号 "DROP"在需要下列之一时: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe符号 "lock在 "DROP" 继续之前已插入。4 39 PLS-00103: 出现符号 ";"在需要下列之一时: . , @ in <an identifier> <a double-quoted delimited-identifier> partition subpartition
AS
BEGIN
drop table shequ.COMPLETED_DETAIL;
commit;
create table shequ.COMPLETED_DETAIL as select * from ODS.COMPLETED_DETAIL@ssqq;
commit;
END
这回变成两个错误提示了:4 6 PLS-00103: 出现符号 "DROP"在需要下列之一时: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe符号 "lock在 "DROP" 继续之前已插入。4 39 PLS-00103: 出现符号 ";"在需要下列之一时: . , @ in <an identifier> <a double-quoted delimited-identifier> partition subpartition
解决方案 »
- ResultSet获取带精度的Number类型时 得到的是空值?求解
- 求一个字符串归并的sql写法
- oracle客户端突然丢失文件怎么回事?
- 怎样判断数据库下有关联关系的表?
- 如何循环动态sql的结果集并拼接字段的值为字符串,这个sql中union了好几个表
- 再加分:大家有没有遇到过这种怪问题?
- 谁能给我一份常用的sql语句大全和存储过程的资料呢??
- 谁用过PB6.5的ORACLE 7.3连ORACLE8i吗?
- help me 连接oracle不上
- 物化视图自动刷新问题(refresh fast on commit)
- 畫面輸入一個特殊的繁體字符到提交到oracle10g后天確變成了"峯"怎么回事情﹖
- 弟弟妹妹们,大姐求助,我从SQLSERVER转到ORACLE,这段代码在ORACLE里如何写?谢谢!
AS
BEGIN
drop table shequ.COMPLETED_DETAIL;
commit;
create table shequ.COMPLETED_DETAIL as select * from ODS.COMPLETED_DETAIL@ssqq;
commit;
END
过程中drop table需要使用sys.dbms_sql package比如CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/
create or replace procedure droptable (table_name varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'DROP TABLE ' || table_name, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ create procedure anyddl (s1 varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, s1, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/ SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');
CREATE OR REPLACE PROCEDURE xiangdan.DAORUFROMSHEQU
AS
cursor1 integer;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'drop table shequ.DIM_97_AREA', dbms_sql.v7);
dbms_sql.parse(cursor1, 'create table shequ.DIM_97_AREA as select * from ODS.DIM_97_AREA@ssqq', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
END;
但是,execute DAORUFROMSHEQU
提示
SQL> execute DAORUFROMSHEQU;
BEGIN DAORUFROMSHEQU; END;*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
ORA-06512: 在"SYS.DBMS_SYS_SQL", line 826
ORA-06512: 在"SYS.DBMS_SQL", line 32
ORA-06512: 在"XIANGDAN.DAORUFROMSHEQU", line 6
ORA-06512: 在line 1
可是我把
drop table shequ.DIM_97_AREA
create table shequ.DIM_97_AREA as select * from ODS.DIM_97_AREA@ssqq
单独执行,却没有问题