Procedure:::::


CREATE OR REPLACE PROCEDURE RDF_NA_Q206.UPDATE_NODE (
    c_node_id IN temp_rdF_node.NODE_ID%type,
    c_status OUT char
    
    )

    IS
         CURSOR C_Node is select  rl.NAV_STATE_ID 
                          from  rdf_link_node rln, rdf_link rl
                          where rln.NODE_ID=c_node_id and rln.LINK_ID=rl.LINK_ID;
        -- c_navigable temp_rdf_node.N_NAVIGABLE%type;
         
        -- select temp_rdf_node.N_NAVIGABLE into c_navigable from temp_rdf_node where temp_rdf_node.node_id=c_node_id;


    BEGIN
        for x in c_node
        loop
          
            if (x.Nav_state_id is not null) then
                UPDATE temp_rdf_node SET n_Navigable = 'Y' WHERE temp_rdf_node.NODE_ID=c_node_id;
                c_status:='Y';
            elsif(x.Nav_state_id is null) then
                
                UPDATE temp_rdf_node SET n_Navigable = 'N' WHERE temp_rdf_node.NODE_ID=c_node_id;
                c_status:='N';
                
            end if;
        end loop;

       COMMIT;

    END UPDATE_NODE;
/







Calling procedure:

DECLARE
          cursor c1 is select * from temp_rdf_node ;
          v_status char:='Y';
      BEGIN
              for r in c1
              loop
                  update_node(r.node_id,v_status);
              end loop;
             if(v_status='N') then
                  dbms_output.put_line('Link is Non-Navigable and Table is Updated.');
             elsif(v_status='Y') then
                  dbms_output.put_line('Link is Navigable and Table is Updated.');
             end if;
             
      EXCEPTION
      
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('ERROR! No row found');
        WHEN INVALID_CURSOR THEN
             dbms_output.put_line('ERROR! Invalid cursor');
        WHEN OTHERS THEN
             dbms_output.put_line('*****Error Occured********');
             dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
             dbms_output.put_line('SQLERRM: '||SQLERRM);
  END ;



Structure of Temp_rdf_node::


CREATE TABLE RDF_NA_Q206.TEMP_RDF_NODE
(
  NODE_ID      NUMBER(10)                       NOT NULL,
  LAT          NUMBER(10)                       NOT NULL,
  LON          NUMBER(10)                       NOT NULL,
  Z_COORD      NUMBER(10),
  ZLEVEL       NUMBER(5)                        NOT NULL,
  IS_ALIGNED   CHAR(1 CHAR)                     NOT NULL,
  NODE_TYPE    NUMBER(2),
  N_NAVIGABLE  CHAR(1 CHAR)
)



CREATE TABLE RDF_NA_Q206.RDF_LINK_NODE
(
  LINK_ID   NUMBER(10)                          NOT NULL,
  NODE_ID   NUMBER(10)                          NOT NULL,
  REF_TYPE  CHAR(1 CHAR)                        NOT NULL
)




CREATE TABLE RDF_NA_Q206.RDF_LINK
(
  LINK_ID        NUMBER(10)                     NOT NULL,
  DETAILED_CITY  CHAR(1 CHAR)                   NOT NULL,
  BRIDGE         CHAR(1 CHAR)                   NOT NULL,
  TUNNEL         CHAR(1 CHAR)                   NOT NULL,
  URBAN          CHAR(1 CHAR)                   NOT NULL,
  IS_ALIGNED     CHAR(1 CHAR)                   NOT NULL,
  FULL_GEOMETRY  CHAR(1 CHAR),
  NAV_STATE_ID   NUMBER(10),
  LONG_HAUL      CHAR(1 CHAR)                   NOT NULL,
  STUB_LINK      CHAR(1 CHAR)                   NOT NULL
)