asked on

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

MarkusId

Hi,

Seems to be an error in the package trace - procedure entry.

bent27

ASKER

Markusid, thank you so much for taking a look, I did try eliminating it, but, I get the same error (the same line number)

bent27

ASKER

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

HainKurt

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?...

bent27

ASKER

its a part of the package...

Andytw

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.

MarkusId

Well, it should probably be something like

tagnames(1) := 123;

But this depends on the type...

bent27

ASKER

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

ASKER CERTIFIED SOLUTION

MarkusId

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Andytw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bent27

ASKER

Thank you guys...

Andytw

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

bent27

ASKER

Thanks Andytw, I appreciate your help :)