CODEWITHSUNDEEP
oracle-sqldeveloperoracle-apexoracle12c
Andre Mueller
Andre Mueller

Reputation: 49

How to create an APEX_COLLECTION?

I want to create an apex_collection in the Oracle SQL Developer for my apex webapplication. Here's my code:

 apex_collections.create_or_truncate_collection('TEST');

After running the statement I get the following:

enter image description here

If I enter the select-statement: 

SELECT *
  FROM apex_collections
 WHERE collection_name = 'TEST';

afterwards, I get this:

enter image description here

It seems to me that no collection has been created,although there is no error code. What am I doing wrong? Why is the collection not created? I also tried to run the statement in the SQL Workshop of Oracle APEX, but the command is unknown there. I am using Oracle 12c version.

Upvotes: 1

Views: 4326

Answers (1)

Adrian P
Adrian P

Reputation: 904

You will need a valid APEX session to create the collection. If you are on APEX 18.1 or later, use the procedure apex_session.create_session (see documentation) to create one. Make sure that the app_id is valid for the workspace that your schema is assigned to.

For anything before 18.1, OraOpenSource oos-utils has a procedure that does the same: oos_util_apex.create_session (see code). You will need to install oos-utils, or at least compile this package in your schema. Alternatively, replicate the activity contained in this procedure.

Update: Also, I just noticed Jeff Smith's comments. You will need to add members to the collection in order to get meaningful results.

begin
  apex_session.create_session(
    p_app_id => 100
    , p_page_id => 1
    , p_username => 'somebody'
  );
end;
/

select * from apex_collections; -- shows nothing

begin
  apex_collection.create_or_truncate_collection(
    p_collection_name => 'test'
  );

  apex_collection.add_member(
    p_collection_name => 'test'
    , p_c001 => 'test row'
  );
end;
/

select * from apex_collections; -- shows one record

Also, if you are using SQLDEV to troubleshoot the collections in an APEX session, then you'll want to use either the apex_session.attach or oos_util_apex.join_session procedure, depending on the version of APEX you are running. Use the APEX Session ID assigned to your browser session. HTH.

Upvotes: 7

Related Questions