You Asked

Hi Tom,

Thanks in Advance for taking my question. I have an existing procedure that needed a change and I want to add an optional pl/sql table as parameter and it also uses bulk insert. This procedure gets called from various other packages as well so I do not want those packages to be impacted.

When we call this witout the optional parameter I'm getting an oracle error "gtORA-22160: element at index [1] does not exist". Please let me know how can I fix this.

Thanks,
Charan

create table test_gt
(column1 number,
column2 number);

Here is the sample code

CREATE OR REPLACE PACKAGE charan_test
AS
TYPE lid_gtbl_type IS TABLE OF emp.empno%TYPE
INDEX BY BINARY_INTEGER;

gt_ledger_id lid_gtbl_type;

TYPE sec_id_int1_gtbl_type IS TABLE OF dept.deptno%TYPE
INDEX BY BINARY_INTEGER;

PROCEDURE main (
p_ledger_id_tbl IN lid_gtbl_type,
tbl IN sec_id_int1_gtbl_type DEFAULT CAST (
NULL AS sec_id_int1_gtbl_type));
END;
/

CREATE OR REPLACE PACKAGE BODY charan_test
AS
PROCEDURE main (
p_ledger_id_tbl IN lid_gtbl_type,
tbl IN sec_id_int1_gtbl_type DEFAULT CAST (
NULL AS sec_id_int1_gtbl_type))
IS
BEGIN
IF p_ledger_id_tbl.COUNT <> 0
THEN
FORALL i IN 1 .. p_ledger_id_tbl.COUNT
INSERT INTO test_gt (column1, column2)
VALUES (p_ledger_id_tbl (i), tbl (i));
END IF;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
END;
/

and Tom said...

EXCEPTION 
WHEN OTHERS 
THEN 
RAISE; 
END;

I hate that. Why - why why why - why in the world would you do that? You really want to make debugging your code harder don't you... the only thing that construct does is remove information from you - it hides line numbers. UGH.

please don't say "it is just a demo", why would you go out of your way to put that in a demo if you don't do something similar in real life? please stop that practice.

ops$tkyte%ORA11GR2> create table test_gt
  2  (column1 number,
  3  column2 number);

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE OR REPLACE PACKAGE charan_test
  2  AS
  3      TYPE lid_gtbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
  4      TYPE sec_id_int1_gtbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
  5  
  6      gt_ledger_id lid_gtbl_type;
  7  
  8      PROCEDURE main
  9      ( p_ledger_id_tbl IN lid_gtbl_type,
 10        tbl IN sec_id_int1_gtbl_type DEFAULT cast( null as sec_id_int1_gtbl_type) );
 11  
 12  END;
 13  /

Package created.

ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE OR REPLACE PACKAGE BODY charan_test
  2  AS
  3  
  4  PROCEDURE main
  5  ( p_ledger_id_tbl IN lid_gtbl_type,
  6    tbl IN sec_id_int1_gtbl_type DEFAULT cast( null as sec_id_int1_gtbl_type) )
  7  as
  8  begin
  9  
 10      if ( tbl.count = p_ledger_id_tbl.count )
 11      then
 12          FORALL i IN 1 .. p_ledger_id_tbl.COUNT
 13              INSERT INTO test_gt (column1, column2)
 14              VALUES (p_ledger_id_tbl (i), tbl (i));
 15      else
 16          forall i in 1.. p_ledger_id_tbl.count
 17              insert into test_gt(column1)
 18              values( p_ledger_id_tbl(i) );
 19      end if;
 20  end;
 21  end;
 22  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_data1 charan_test.lid_gtbl_type;
  3      l_data2 charan_test.sec_id_int1_gtbl_type;
  4  begin
  5      l_data1(1) := 100;
  6      l_data1(2) := 200;
  7      l_data2(1) := 1000;
  8      l_data2(2) := 2000;
  9      charan_test.main( l_data1 );
 10      charan_test.main( l_data1, l_data2 );
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from test_gt;

   COLUMN1    COLUMN2
---------- ----------
       100
       200
       100       1000
       200       2000

ops$tkyte%ORA11GR2>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Charan V, June 04, 2013 - 5:22 pm UTC

Tom,

Trust me this is not how the actual code is .. Just to make the sameple, I quickly wrote that.

Again thanks for your help,
Charan.

June 04, 2013 - 8:00 pm UTC

I said don't tell me that ;)

why did you have it - just to make the sample??? why add something to an example that

a) makes it harder to debug
b) only takes away from the example, doesn't add anything
c) added keystrokes and lines of code to something that could have been smaller

??? It must be a practice ingrained in the code at your site "all blocks of code MUST have an exception handler" or something similar. A block of code like that doesn't just pop out of nowhere!

Just saying, review your exception handling - if you have anything similar to that in real code - get rid of it. You use a when others

a) to release a resource - like a file handle or something - that doesn't automagically release itself. then you re-raise it

b) to log an error using an autonomous transaction. then you re-raise it.

and that is about it.

at least this when others does have a raise in it...

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

How to make a pl/sql table as optional parameter in a procedure using bulk insert

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs