大俠: ORA-20000,ORU-10027,ORA-06512錯誤,我用exec dbms_output.enable(20000)不行;用set serveroutput on size 1000000也不行;
該怎樣呢?謝謝!
該怎樣呢?謝謝!
解决方案 »
- RMAN-06023: no backup or copy of datafile 6 found to restore
- oracle如何查询非法字符的数据
- 求助一个关于连接超时的问题
- PLsql登陆错误:初始化错误 SQL*NET没有完全安装 OracleHomekey:software oracle
- 求statspack的脚本access.sql和access_report.sql
- Oracle9i与Tomcat的冲突问题
- Proc 调用.pc文件时,如何传入argv参数?
- Oracle RAC 11g
- 求一个存储过程
- 存储过程中的游标问题!急!
- oracle装成了,但问题仍很多!
- 各位前辈,高手,下面的两个存储过程怎么改啊??
我的oracle 10.2,Xp系统执行没问题!SQL> exec dbms_output.enable(20000);PL/SQL 过程已成功完成。SQL> set serveroutput on size 1000000
SQL>
Oracle 10.2SQL> exec dbms_output.enable(20000);PL/SQL procedure successfully completed.這樣子也OK,SQL運行時不行還是報錯
再增大点看看呢!
(
V_PID NUMBER,
V_TBCODE VARCHAR2
)RETURN VARCHAR2
AS
V_TBN VARCHAR2(60);
V_PIDFLD VARCHAR2(60);
V_SIDFLD VARCHAR2(60);
V_SEQFLD VARCHAR2(60);
SQLTXT VARCHAR2(400);
V_TPPID NUMBER;
V_TPSEQ NUMBER;
V_TPTXT VARCHAR2(60);
BEGIN
SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN
(SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A
ON 1=1;
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_PID ||') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1)
LOOP
V_TPTXT := '00'||'.'||TRIM(V_TPTXT);
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT '||V_PIDFLD||' AS PID, '||V_SEQFLD||' AS SEQ FROM '|| V_TBN ||' WHERE '|| V_SIDFLD ||'='||V_TPPID ||') A ON 1=1';
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
END LOOP;
RETURN V_TPTXT;
END;
幫我看看,這樣子會死循環嗎?
(
V_PID NUMBER,
V_TBCODE VARCHAR2
)RETURN VARCHAR2
AS
V_TBN VARCHAR2(60);
V_PIDFLD VARCHAR2(60);
V_SIDFLD VARCHAR2(60);
V_SEQFLD VARCHAR2(60);
SQLTXT VARCHAR2(400);
V_TPPID NUMBER;
V_TPSEQ NUMBER;
V_TPTXT VARCHAR2(60);
BEGIN
SELECT A.TBN,A.PID,A.SID,NVL(A.SEQ,'0') INTO V_TBN,V_PIDFLD,V_SIDFLD,V_SEQFLD FROM DUAL LEFT JOIN
(SELECT TBN,PID,SID, SEQ FROM SYS_TREETBS WHERE LOWER(TBCODE) = TRIM(LOWER(V_TBCODE))) A
ON 1=1;
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_PID ¦ ¦') A ON 1=1';
DBMS_OUTPUT.put_line(SQLTXT);
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
V_TPTXT := '00';
WHILE(V_TPPID <> -1)
LOOP
V_TPTXT := '00' ¦ ¦'.' ¦ ¦TRIM(V_TPTXT);
SQLTXT := 'SELECT NVL(A.PID,-1),A.SEQ FROM DUAL LEFT JOIN (SELECT ' ¦ ¦V_PIDFLD ¦ ¦' AS PID, ' ¦ ¦V_SEQFLD ¦ ¦' AS SEQ FROM ' ¦ ¦ V_TBN ¦ ¦' WHERE ' ¦ ¦ V_SIDFLD ¦ ¦'=' ¦ ¦V_TPPID ¦ ¦') A ON 1=1';
EXECUTE IMMEDIATE SQLTXT INTO V_TPPID,V_TPSEQ;
END LOOP;
RETURN V_TPTXT;
END;
好看些