1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORDimage-procedure problem

Discussion in 'SQL PL/SQL' started by lewy2508, Jun 20, 2012.

  1. lewy2508

    lewy2508 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I created id, pictures and sygnature column in "Pictures" table. I have copied some pictures to "Pictures" table (I used stored procedure which generated sygnatures). When tried compile other stored procedure I received this message :

    Error(12,4): PL/SQL: SQL Statement ignored;
    Error(12,39): PL/SQL: ORA-00904: "ORDSYS"."IMGSIMILAR": incorrect identifier

    This stored procedure you can find below:

    create or replace
    procedure find (color varchar2, texture varchar2, shape varchar2, location varchar2, sensitivity number) is
    scale varchar2(200);
    number number;
    idd NUMBER;
    image ORDSYS.ORDIMAGE;
    query_signature ORDSYS.ORDIMAGESIGNATURE;
    query_image ORDSYS.ORDIMAGE;
    ctx raw(64) := null;


    CURSOR getphotos (c_scale varchar2) IS
    SELECT id, picture FROM pictures WHERE


    ordsys.imgsimilar(sygnature, query_image, c_scale, sensitivity) =1 order by id;

    BEGIN
    SELECT picture INTO query_image FROM tab_obr WHERE id = (select max(id) from tab_obr ) ;
    -- Initialize signature object
    query_signature := ORDSYS.ORDIMAGESIGNATURE.init();
    -- Create temporary storage for the LOB in the signature object
    DBMS_LOB.CREATETEMPORARY(query_signature.signature, TRUE);
    query_signature.generateSignature(query_image);
    scale := 'color='||color||' texture='||texture||' shape='||shape||' location='||location||'';
    OPEN getphotos (scale);
    LOOP
    FETCH getphotos INTO idd, image;
    EXIT WHEN getphotos%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Image with ID ' || idd || ' matches query image');
    insert into tab_obr2 (select * from picture where id=idd);
    image.export(ctx, 'FILE', 'PICTURE_KAT_EKSS', image.getSourceName());
    END LOOP;
    CLOSE getphotos;
    DBMS_LOB.FREETEMPORARY(query_signature.signature);
    END;

    Can you help me and tell me why I cannot compile this procedure? I have generated sygnatures before I tried compile procedure above...

    I'm so sorry for my bad English :)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This works in 11.2.0.3 -- notice the changes made:

    Code (SQL):
    CREATE OR REPLACE
    PROCEDURE find (color IN varchar2, texture IN varchar2, shape IN varchar2, location IN varchar2, sensitivity IN NUMBER) IS
    v_scale varchar2(200);
    v_number NUMBER;
    v_idd NUMBER;
    v_image ORDSYS.ORDIMAGE;
    v_query_signature ORDSYS.ORDIMAGESIGNATURE;
    v_query_image ORDSYS.ORDIMAGE;
    v_ctx raw(64) := NULL;
    CURSOR getphotos (c_scale varchar2) IS
    SELECT id, picture FROM pictures WHERE
    ordsys.imgsimilar(sygnature, query_image, c_scale, sensitivity) =1 ORDER BY id;
    BEGIN
    SELECT picture INTO query_image FROM tab_obr WHERE id = (SELECT MAX(id) FROM tab_obr ) ;
    -- Initialize signature object
    v_query_signature := ORDSYS.ORDIMAGESIGNATURE.init();
    -- Create temporary storage for the LOB in the signature object
    DBMS_LOB.CREATETEMPORARY(v_query_signature.signature , TRUE);
    v_query_signature.generateSignature(v_query_image);
    v_scale := 'color='||color||' texture='||texture||' shape='||shape||' location='||location||'';
    OPEN getphotos (v_scale);
    LOOP
    FETCH getphotos INTO v_idd, v_image;
    EXIT WHEN getphotos%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Image with ID ' || v_idd || ' matches query image');
    INSERT INTO tab_obr2 (SELECT * FROM picture WHERE id=v_idd);
    v_image.export(v_ctx, 'FILE', 'PICTURE_KAT_EKSS', v_image.getSourceName());
    END LOOP;
    CLOSE getphotos;
    DBMS_LOB.FREETEMPORARY(v_query_signature.signature);
    END;
    /
     
    Variables to a procedure need to be declared as IN, OUT or IN OUT and you didn't do that. Also reserved words are not allowed which is why I changed the variable names by prepending v_ to them.
     
  3. lewy2508

    lewy2508 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Unfortunately I received the same error message :(
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have failed to report which release of Oracle you are running and the installed options for the database. Seeing my mistake I created the necessary tables from the procedure and received the same error and, upon investigation, found no procedure named iimgsimilar in the ORDSYS schema or in any other. Plainly and simply put this procedure you've tried to call does not exist.