这个存储过程编译通过了,但是执行时出错:create or replace procedure add_id2(var0 in varchar) as
var_id varchar2(100);
var2 number(10);
pvar2 varchar2(100);
type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1;OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad(to_char('||var2||'),10,'||'0'||') where rowid= '||var_id;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp;commit;
end add_id2;
SQL> exec add_id2('test2');
begin add_id2('test2'); end;
ORA-00904: "AAAMS1AAEAAAAIUAAA": 标识符无效
ORA-06512: 在 "NM_NXT1.ADD_ID2", line 22
ORA-06512: 在 line 2
其实我的第一行rowid是这样的:AAAMs1AAEAAAAIUAAA但是不知道为什么都变成了大写,我的过程里没转换啊!这个问题怎么解决啊,谢谢各位了先!
var_id varchar2(100);
var2 number(10);
pvar2 varchar2(100);
type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1;OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad(to_char('||var2||'),10,'||'0'||') where rowid= '||var_id;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp;commit;
end add_id2;
SQL> exec add_id2('test2');
begin add_id2('test2'); end;
ORA-00904: "AAAMS1AAEAAAAIUAAA": 标识符无效
ORA-06512: 在 "NM_NXT1.ADD_ID2", line 22
ORA-06512: 在 line 2
其实我的第一行rowid是这样的:AAAMs1AAEAAAAIUAAA但是不知道为什么都变成了大写,我的过程里没转换啊!这个问题怎么解决啊,谢谢各位了先!
var_id rowid; --类型定义错了
var2 number(10);
pvar2 varchar2(100);
type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1; OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad(to_char('||var2||'),10,'||'0'||') where rowid= '||var_id;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp; commit;
end add_id2;
SQL> exec add_id2('test2');
begin add_id4('test2'); end;
ORA-00904: "AAAMS1AAEAAAAIUAAA": 标识符无效
ORA-06512: 在 "NM_NXT1.ADD_ID2", line 20
ORA-06512: 在 line 2
照这个朋友改后:
create or replace procedure add_id4(var0 in varchar) as
var_id rowid; --类型定义错了
var2 number(10);
pvar2 varchar2(100);
type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1; OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad(to_char('||var2||'),10,'||'0'||') where rowid='||'''' || var_id || ''' ;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp; commit;
end add_id4;
报这个错:
SQL> exec add_id4('test2');
begin add_id4('test2'); end;
ORA-06550: 第 2 行, 第 7 列:
PLS-00905: 对象 NM_NXT1.ADD_ID4 无效
ORA-06550: 第 2 行, 第 7 列:
PL/SQL: Statement ignored
SQL> ed
已写入文件 afiedt.buf 1 declare
2 aa rowid;
3 i number;
4 begin
5 select rowid into aa from a where rownum=1;
6 select count(1) into i from a where rowid=aa;
7* end;
SQL> /PL/SQL 过程已成功完成。SQL>
1 declare
2 aa rowid;
3 i number;
4 begin
5 select rowid into aa from a where rownum=1;
6 select count(1) into i from a where rowid=aa;
7 dbms_output.put_line(i);
8* end;
SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on
SQL> /
1PL/SQL 过程已成功完成。SQL>
好像又给转成char了
create or replace procedure add_id4(var0 in varchar) as
var_id rowid; --类型定义错了
var2 number(10);
pvar2 varchar2(100); type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1;OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad('||var2||',10,'||'0'||') where rowid='|| var_id ;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp;commit;
end add_id4;报错!
SQL> exec add_id4('test2')
begin add_id4('test2'); end;
ORA-00904: "AAAMS1AAEAAAAIUAAA": 标识符无效
ORA-06512: 在 "NM_NXT1.ADD_ID4", line 20
ORA-06512: 在 line 2
谁知到这是为什么?
var_id rowid; --类型定义错了
var2 number(10);
pvar2 varchar2(100);
type cursor_t is ref cursor;
c_emp cursor_t ;
begin
var2:=1; OPEN c_emp FOR 'SELECT rowid FROM '||var0;
LOOP
FETCH c_emp
INTO var_id;
pvar2:='update '||var0||' set id = lpad(to_char('||var2||'),10,'||'0'||') where rowid= '||var_id;
execute immediate pvar2;
var2 := var2 +1;
EXIT WHEN c_emp%NOTFOUND;
END loop;
CLOSE c_emp; commit;
end add_id2;
row_id VARCHAR2(100);
l_sql VARCHAR2(200);
BEGIN
SELECT ROWIDTOCHAR(ROWID)
INTO row_id
FROM t2
WHERE ROWNUM=1;
l_sql := 'update t2 set qty=2 where rowid=chartorowid('''||row_id||''')';
EXECUTE IMMEDIATE(l_sql);
END;
谢谢!