CODEWITHSUNDEEP
oracle-databaseplsql
VKP
VKP

Reputation: 658

Object not supported in this context while Creating the package in Oracle

Hi I am trying to execute below statements for creating a package , but i am getting an error saying

Error(3,1): PLS-00540: object not supported in this context.

When trying to change it to RECORD i am getting another error

Error(3,32): PLS-00103: Encountered the symbol "RECORD" when expecting one of the following:     
object opaque The symbol "object was inserted before "RECORD" to continue.

package Definition trying to execute

CREATE OR REPLACE PACKAGE        "PKG_LOAN_LOGIC_SERVICE_V2" AS

TYPE LOAN_LOGIC_RESULT_TYPE AS OBJECT (
loanid NUMBER,
ret_value varchar2(500),
xPath varchar2(200)
);

TYPE LOAN_LOGIC_RESULTS_TABLE IS TABLE OF LOAN_LOGIC_RESULT_TYPE;

procedure PR_CORAL_LOAN_LOGIC( 
                      in_loan_id      IN   wcts.loans.loan_id%TYPE,
                      in_trans_id     IN   wcts.loans.loan_id%TYPE,
                      as_errm            out varchar2,
                      out_order_contents_tab out LOAN_LOGIC_RESULTS_TABLE
                      );
END PKG_LOAN_LOGIC_SERVICE_V2;

When i am trying to execute the user defined types in another standalone SQL Developer Window , its executing successfully .

CREATE OR REPLACE TYPE LOAN_LOGIC_RESULT_TYPE AS OBJECT (
loanid NUMBER,
ret_value varchar2(500),
xPath varchar2(200)
);

CREATE OR REPLACE TYPE LOAN_LOGIC_RESULTS_TABLE AS TABLE OF LOAN_LOGIC_RESULT_TYPE;

Why its not allowing me to run inside package ? or how i will create these types inside a package

Update:

create or replace procedure PR_CORAL_LOAN_LOGIC( 
                      in_loan_id      IN   wcts.loans.loan_id%TYPE,
                      in_trans_id     IN   wcts.loans.loan_id%TYPE,
                      as_errm            out varchar2,
                      out_order_contents_tab out LOAN_LOGIC_RESULTS_TABLE
                      )
is 
begin
for o in (SELECT    xpath_name    FROM  loan_logic  WHERE   attribute = 
 upper(TRIM('STATUS_0')))
  loop
  -- How i will get the user defined type here tp assign the values 
  --Assign the column values while iterating         
   END LOOP;
 end PR_CORAL_LOAN_LOGIC;

Upvotes: 0

Views: 516

Answers (1)

Abra
Abra

Reputation: 20914

According to Oracle documentation

You must define object types using the SQL statement CREATE TYPE

EDIT

For example

create or replace procedure PR_CORAL_LOAN_LOGIC(out_order_contents_tab out LOAN_LOGIC_RESULTS_TABLE)
is
  obj LOAN_LOGIC_RESULT_TYPE;
begin
  obj := LOAN_LOGIC_RESULT_TYPE(2, 'return value', 'some/path');
  out_order_contents_tab := LOAN_LOGIC_RESULTS_TABLE();
  out_order_contents_tab.extend;
  out_order_contents_tab(1) := obj;

There are lots of examples available online, including Working with Collections. Not to mention the Oracle documentation.

Upvotes: 3

Related Questions