pl/sql runtime error !

I am trying to run my stored proc, but, it gives me the following error...  

ORA-06550: line9, column 15;
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 3:
PL/SQL: Statement Ignored
ORA-06512: at line 58

Any help is appreciated, TIA
PROCEDURE P_STORE_BOOKMARK(userId IN NUMBER, assetId IN NUMBER, tagNames IN INT_STRING_LIST) IS
temp_bookmark_id NUMBER := 0;
temp_bookmark_seq NUMBER := 0;
temp_modified_dt TIMESTAMP(6) WITH TIME ZONE:=  SYSTIMESTAMP AT TIME ZONE 'UTC';
temp_tag_name VARCHAR2(50 BYTE) := '';
temp_tag_id NUMBER := 0;
temp_bookmark_count NUMBER := 0;
BEGIN
  TRACE.PENTRY;
  SELECT BOOKMARK_ID_SEQ.NEXTVAL INTO temp_bookmark_seq FROM DUAL;
  MERGE INTO BOOKMARK D
      USING (SELECT BOOKMARK_ID AS temp_bookmark_id FROM bookmark_tag_xref B 
              WHERE bookmark_id IN (
                SELECT BOOKMARK_ID FROM bookmark 
                  WHERE bookmark.user_id = userId AND bookmark.asset_id = assetId)) S
        ON (D.BOOKMARK_ID = S.temp_bookmark_id)
      WHEN MATCHED THEN UPDATE SET MODIFIED_DT = temp_modified_dt
      WHEN NOT MATCHED THEN INSERT(USER_ID, ASSET_ID, BOOKMARK_ID, MODIFIED_DT)
        VALUES ( userID, assetId, temp_bookmark_seq, SYSTIMESTAMP AT TIME ZONE 'UTC');
  -- DELETE ALL ASSOCIATIONS
  DELETE FROM bookmark_tag_xref WHERE bookmark_id = temp_bookmark_id;
  
  -- ADD ASSOCIATIONS IN XREF TABLE
   FOR i IN 1 .. tagNames.COUNT
       LOOP
          -- before adding check to see if the tag exists, if not, add the tag first
          SELECT lcase_tag_nm INTO temp_tag_name FROM bookmark_tag WHERE user_id = userId;
          IF ( SQL%ROWCOUNT = 0 ) THEN
            -- tag does not exist, add a new tag
            SELECT BOOKMARK_ID_SEQ.NEXTVAL INTO temp_tag_id FROM DUAL;
            INSERT INTO BOOKMARK_TAG (USER_ID, LCASE_TAG_NM, TAG_ID, ORIG_TAG_NM) VALUES
                (userID, LOWER(tagNames(i).str), temp_tag_id, tagNames(i).str);
          END IF;
          -- ADD ASSOCIATION
          INSERT INTO BOOKMARK_TAG_XREF (BOOKMARK_ID, TAG_ID, TAG_SEQ_NO) VALUES
                (temp_bookmark_id, temp_tag_id, BOOKMARK_ID_SEQ.NEXTVAL);
   END LOOP;
    
  -- REMOVE ORPHAN TAGS
   FOR k in (SELECT TAG_ID FROM BOOKMARK_TAG WHERE user_id = userID) LOOP
      SELECT COUNT(BOOKMARK_ID) INTO temp_bookmark_count FROM bookmark_tag_xref WHERE tag_id = K.TAG_ID;
       IF ( temp_bookmark_count = 0 ) THEN
          DELETE FROM bookmark_tag WHERE tag_id = K.TAG_ID;
       END IF;
   END LOOP;
  TRACE.PEXIT;
END P_STORE_BOOKMARK;

Open in new window

bent27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MarkusIdCommented:
Hi,

Seems to be an error in the package trace - procedure entry.
0
bent27Author Commented:
Markusid, thank you so much for taking a look, I did try eliminating it, but, I get the same error (the same line number)
0
bent27Author Commented:
btw, this is how, I'm running it  
DECLARE
  USERID NUMBER;
  ASSETID NUMBER;
  TAGNAMES CMR.INT_STRING_LIST;
BEGIN
  USERID := 106568;
  ASSETID := 1112;
  -- Modify the code to initialize the variable
  TAGNAMES := 123;
 
  BOOKMARK_API.P_STORE_BOOKMARK(
    USERID => USERID,
    ASSETID => ASSETID,
    TAGNAMES => TAGNAMES
  );
END;

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HainKurtSr. System AnalystCommented:
maybe you can debug the code (you can sue TOAD, free trial version) and see where is the error exactly. The line numbers for the code does not mean anything... the code you post has 42 lines, and error says line 58. Is this procedure a part of package, or stand alone procedure?...
0
bent27Author Commented:
its a part of the package...
0
AndytwCommented:
The first error is in your anonymous block (line 9) with the statement:
TAGNAMES := 123;

based on how you are using this parameter in your procedure P_STORE_BOOKMARK ...
e.g.   INSERT INTO BOOKMARK_TAG (USER_ID, LCASE_TAG_NM, TAG_ID, ORIG_TAG_NM) VALUES
                (userID, LOWER(tagNames(i).str), temp_tag_id, tagNames(i).str);

... I suspect that this is a collection of records.  In which case, you cannot do:
TAGNAMES := 123;
the literal 123 is not a collection.

So this is where your problem is.  Can you post the definition of the type CMR.INT_STRING_LIST? Then I can help you with the code to  assign this variable correctly.
0
MarkusIdCommented:
Well, it should probably be something like

tagnames(1) := 123;

But this depends on the type...
0
bent27Author Commented:

create or replace TYPE INT_STRING_LIST IS TABLE OF INT_STRING_PAIR;
create or replace TYPE INT_STRING_PAIR AS OBJECT (
  id  NUMBER(12),
  str VARCHAR2(4000));

Open in new window

0
MarkusIdCommented:
Well, in this case you should try this:

DECLARE
  USERID NUMBER;
  ASSETID NUMBER;
  TAGNAMES CMR.INT_STRING_LIST := cmr.int_string_list();;
BEGIN
  USERID := 106568;
  ASSETID := 1112;
  -- Modify the code to initialize the variable
  TAGNAMES(1).ID := 123;
 
  BOOKMARK_API.P_STORE_BOOKMARK(
    USERID => USERID,
    ASSETID => ASSETID,
    TAGNAMES => TAGNAMES
  );
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndytwCommented:
Ok, so your anonymous block should be something like:
DECLARE
  USERID NUMBER;
  ASSETID NUMBER;
  TAGNAMES INT_STRING_LIST := INT_STRING_LIST();
BEGIN
  USERID := 106568;
  ASSETID := 1112;
  -- Modify the code to initialize the variable
  --TAGNAMES := 123;
  TAGNAMES.EXTEND(2);
  TAGNAMES(1) := INT_STRING_PAIR(1,'string1');
  TAGNAMES(2) := INT_STRING_PAIR(2,'string2');
 
  BOOKMARK_API.P_STORE_BOOKMARK(
    USERID => USERID,
    ASSETID => ASSETID,
    TAGNAMES => TAGNAMES
  );
END;

Open in new window

0
bent27Author Commented:
Thank you guys...
0
AndytwCommented:
I missed out the package prefix for INT_STRING_LIST (and presumming that INT_STRING_PAIR is also declared in the same package), it should be...

MarkusId: It's a nested table so you need to call EXTEND on TAGNAMES before assigning an item, otherwise you'll get a Subscript beyond cout error on assignment.
DECLARE
  USERID NUMBER;
  ASSETID NUMBER;
  TAGNAMES CMR.INT_STRING_LIST := CMR.INT_STRING_LIST();
BEGIN
  USERID := 106568;
  ASSETID := 1112;
  -- Modify the code to initialize the variable
  --TAGNAMES := 123;
  TAGNAMES.EXTEND(2);
  TAGNAMES(1) := CMR.INT_STRING_PAIR(1,'string1');
  TAGNAMES(2) := CMR.INT_STRING_PAIR(2,'string2');
 
  BOOKMARK_API.P_STORE_BOOKMARK(
    USERID => USERID,
    ASSETID => ASSETID,
    TAGNAMES => TAGNAMES
  );
END;

Open in new window

0
bent27Author Commented:
Thanks Andytw, I appreciate your help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.