insert into bi.bm_zfzjkb
select * from post_test2.bm_zfzjkb where rcsj>=to_date('20030101','yyyymmdd') and rcsj<=to_date('20030131','yyyymmdd');需要有对这两个表进行这种操作的权限。
execute immediate 'alter session set NLS_DATE_FORMAT=''YYYYMMDD''';访问另外一个数据库需要先创建数据库连接
创建语法:
create database link linkname
connect to user
identified by usrpwd
using 'demona';其中
--demona为用net8 easy config创建的连接字符串
目的方数据库的init.ora文件中的global_names设为false
重新启动数据库设置才能生效
或者用sys用户执行
alter system set global_names=false;使用方法:select * from tbname@linkname where ...;
select * from post_test2.bm_zfzjkb where rcsj>=to_date('20030101','yyyymmdd') and rcsj<=to_date('20030131','yyyymmdd');需要有对这两个表进行这种操作的权限。
execute immediate 'alter session set NLS_DATE_FORMAT=''YYYYMMDD''';访问另外一个数据库需要先创建数据库连接
创建语法:
create database link linkname
connect to user
identified by usrpwd
using 'demona';其中
--demona为用net8 easy config创建的连接字符串
目的方数据库的init.ora文件中的global_names设为false
重新启动数据库设置才能生效
或者用sys用户执行
alter system set global_names=false;使用方法:select * from tbname@linkname where ...;
第二个问题我照你说的修改后系统会报“PLS-00103:出现符号'immedidate'在需要下列之一时:=(@%;符号”:=在“immediate”继续之前已插入。
1。
显示的赋权限
connect bi/..
grant insert on tbname to username;
connect post_test2/..
grant select on tbname to username;2。
你的数据库8.0.5的话,这条语句不行
需要用dbms_sql包把你的语句贴出来看看
我照你的用法试了下,果然是权限不够的问题,现在可以了,谢谢。
2.
如果是数据库的版本问题那就没办法了,你的说dbms_sql是怎么用的,在哪里能下载?我的存储过程语句很简单,就是:
create or replace procedure test is
begin
execute immediate 'alter session set NLS_DATE_FORMAT=''YYYYMMDD''';
insert into bi.bm_zfzjkb
select * from bm_zfzjkb where rcsj>='20030101' and rcsj<='20031231';
insert into bi.bb_ckpjxx
select * from post_test2.bb_ckpjxx where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ckyjxx
select * from post_test2.bb_ckyjxx where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ckyjxx_back
select * from post_test2.bb_ckyjxx_back where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ctqkjl
select * from post_test2.bb_ctqkjl where yjrcsj>='20030101' and yjrcsj<='20031231';
insert into bi.bh_ctqkjl
select * from post_test2.bh_ctqkjl where yjrcsj>='20030101' and yjrcsj<='20031231';
insert into bi.bb_rqzyb
select * from post_test2.bb_rqzyb where ffsj>='20030101' and ffsj<='20031231';end test;
begin
.....
exception
when
......
end;
)
create or replace procedure test is
sqlcmd varchar2(100);
begin
nSQL :=DBMS_SQL.OPEN_CURSOR;
SQLCMD :='alter session set NLS_DATE_FORMAT=''YYYYMMDD''';
DBMS_SQL.PARSE(nSQL,SQLCMD,DBMS_SQL.V7);
ret :=DBMS_SQL.EXECUTE(nSQL);
insert into bi.bm_zfzjkb
select * from bm_zfzjkb where rcsj>='20030101' and rcsj<='20031231';
insert into bi.bb_ckpjxx
select * from post_test2.bb_ckpjxx where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ckyjxx
select * from post_test2.bb_ckyjxx where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ckyjxx_back
select * from post_test2.bb_ckyjxx_back where yjsjsj>='20030101' and yjsjsj<='20031231';
insert into bi.bb_ctqkjl
select * from post_test2.bb_ctqkjl where yjrcsj>='20030101' and yjrcsj<='20031231';
insert into bi.bh_ctqkjl
select * from post_test2.bh_ctqkjl where yjrcsj>='20030101' and yjrcsj<='20031231';
insert into bi.bb_rqzyb
select * from post_test2.bb_rqzyb where ffsj>='20030101' and ffsj<='20031231';end test;
nSQL INT;