2 Replies Latest reply on Jan 6, 2014 4:53 PM by alwu-Oracle

    Using Cursor and FOR LOOP to INSERT the data into table

    Angelina84

      Hi all,

       

      I have SELECT statement that returns 3 rows:

       

      PROCESSNAMEPROTDATE
      IMM2013-12-18
      Metrology2013-11-18
      CT2013-12-04

       

      SELECT  processName, MAX(NVL(protStartDate, protCreateDate)) AS protDate

          FROM TABLE(SEM_MATCH("{

                  ?ipc rdf:type s:Protocol .

                  ?ipc s:protocolNumber ?protNum .

                  ?ipc s:protocolCreateDate ?protCreateDate .

                  OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

                  ?ipra rdf:type s:ProcessAggregate .

                  ?ipra s:hasProtocol ?iprot .

                  ?iprot s:protocolNumber ?protNum .

                  ?ipra s:processAggregateProcess ?processName.

          }",sem_models("PROTS", "LINEARS"),NULL, SEM_ALIASES(SEM_ALIAS("","http://VISION/Data/SEMANTIC#"),SEM_ALIAS("s","http://VISION/DataSource/SEMANTIC#")),NULL))

              Group by processName

       

      Now I need to INSERT these values into the table along with the other values.

      these other values come from different table.


             INSERT INTO MODEL_CLASS_COUNTS (MODEL_NAME, CLASS_NAME, INS_COUNT, COUNT_DATETIME, PROCESS_NAME, PROT_DATE)

             VALUES

             ("$MODEL",     

                  "${i}",

              (SELECT COUNT (DISTINCT S)  FROM TABLE(SEM_MATCH(

                              "{?s rdf:type :${i} . }",SEM_Models("$MODEL"),NULL, SEM_ALIASES(SEM_ALIAS("","http://VISION/DataSource/SEMANTIC#")),NULL))),

               SYSTIMESTAMP, %%here need to insert PROCESSNAME, PROTDATE%%

                  );


      t was giving me error:


      PL/SQL: ORA-22905: cannot access rows from a non-nested table item

      so i enclosed sparql query into single quotes.


      The code is as follows:

      declare

      type c_type is REF CURSOR;

      cur c_type;

      v_process varchar2(200);

      v_pdate varchar2(200);

       

      begin

       

      open cur for

             ' SELECT processName,  MAX(NVL(protStartDate, protCreateDate)) AS protDate   <-- it's complaining about this being too long identifier, i think...

              FROM TABLE

                (SEM_MATCH (

                          "{

                              ?ipc rdf:type s:Protocol .

                              ?ipc s:protocolNumber ?protNum .

                              ?ipc s:protocolCreateDate ?protCreateDate .

                              OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

                              ?ipra rdf:type s:ProcessAggregate .

                              ?ipra s:hasProtocol ?iprot .

                              ?iprot s:protocolNumber ?protNum .

                              ?ipra s:processAggregateProcess ?processName.

                          }",SEM_Models("XCOMPASS", "XPROCESS"),NULL,    

                SEM_ALIASES(SEM_ALIAS("","http://VISION/Data/SEMANTIC#"),

                SEM_ALIAS("s", "http://VISION/DataSource/SEMANTIC#")),NULL))

                 Group by processName';  

      loop

      fetch cur into v_process, v_pdate;

      exit when cur%NOTFOUND;

      --here I need to insert v_process , v_pdate into my table along with other values...

      dbms_output.put_line('values for process and prod_date are: ' || v_process || v_pdate );

      end loop;

                

      close cur;

       

      end;

      /

      exit;

       

       

      Now, I get an error:

       

      ORA-00972: identifier is too long

       

      Does anyone know way around this?

        • 1. Re: Using Cursor and FOR LOOP to INSERT the data into table
          marc980813

          Hi,

            I tested something similar with insert into select  and it worked fine :

           

          insert into t_countries(ID,CITY ,POPULATION, DESCRIPTION, located, insdate )

          SELECT 1 id, city, o , city||' is a nice city' description,  max(nvl(locatedAt,'unknown')) as located,

            SYSTIMESTAMP

            FROM TABLE(SEM_MATCH(

              '{GRAPH :gCH {<http://www.semwebtech.org/mondial/10/countries/CH/> :hasCity ?cityID .

                 ?cityID :name ?city .

                 OPTIONAL{?cityID :locatedAt ?locatedAt .}

                 ?cityID :population ?o .

                  } }',

              SEM_Models('VIRT_MODEL_MONDIAL'),

              SEM_Rulebases(null),

              SEM_ALIASES(SEM_ALIAS('','http://www.semwebtech.org/mondial/10/meta#'),

              SEM_ALIAS('prv','http://www.semwebtech.org/mondial/10/countries/CH/provinces/')

              ),

              null))

              group by city,o

              order by city;

           

          Or with execute immediate :

           

          declare

            v_country varchar2(200) :='http://www.semwebtech.org/mondial/10/countries/F/';

            v_text varchar2(2000);

          begin

          v_text := 'insert into t_countries(ID,CITY ,POPULATION, DESCRIPTION, located, insdate )

          SELECT 1 id, city, o , city||'' is a nice city'' description,  max(nvl(locatedAt,''unknown'')) as located,

            SYSTIMESTAMP

            FROM TABLE(SEM_MATCH(

              ''{<'||v_country||'> :hasCity ?cityID .

                 ?cityID :name ?city .

                 OPTIONAL{?cityID :locatedAt ?locatedAt .}

                 ?cityID :population ?o .

                  } '',

              SEM_Models(''VIRT_MODEL_MONDIAL''),

              SEM_Rulebases(null),

              SEM_ALIASES(SEM_ALIAS('''',''http://www.semwebtech.org/mondial/10/meta#'') ),

              null))

              group by city,o

              order by city';

              dbms_output.put_line(v_text);

              delete from t_countries;

              execute immediate v_text ;

              commit;

          end;

          /

          Marc

          • 2. Re: Using Cursor and FOR LOOP to INSERT the data into table
            alwu-Oracle

            Hi,

             

            Looks like there was a mis-use of quotes. Please cut & paste the following PL/SQL and run it in your environment.

            Note that '' in the following snippet is actually two single quotes.

             

            declare

            type c_type is REF CURSOR;

            cur c_type;

            v_process varchar2(200);

            v_pdate varchar2(200);

            begin

            open cur for

                   ' SELECT processName,  MAX(NVL(protStartDate, protCreateDate)) AS protDate

                    FROM TABLE

                      (SEM_MATCH (

                                ''{

                                    ?ipc rdf:type s:Protocol .

                                    ?ipc s:protocolNumber ?protNum .

                                    ?ipc s:protocolCreateDate ?protCreateDate .

                                    OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

                                    ?ipra rdf:type s:ProcessAggregate .

                                    ?ipra s:hasProtocol ?iprot .

                                    ?iprot s:protocolNumber ?protNum .

                                    ?ipra s:processAggregateProcess ?processName.

                                }'',SEM_Models(''XCOMPASS'', ''XPROCESS''),NULL,   

                      SEM_ALIASES(SEM_ALIAS('''',''http://VISION/Data/SEMANTIC#''),

                      SEM_ALIAS(''s'', ''http://VISION/DataSource/SEMANTIC#'')),NULL))

                       Group by processName'; 

            loop

            fetch cur into v_process, v_pdate;

            exit when cur%NOTFOUND;

            --here I need to insert v_process , v_pdate into my table along with other values...

            dbms_output.put_line('values for process and prod_date are: ' || v_process || v_pdate );

            end loop;

            close cur;

            end;

            /

             

             

            Hope it helps,

             

            Zhe