Home » SQL & PL/SQL » SQL & PL/SQL » varray out parameter in a procedure
varray out parameter in a procedure [message #243618] Fri, 08 June 2007 01:24 Go to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hello experts,

how do i handle this error. what is the mistake i am making.

please help me out

here is the package

SQL> CREATE OR REPLACE package hcl_bpel_dash as
  2  TYPE x_conc_seg IS  varray(100) OF VARCHAR2(100);
  3  procedure Test_perf(conc_seg OUT hcl_bpel_dash.x_conc_seg);
  4  end;
  5  /
Package created.

SQL> CREATE OR REPLACE package body hcl_bpel_dash as
  2         procedure Test_perf(conc_seg OUT hcl_bpel_dash.x_conc_seg)
  3         is
  4          i NUMBER;
  5   cursor hcl_demand IS
  6   SELECT msik.concatenated_segments   conc_segment
  7  FROM mtl_system_items_kfv msik,
  8       org_organization_definitions ood,
  9    hr_operating_units hou,
 10    oe_order_lines_all oel,
 11    oe_order_headers_all oeh,
 12    hz_cust_accounts hca,
 13    po_requisition_lines_all prl,
 14    mtl_parameters mp
 15    WHERE
 16   msik.organization_id = oel.ship_from_org_id
 17   AND oel.ship_from_org_id = ood.organization_id
 18   AND oel.org_id = hou.organization_id
 19   AND oel.inventory_item_id = msik.inventory_item_id
 20   AND oel.schedule_ship_date BETWEEN (sysdate-10) AND Sysdate
 21   AND oel.header_id = oeh.header_id
 22   AND oeh.sold_to_org_id = hca.cust_account_id
 23   AND oel.source_document_line_id =       prl.requisition_line_id(+)
 24   AND prl.deliver_to_location_id = mp.organization_id(+)
 25   AND oel.item_type_code = 'STANDARD'
 26   AND oel.line_category_code IN ('ORDER')
 27   AND oel.flow_status_code NOT IN ('CLOSED', 'SHIPPED');
 28         begin
 29         i :=1;
 30  Conc_seg := x_conc_seg();
 31   FOR  emp_rec IN hcl_demand loop
 32   Conc_seg.extend(1);
 33   Conc_seg(I) := emp_rec.conc_segment;
 34   i :=i+1;
 35   END LOOP;
 36         end;
 37        end;
 38  /

Package body created.

and this is how i execute

SQL> declare
  2  TYPE x_conc_seg_1 IS  varray(100) OF VARCHAR2(100);
  3  con x_conc_seg_1;
  4  begin
  5  hcl_bpel_dash.Test_perf(con);
  6  end;
  7  /
hcl_bpel_dash.Test_perf(con);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_PERF'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored


my oracle version
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


how should i execute this procedure? what is the mistake i am doing.
please help me out...

[Updated on: Fri, 08 June 2007 01:25]

Report message to a moderator

Re: varray out parameter in a procedure [message #243622 is a reply to message #243618] Fri, 08 June 2007 01:46 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

It's because you've declared two types, one in the package ( hcl_bpel_dash.x_conc_seg ) and one local to your PL/SQL block.
Just because they are both declared identically, i.e.
VARRAY(100) OF VARCHAR2(100), doesn't mean they are the same type.

What you need to do is declare your local varray variable to be of the package type, i.e.
HOU@HOUDEV.WORLD> CREATE OR REPLACE package hcl_bpel_dash as
  2    TYPE x_conc_seg IS  varray(100) OF VARCHAR2(100);
  3    procedure Test_perf(conc_seg OUT hcl_bpel_dash.x_conc_seg);
  4  end;
  5  /

Package created.

HOU@HOUDEV.WORLD> CREATE OR REPLACE PACKAGE BODY hcl_bpel_dash
  2  AS
  3    procedure Test_perf(conc_seg OUT hcl_bpel_dash.x_conc_seg)
  4    as
  5    begin
  6      null;
  7    end;
  8  END;
  9  /

Package body created.

HOU@HOUDEV.WORLD> declare
  2    con hcl_bpel_dash.x_conc_seg;
  3  begin
  4    hcl_bpel_dash.Test_perf(con);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Regards
Re: varray out parameter in a procedure [message #243625 is a reply to message #243622] Fri, 08 June 2007 02:07 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

i get it now.thanks for the reply.

regards
shanth
Previous Topic: Hi Experts,
Next Topic: Duplicate/Split rows per week
Goto Forum:
  


Current Time: Sat Dec 10 18:40:10 CST 2016

Total time taken to generate the page: 0.18430 seconds