table_name: 这列的值为表名,带上用户名 system.test 就找不出记录来了。select count(*) into cou from all_tables where table_name=upper(tab_name) AND OWNER = upper(username);参数类型varchar最好写成varchar2
create or replace procedure tes (user_name in varchar2, tab_name in varchar2) as cou int;v_sql varchar2(100);begin select count(*) into cou from all_tables where owner = (user_name) AND table_name = upper(tab_name); if cou = 1 then prinf cou v_sql := 'drop table '|| user_name || '.' || tab_name; execute immediate v_sql; end if; end; /exec tes('system', 'test')
试试下面的: create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2) as cou number; v_sql varchar2(100); begin select count(*) into cou from all_tables where table_name=upper(tab_name) and OWNER=upper(tab_owner); if cou=1 then v_sql:='drop table '|| tab_name; execute immediate v_sql; end if; end; exec tes('test','system'); exit
conn sys/change_on_install@oemrep as sysdba create or replace procedure tes(tab_name in varchar, tabspace_name in varchar) as cou int; v_sql varchar(100); begin select count(*) into cou from all_tables where table_name=upper(tab_name) and owner=upper(tabspace_name) if cou=1 then prinf cou v_sql:='drop table '||tab_name; execute immediate v_sql; end if; end; / exec tes('test','system') exit这样写了 可是编译还是有问题啊
现在是这样的情况 c:\d.sql文件内容是 conn sys/change_on_install@lujiaqi as sysdba create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2); as cou number; v_sql varchar2(100); begin select count(*) into cou from all_tables where table_name=upper(tab_name) and OWNER=upper(tab_owner); if cou=1 then v_sql:='drop table '|| tab_name; execute immediate v_sql; end if; end; / exec tes('test','system'); exit 接着这么执行C:\>sqlplus /nolog <c:\d.sqlSQL*Plus: Release 9.0.1.0.1 - Production on 星期三 12月 29 09:43:33 2004(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL> 已连接。 SQL> 2 3 4 5 6 7 8 9 10 11 12 13 警告: 创建的过程带有编译错误。SQL> BEGIN tes('test','system'); END; * ERROR 位于第 1 行: ORA-06550: 第 1 行, 第 7 列: PLS-00905: 对象 SYS.TES 无效 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored SQL> 从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production中断开 不知道现在错在哪里?
create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2); 创建过程的时候 如果里面只有一个参数的话 好象就没有编译警告了 现在好象必须有两个参数,就有这样的编译警告的问题了
create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2); 这句最后的分号去掉
1 create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2) 2 as 3 cou number; 4 v_sql varchar2(100); 5 begin 6 select count(*) into cou from all_tables 7 where table_name=upper(tab_name) and OWNER=upper(tab_owner); 8 if cou=1 then 9 v_sql:='drop table '|| tab_name; 10 dbms_output.put_line(v_sql); 11 end if; 12* end; SQL> /过程已创建。我用的是805,不能运行exec immediate故改成了dbms_output输出SQL命令
as cou int;v_sql varchar2(100);begin
select count(*) into cou
from all_tables
where owner = (user_name) AND table_name = upper(tab_name);
if cou = 1 then
prinf cou
v_sql := 'drop table '|| user_name || '.' || tab_name;
execute immediate v_sql;
end if;
end;
/exec tes('system', 'test')
create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2)
as
cou number;
v_sql varchar2(100);
begin
select count(*) into cou from all_tables
where table_name=upper(tab_name) and OWNER=upper(tab_owner);
if cou=1 then
v_sql:='drop table '|| tab_name;
execute immediate v_sql;
end if;
end;
exec tes('test','system');
exit
create or replace procedure tes(tab_name in varchar, tabspace_name in varchar) as cou int;
v_sql varchar(100);
begin
select count(*) into cou from all_tables where table_name=upper(tab_name) and owner=upper(tabspace_name)
if cou=1 then
prinf cou
v_sql:='drop table '||tab_name;
execute immediate v_sql;
end if;
end;
/
exec tes('test','system')
exit这样写了 可是编译还是有问题啊
警告: 创建的过程带有编译错误。SQL> SQL> BEGIN tes('system', 'test'); END; *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 SYS.TES 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
我正在学习oracle有易找老师 帮助我吧
c:\d.sql文件内容是
conn sys/change_on_install@lujiaqi as sysdba
create or replace procedure tes(tab_name in varchar2,tab_owner in varchar2);
as
cou number;
v_sql varchar2(100);
begin
select count(*) into cou from all_tables
where table_name=upper(tab_name) and OWNER=upper(tab_owner);
if cou=1 then
v_sql:='drop table '|| tab_name;
execute immediate v_sql;
end if;
end;
/
exec tes('test','system');
exit
接着这么执行C:\>sqlplus /nolog <c:\d.sqlSQL*Plus: Release 9.0.1.0.1 - Production on 星期三 12月 29 09:43:33 2004(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL> 已连接。
SQL> 2 3 4 5 6 7 8 9 10 11 12 13
警告: 创建的过程带有编译错误。SQL> BEGIN tes('test','system'); END; *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 SYS.TES 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
SQL> 从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开
不知道现在错在哪里?
创建过程的时候 如果里面只有一个参数的话 好象就没有编译警告了
现在好象必须有两个参数,就有这样的编译警告的问题了
这句最后的分号去掉
2 as
3 cou number;
4 v_sql varchar2(100);
5 begin
6 select count(*) into cou from all_tables
7 where table_name=upper(tab_name) and OWNER=upper(tab_owner);
8 if cou=1 then
9 v_sql:='drop table '|| tab_name;
10 dbms_output.put_line(v_sql);
11 end if;
12* end;
SQL> /过程已创建。我用的是805,不能运行exec immediate故改成了dbms_output输出SQL命令