在存储过程中,使用动态SQL语句建临时表,建完后在PL/SQL中无法查看到该表?
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';--create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
end pro_temp;
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';--create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
end pro_temp;
解决方案 »
- oracle11g数据库泵expdp与impdp导出导入问题!40分
- 哪位大哥,能帮忙看SQL问题?
- 监听程序当前无法识别连接描述符中请求的服务
- JAVABEAN连接数据库的问题,请各位高手帮帮忙!
- 为什么在sql*plus中删除数据到空的表,用JAVA的resultSet还能打开?
- 紧急招聘顶尖互联网公司DBA系列职位-TK China soft headhunting
- oracle9i连接到oracle8i问题
- Oracle复杂数据类型
- 请问如何启动OracleOraHome81ManagementServer
- 命名块的使用例
- 高手帮帮忙.谢谢
- oracle能不能用select 实现一个数字字段的加减,现在不记得了
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';--create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;--insert data
execute immediate 'insert into t_temp values('''||v_col1||''','''||v_col2||''')';execute immediate 'select col1 from t_temp' into v_num;
dbms_output.put_line(v_num);
execute immediate 'delete from t_temp';
commit;
execute immediate 'drop table t_temp';
end pro_temp;测试:15:23:54 SQL> set serveroutput on
15:24:01 SQL> exec pro_temp('11','22');
11PL/SQL 过程已成功完成。已用时间: 00: 00: 00.79
15:24:08 SQL> desc t_temp;
ERROR:
ORA-04043: 对象 t_temp 不存在