try this:
v_sSeqName := 'DEPARTMENT_SEQ';
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_sGetSeq := 'select '||v_sSeqName||'.nextval from dual';
..........................
v_sSeqName := 'DEPARTMENT_SEQ';
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_sGetSeq := 'select '||v_sSeqName||'.nextval from dual';
..........................
v_sSeqName := 'DEPARTMENT_SEQ';
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_sGetSeq := 'select '||v_sSeqName||'.nextval from dual';
..........................
v_sGetSeq := 'select :SeqName.nextval from dual';
这样的变量绑定有问题。注意组合的方式
改过后,得到的结果还是 nullto farspeed
你能告诉我怎样绑定吗?
我定义 v_nIDRet 为 long 型!
IS
procedure GetSeqByTableName(
p_sTableName in varchar2,
p_nSeq in out integer)
IS
v_CursorID integer;
v_sGetSeq varchar2(100);
v_sSeqName varchar2(50);
v_nIDRet long;
v_nID INTEGER;v_nExecute integer;
v_dataChunk varchar2(254);
v_RetLenth integer;BEGIN -- v_sSeqName := p_sTableName || '_SEQ';
v_sSeqName := 'DEPARTMENT_SEQ';
v_CursorID := DBMS_SQL.OPEN_CURSOR;
-- v_sGetSeq := 'select :SeqName.nextval from dual';
v_sGetSeq := 'select '||v_sSeqName||'.nextval from dual';
DBMS_SQL.PARSE(v_CursorID,v_sGetSeq,DBMS_SQL.v7);
-- DBMS_SQL.BIND_VARIABLE(v_CursorID,':SeqName',v_sSeqName);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_nIDRet,20);
v_nExecute := DBMS_SQL.EXECUTE(v_CursorID);
dbms_output.put_line('v_nExecute = ' || v_nExecute);
DBMS_SQL.COLUMN_VALUE(v_CursorID,1,v_nIDRet);
DBMS_OUTPUT.PUT_LINE('v_nIDRet = ' || v_nIDRet);
END;END COMMIT_PACKAGE;sql*plus中的执行结果如下:SQL> DECLARE
2 v_sTable varchar2(20) := 'T_DEPARTMEN';
3 v_nReturn integer;
4 begin
5 COMMIT_PACKAGE.GetSeqByTableName(v_sTable,v_nReturn);
6 end;
7 /
v_nExecute = 0
v_nIDRet =PL/SQL 过程已成功完成
你换别的类型的试试
----------------------------
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_nIDRet);
v_nExecute := DBMS_SQL.EXECUTE(v_CursorID);
jlandzpa := DBMS_SQL.FETCH_ROWS(v_CursorID) ;
---------------------------------13:11:09 SQL> exec COMMIT_PACKAGE
v_nExecute = 1
v_nIDRet = 3PL/SQL 过程已成功完成。已用时间: 00: 00: 00.20
--added by zergwangcn begin
v_nSeqNum := 0;
--added end
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_sGetSeq := 'select :SeqName.nextval into :SeqNum from dual';
v_nIDRet := 0;
DBMS_SQL.PARSE(v_CursorID,v_sGetSeq,DBMS_SQL.v7);
DBMS_SQL.BIND_VARIABLE(v_CursorID,':SeqName',v_sSeqName);
--DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_nIDRet,20);
--added by zergwangcn begin
DBMS_SQL.BIND_VARIABLE(v_CursorID,':SeqNum',v_nSeqNum);
--added end
v_nExecute := DBMS_SQL.EXECUTE(v_CursorID);
这样就可以了。
比如换成 integer
错误信息:
SQL> DECLARE
2 v_sTable varchar2(20) := 'T_DEPARTMEN';
3 v_nReturn integer;
4 begin
5 COMMIT_PACKAGE.GetSeqByTableName(v_sTable,v_nReturn);
6 end;
7 /
v_nExecute = 0
DECLARE
*
ERROR 位于第 1 行:
ORA-06562: 输出自变量的类型必须与列或赋值变量的类型匹配
ORA-06512: 在"SYS.DBMS_SYS_SQL", line 1099
ORA-06512: 在"SYS.DBMS_SQL", line 333
ORA-06512: 在"ASTUSER.COMMIT_PACKAGE", line 29
ORA-06512: 在line 5
13:14:55 2 as
13:14:55 3 v_CursorID integer;
13:14:55 4 v_sGetSeq varchar2(100);
13:14:55 5 v_sSeqName varchar2(50);
13:14:55 6 v_nIDRet number;
13:14:55 7 v_nID INTEGER;
13:14:55 8 v_nExecute integer;
13:14:55 9 v_dataChunk varchar2(254);
13:14:55 10 v_RetLenth integer;
13:14:55 11 BEGIN
13:14:55 12 v_sSeqName := 'DEPARTMENT_SEQ';
13:14:55 13 v_CursorID := DBMS_SQL.OPEN_CURSOR;
13:14:55 14 v_sGetSeq := 'select '||v_sSeqName||'.nextval from dual';
13:14:55 15
13:14:55 16 DBMS_SQL.PARSE(v_CursorID,v_sGetSeq,DBMS_SQL.v7);
13:14:55 17
13:14:55 18 DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_nIDRet);
13:14:55 19 v_nExecute := DBMS_SQL.EXECUTE(v_CursorID);
13:14:55 20 v_nExecute := DBMS_SQL.FETCH_ROWS(v_CursorID) ;
13:14:55 21 dbms_output.put_line('v_nExecute = ' || v_nExecute);
13:14:55 22
13:14:55 23 DBMS_SQL.COLUMN_VALUE(v_CursorID,1,v_nIDRet);
13:14:55 24 DBMS_OUTPUT.PUT_LINE('v_nIDRet = ' || v_nIDRet);
13:14:55 25 END;
13:14:56 26 /过程已创建。已用时间: 00: 00: 00.50
13:14:56 SQL> exec COMMIT_PACKAGE
v_nExecute = 1
v_nIDRet = 4PL/SQL 过程已成功完成。已用时间: 00: 00: 00.40
13:15:00 SQL>
jlandzpa(ORA-00600) 的方法我试了很成功
神游 的方法 还是有点问题,提示赋值变量不存在,我不知道什么原因,按理我认为应该成功才对