CODEWITHSUNDEEP
oracle-databasestored-procedures
user2342259
user2342259

Reputation: 345

How to call and execute a Oracle stored procedure using TYPE as a parameter

This is the package specification:

PROCEDURE Save_Countries
    ( p_inst_id Number,
      p_CountryList  IN T_COUNTRY_TYPE
    )
AS
v_count number;
BEGIN
            for i in p_CountryList.first..p_CountryList.last loop

                  insert into countries (INST_ID, COUNTRY_NAME, COUNTRY_CODE)
                        values (p_inst_id, p_CountryList(i).COUNTRY_NAME, p_CountryList(i).COUNTRY_CODE);
            end loop;
      Commit;
end Save_Countries ;

This is the Type specification:

create or replace TYPE OBJ_COUNTRY FORCE as OBJECT (
      COUNTRY_ID        NUMBER(10),
      COUNTRY_NAME      VARCHAR2(255),
      COUNTRY_CODE      VARCHAR2(6)
);
create or replace TYPE T_COUNTRY_TYPE as TABLE OF OBJ_COUNTRY;

So far I have this:

DECLARE
  P_INST_ID NUMBER;
  P_COUNTRYLIST T_COUNTRY_TYPE;
BEGIN
  P_INST_ID := 255;

  PKG_TEST.SAVE_COUNTRIES(
    P_INST_ID => P_INST_ID,
    P_COUNTRYLIST => P_COUNTRYLIST
  );
END;

Question I am trying to call the store procedure from my Oracle SQL Developer. How I can initialize the list (TYPE) so I can pass that as a parameter. I've googled around but I wasn't able to find something to help with this.

Upvotes: 1

Views: 5082

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

For nested tables, you must initialise and assign elements to the collection. 

DECLARE
  P_INST_ID NUMBER;
  P_COUNTRYLIST T_COUNTRY_TYPE := T_COUNTRY_TYPE(); --initialization
BEGIN
  P_INST_ID     := 255;
  P_COUNTRYLIST.extend(2); --allocate 2 null elements.
  P_COUNTRYLIST(1) :=  OBJ_COUNTRY(1,'INDIA','IND'); --set the values
  P_COUNTRYLIST(2) :=  OBJ_COUNTRY(2,'AUSTRALIA','AUS');
--To assign n more elements, use P_COUNTRYLIST.extend(n);



  PKG_TEST.SAVE_COUNTRIES(
    P_INST_ID => P_INST_ID,
    P_COUNTRYLIST => P_COUNTRYLIST
  );
END;
/

Demo

Upvotes: 2

Related Questions