各位前輩您好: 有兩個問題想請教各位前輩,謝謝! 1. 我寫了一個oracle的procedure.目的是要將某bom表的下階資料全列出 程序如下,然後我會傳入的參數objid格式像'36181,17799,35166'這樣的 CREATE OR REPLACE PROCEDURE BOM( objid IN varchar2 ) AS temp varchar2(1024) ; BEGIN --我在將傳入的參數objid的前後"'"拿掉 dbms_output.put_line(objid); temp := substr(objid,2,length(objid)-1); temp := substr(temp,1,length(temp)-1); dbms_output.put_line(temp); --使用指標指向在query中的data declare CURSOR cur_tree IS SELECT LEVEL as floor, object_id1,(select tdm_id from SM.ITEMS where object_id=object_id1) as pid, object_id2,(select tdm_id from SM.ITEMS where object_id=object_id2) as cid FROM SM.BOM_LINK START WITH object_id1 in (temp) CONNECT BY object_id1 = PRIOR object_id2; --宣告一個cur_tree的recordtype的bom_rec bom_rec cur_tree%ROWTYPE; begin open cur_tree; loop fetch cur_tree into bom_rec; EXIT WHEN cur_tree%NOTFOUND; dbms_output.put_line(bom_rec.floor||'~'||bom_rec.pid||'~'||bom_rec.cid||'~'); end loop; close cur_tree; end; END; 就這樣的一小段而己,為什麼會有下列的error呢..看了一二天了.還是對oracle 的error沒感覺,請各位前輩救一下 ORA-01722: 數字無效 ORA-06512: 在 "SM.BOM", line 17 ORA-06512: 在 "SM.BOM", line 26 ORA-06512: 在 line 7 2.請問.如果我要將我在上列的bom表出來後,暫存到某一個table裡去.要怎麼作呢?我有看了之前討論的內容作法如下: sqlstr := 'create global temporary table temptable(field1 number(12),field2 number(12),field3 number(12),field4 number(12)) on commit delete rows'; EXECUTE IMMEDIATE sqlstr; create是屬於ddl.要用EXECUTE IMMEDIATE 來執行 但是.我如果在EXECUTE IMMEDIATE sqlstr後,再下 insert into temptable select * from bom 這樣會出現物件不存在的msg,這樣應該要怎麼作才好呢? 謝謝您的回答

我剛又突然想到.如果把傳入的參數objid型態設成integer,然後只傳入一個36181,這樣可以run耶.那是不是表示在cursor那段的in (temp)這個條件的錯誤丫?可是這樣.我如果一次要查多個bom時(即我傳入的參數超過2個以上ex:'36181,17799')這樣就會出現「數字無效」喔?是這樣嗎? 自言自語中...

Hi demo,    1.是的，正如你所言，問題就是出在你所說的地方，建議將 Procedure 更改如下：

create or replace procedure bom
    (objid in varchar2)
is
    s       varchar2(1024);
    r       varchar2(255);
    p       integer;
    d       integer;
    cursor cur_tree(p_objid integer) is
        select  level as floor,
                object_id1,
                (select tdm_id from sm.items where object_id=object_id1) as pid,
                object_id2,
                (select tdm_id from sm.items where object_id=object_id2) as cid
        from    sm.bom_link
        start   with object_id1 = p_objid
        connect by object_id1 = prior object_id2;    begin
    s := objid;
    p := instr(s,',');
    while p > 0 loop
        r := substr(s,0,p - 1);
        s := substr(s,p   1,length(s));
        p := instr(s,',');
        begin
            d := to_number(r);
            for bom_rec in cur_tree(d) loop
                dbms_output.put_line(bom_rec.floor||'~'||bom_rec.pid||'~'||bom_rec.cid||'~');
            end loop;
        exception
        end;
    end loop;
    begin
        d := to_number(s);
        for bom_rec in cur_tree(d) loop
            dbms_output.put_line(bom_rec.floor||'~'||bom_rec.pid||'~'||bom_rec.cid||'~');
        end loop;
    exception
    end;
end;

傳入『123,456,789』字串，並於Procedure 中拆解與 LOOP 呼叫 CURSOR(注意，以逗號分隔，不含單引號)。 PS : 可能是 DataBase 版本問題，我在 Compile 時 (select tdm_id from sm.items where object_id=object_id1) as pid, 與 (select tdm_id from sm.items where object_id=object_id2) as cid 處，會出現錯誤 2.Oracle 中並無 SQL Server 的暫存 Table，一般我都是先建立一個實體的暫存 Tabel，並多開一個欄位用來識別資料寫入者(考慮多人使用狀況)，於資料完成寫入後，可根據該欄位取得自己之資料，並於程式執行完後將寫入資料予以刪除，而不影響其他人資料，以上供你參考，若其他大大有更好建議，也請不吝指教！ ---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

謝謝您的回答丫, 還有個問題想請問您一下,就是針對我第二個問題的.. 最近我寫了一段,用意是要將我recu出來的sql資料再全部一次insert到原來在oracle的實體table 可是一直有error的msg 請您幫我看一下好嗎.拜託拜託您了!!感激不盡~~ CREATE OR REPLACE PROCEDURE BULKTEST IS TYPE name_t IS TABLE OF tablename%ROWTYPE INDEX BY PLS_INTEGER; flr name_t; ob1 name_t; ob2 name_t; ob name_t; prodv name_t; CURSOR cur_tree(p_objid integer,viewid integer) is SELECT LEVEL as floor,OBJECT_ID, OBJECT_ID1, OBJECT_ID2,view_id FROM SM.BOM_LINK WHERE view_id=viewid START WITH OBJECT_ID1 = p_objid CONNECT BY OBJECT_ID1 = PRIOR OBJECT_ID2; BEGIN open cur_tree(36181,2); LOOP FETCH cur_tree BULK COLLECT INTO flr,ob,ob1,ob2,prodv;--<-就是出錯在這一行"PLS-00394: FETCH 敘述句的 INTO 列示中的數值數目錯誤" FORALL indx IN flr.FIRST..flr.LAST--這裡要用flr.first.為什麼下面要用floor丫? INSERT INTO temptable VALUES (floor(indx),object_id(indx),ojbect_id1(indx),object_id2(indx),view_id(indx));--<-"PL/SQL: ORA-00904: "view_id": ID 無效" EXIT WHEN cur_tree%NOTFOUND; END LOOP; END;

Hi demo,    這似乎只要用

insert into temptable(field1,field2,filed3,...)
select field1,field2,filed3,...
from   sourcetable
where  ...

指令即可，不需使用 Cursor !! ---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman