--
connect &METADATAOWNER/&METADATAPASSWORD@&TNSALIAS
--
create or replace package METADATA_MANAGER
AUTHID CURRENT_USER
as
  procedure updateImageMetadata(P_RESID VARCHAR2, P_TITLE XMLType, P_DESCRIPTION XMLType);
end;
/
--
show errors
--
create or replace public synonym METADATA_MANAGER for &METADATAOWNER..METADATA_MANAGER
/
create or replace package body METADATA_MANAGER
as 
--
procedure updateImageMetadata(P_RESID VARCHAR2, P_TITLE XMLType, P_DESCRIPTION XMLType)
as
begin
  update IMAGE_METADATA_TABLE m
    set OBJECT_VALUE = updateXML
                       (
                          OBJECT_VALUE,
                          '/img:imageMetadata/img:Title',
                          P_TITLE,
                          'xmlns:img="http://xmlns.oracle.com/demo/imageMetadata"'
                       )
   where m.RESID = P_RESID
     and XMLExists
         (
           'declare namespace img = "http://xmlns.oracle.com/demo/imageMetadata"; (: :)
            /img:imageMetadata/img:Title'
           passing m.OBJECT_VALUE
         );
             
  dbms_output.put_line('Update Title : Rowcount = ' ||  SQL%ROWCOUNT );  
        
  if (SQL%ROWCOUNT = 0) then
     update IMAGE_METADATA_TABLE m
        set object_value = insertChildXML
                           (
                              object_value, 
                              '/img:imageMetadata',
                              'img:Title',
                               P_TITLE,
                             'xmlns:img="http://xmlns.oracle.com/demo/imageMetadata"'
                           )
      where m.RESID = P_RESID; 

      dbms_output.put_line('Insert Title : Rowcount = ' ||  SQL%ROWCOUNT );  

  end if;
   
  update IMAGE_METADATA_TABLE m
     set OBJECT_VALUE = updateXML
                        (
                           OBJECT_VALUE,
                           '/img:imageMetadata/img:Description',
                           P_DESCRIPTION,
                           'xmlns:img="http://xmlns.oracle.com/demo/imageMetadata"'
                        )
   where m.RESID = P_RESID
     and XMLExists
         (
           'declare namespace img = "http://xmlns.oracle.com/demo/imageMetadata"; (: :)
            /img:imageMetadata/img:Description'
           passing m.OBJECT_VALUE
         );
        
  dbms_output.put_line('Update Description : Rowcount = ' ||  SQL%ROWCOUNT );  

  if (SQL%ROWCOUNT = 0) then
    update IMAGE_METADATA_TABLE m
       set object_value = insertChildXML
                          (
                             object_value, 
                             '/img:imageMetadata',
                             'img:Description',
                              P_DESCRIPTION,
                             'xmlns:img="http://xmlns.oracle.com/demo/imageMetadata"'
                          )
     where m.RESID = P_RESID;
     dbms_output.put_line('Insert Description : Rowcount = ' ||  SQL%ROWCOUNT );  
  end if;
end;
--
end;
/
show errors
--
grant execute on METADATA_MANAGER to public
/
disconnect
-- 