SQL> ed
Wrote file afiedt.buf 1 CREATE OR REPLACE PROCEDURE STEL_PROC(error_table in varchar2) is
2 stmt varchar2(1500);
3 begin
4 stmt:='create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as ' ||
5 'select cust_id, pic,uin, bill_acct_no, month_yyyymm, use_code, charge_type, '||
6 'subtype_code, discount_adj_ind, amount, cust_classification '||
7 'from MOCHA.V_EARNED_REVENUE ' ||
8 'where charge_type =4 and month_yyyymm = to_char(add_months(sysdate, -1), ''YYYYMM'') '||
9 'and use_code=1 ';
10 SP_execSQL001(stmt, error_table );
11* end;
SQL> /Procedure created.SQL> execute stel_proc('abc');
create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as select
cust_id, pic,uin, bi
ll_acct_no, month_yyyymm, use_code, charge_type, subtype_code, discount_adj_ind,
amount, cust_classi
fication from MOCHA.V_EARNED_REVENUE where charge_type =4 and month_yyyymm =
to_char(add_months(sysd
ate, -1), 'YYYYMM') and use_code=1
BEGIN stel_proc('abc'); END;*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "STEL.SP_EXECSQL001", line 48
ORA-06512: at "STEL.STEL_PROC", line 10
ORA-06512: at line 1
Wrote file afiedt.buf 1 CREATE OR REPLACE PROCEDURE STEL_PROC(error_table in varchar2) is
2 stmt varchar2(1500);
3 begin
4 stmt:='create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as ' ||
5 'select cust_id, pic,uin, bill_acct_no, month_yyyymm, use_code, charge_type, '||
6 'subtype_code, discount_adj_ind, amount, cust_classification '||
7 'from MOCHA.V_EARNED_REVENUE ' ||
8 'where charge_type =4 and month_yyyymm = to_char(add_months(sysdate, -1), ''YYYYMM'') '||
9 'and use_code=1 ';
10 SP_execSQL001(stmt, error_table );
11* end;
SQL> /Procedure created.SQL> execute stel_proc('abc');
create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as select
cust_id, pic,uin, bi
ll_acct_no, month_yyyymm, use_code, charge_type, subtype_code, discount_adj_ind,
amount, cust_classi
fication from MOCHA.V_EARNED_REVENUE where charge_type =4 and month_yyyymm =
to_char(add_months(sysd
ate, -1), 'YYYYMM') and use_code=1
BEGIN stel_proc('abc'); END;*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "STEL.SP_EXECSQL001", line 48
ORA-06512: at "STEL.STEL_PROC", line 10
ORA-06512: at line 1
SQL> ed
Wrote file afiedt.buf 1 declare
2 stmt varchar2(1500);
3 begin
4 stmt:='create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as ' ||
5 'select cust_id, pic,uin, bill_acct_no, month_yyyymm, use_code, charge_type, '||
6 'subtype_code, discount_adj_ind, amount, cust_classification '||
7 'from MOCHA.V_EARNED_REVENUE ' ||
8 'where charge_type =4 and month_yyyymm = to_char(add_months(sysdate, -1), ''YYYYMM'') '||
9 'and use_code=1 ';
10 SP_execSQL001(stmt, 'abc');
11* end;SQL> /
create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as select
cust_id, pic,uin, bill_acct_no, month_yyyymm, use_code, charge_type, subtype_code, discount_adj_ind,
amount, cust_classification
from MOCHA.V_EARNED_REVENUE where charge_type =4 and
month_yyyymm =to_char(add_months(sysdate, -1), 'YYYYMM') and use_code=1
PL/SQL procedure successfully completed.彻底faint,有谁遇到这样的问题吗?
2 stmt varchar2(1500);
3 begin
4 stmt:='create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as ' ||
5 'select cust_id, pic,uin, bill_acct_no, month_yyyymm, use_code, charge_type, '||
6 'subtype_code, discount_adj_ind, amount, cust_classification '||
7 'from MOCHA.V_EARNED_REVENUE ' ||
8 'where charge_type =4 and month_yyyymm = to_char(add_months(sysdate, -1), ''YYYYMM'') '||
9 'and use_code=1 ';
10 execute immediate stmt;
11* end;
SQL> /Procedure created.SQL> execute stel_proc('abc');
这样呢?还有看你执行的帐户有没有CREATE TABLE 的权限。
9i里面,如果要在pl/sql下访问另一用户的表,则需要在对应用户下显式授权>conn mocha
>grant select on v_earned_revenue to stel;然后再尝试
create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as select * from tabname;
与下面这条语句相同作用:
create GLOBAL TEMPORARY TABLE RSN0261M_base01 ON COMMIT PRESERVE ROWS as select * from tabname where 1=2;本人已重复测试了,只能创建临时表后,再作一个insert动作
不一样,加了这个priv就好了,多谢大家
请 iwom(鲜橙)把你写好的能执行通过的过程在发布一下好吗??