problem in passing Type table to procedure ..

CREATE TABLE TABLE2 (
  TRANSCODE NUMBER,
  CUSTOMERNUMBER NUMBER (7),
  ACCOUNTNUMBER CHAR (21)); /
CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER INSERT ON TABLE2
FOR EACH ROW DECLARE
  TYPE Var_arr IS TABLE OF VARCHAR2(50)
    INDEX BY BINARY_INTEGER;      v_tab Var_arr;
  v_idx NUMBER;
BEGIN

• Initialise the collection.

--FOR i IN 1 .. 5 LOOP

    v_tab(1) := TO_CHAR(:NEW.TRANSCODE) ;
    v_tab(2) := TO_CHAR(:NEW.CUSTOMERNUMBER);	
    v_tab(3) := :NEW.ACCOUNTNUMBER;		

• Delete the third item of the collection.
  --v_tab.DELETE(3);
  
• Traverse sparse collection v_idx := v_tab.FIRST;
  • WHILE v_idx IS NOT NULL LOOP
    • DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx)); --v_idx := v_tab.NEXT(v_idx);
      --END LOOP display_loop;
      DBankPackage.myproc(v_tab);

END; /

CREATE or REPLACE PACKAGE DBankPackage
IS

 TYPE Var_arr IS TABLE OF VARCHAR2(50)
  INDEX BY BINARY_INTEGER;
 PROCEDURE MyProc(tabval Var_arr);

END DBankPackage;
/   

CREATE or REPLACE PACKAGE BODY DBankPackage IS
 PROCEDURE MyProc(tabval)
 IS
  v_idx NUMBER;
 BEGIN

• Traverse sparse collection
  --v_idx := v_tab.FIRST;
  << display_loop >>
  • WHILE v_idx IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('The number ' || 'asdasd');
    • v_idx := v_tab.NEXT(v_idx);
      --END LOOP display_loop;
      

 END MyProc;

END DBankPackage;
/ Received on Tue Apr 22 2003 - 12:03:35 CEST