8 Replies Latest reply on Mar 31, 2014 1:23 PM by AAG

    Global Temp Table in PL/SQL With XML

    AAG

      Hi,

       

      Feel like this is something strange i am seeing or may be i am missing something basic.

       

      Step 1: Create a global Temp table which is required to be transaction specific.

      create global Temporary table Temp01

      (

      TICKET_ID    NUMBER

      ,REGION Varchar2(10)

      ,YEARR NUMBER

      ,CO_ID    VARCHAR2(10)

      ) ON COMMIT DELETE ROWS;

       

      Step2:

      My XML which is going in as a parameter to a new function.

      <TICKET>

          <TICKET_ID>38498051</TICKET_ID>

          <REGION>USA</REGION>

          <YEAR>2014</YEAR>

          <CO_ID>XYZ123</CO_ID>

      </TICKET>

       

      Step 3: Create a Stand Alone Function:

       

      --drop function aagarwal.wr_creation;

      create or replace FUNCTION XML_FUNC

              (

              ret_msg    out varchar2,

              p_xmlval IN varchar2

              )

              RETURN varchar2

      is

          l_xmlval  varchar2(4000):= p_xmlval;

          V_CO_ID   VARCHAR2(10);

          V_CODE       VARCHAR2(10);

      BEGIN

       

          BEGIN

               INSERT INTO Temp01

              (

                  TICKET_ID,

                  REGION,

                  YEARR,

                  CO_ID

              )

              SELECT

                  EXTRACTVALUE(XMLTYPE(p_xmlval), '/TICKET/TICKET_ID') ID,

                  EXTRACTVALUE(XMLTYPE(p_xmlval), '/TICKET/REGION') REGION,

                   EXTRACTVALUE(XMLTYPE(p_xmlval), '/TICKET/YEAR') YEARR,

                  EXTRACTVALUE(XMLTYPE(p_xmlval), '/TICKET/CO_ID') CO_ID

              FROM DUAL;

          ret_msg:='SUCCESS';

          --SELECT CO_ID INTO V_CO_ID FROM aagarwal.TEMP_STAGE_WR;

          -- return ret_msg;

             EXCEPTION

              WHEN OTHERS THEN

                  ret_msg:= sqlerrm;

                   return ret_msg;

          END;

          BEGIN

          SELECT CO_ID INTO V_CO_ID FROM Temp01;

          /* MERGE INTO site se

             USING aagarwal.TEMP01 T

          ON (T.co_id = se.code AND se.type_nm = 'TYPE' and se.src_nm = T.region)

              WHEN NOT MATCHED THEN

             INSERT (ID, SRCNM, CODE, TYPENM)

           VALUES(SHARED_SEQ.NEXTVAL, T.region , T.co_id, 'TYPE');

          --commit; */

          return ret_msg || ' AS ' || v_co_id;

       

         END;

       

      END;

      /

       

      Done - Function Created.

       

      NOTE: MERGE Statement is blocked and so Function was created smoothly.

       

      Step 4: Call the Function

      declare

      l_out varchar2(50);

      l_outr varchar2(50);

      p_xml XMLTYPE;

      begin

          l_outr:= XML_FUNC(l_out, '<TICKET>

          <TICKET_ID>38498051</TICKET_ID>

          <REGION>USA</REGION>

          <YEAR>2014</YEAR>

          <CO_ID>XYZ123</CO_ID>

      </TICKET>');

          dbms_output.put_line(l_outr);

      end;

      /


       

      Step 5: Check the value being inserted into Temporary Table:


      select * from temp01;


      So Far So Good.

       

       

      THE PROBLEM:

      Now I want to tweek the above Function XML_FUNC by uncommenting MERGE Statement which leads me to a Error which is not derivable:

      i.e PL/SQL: ORA-00942: table or view does not exist at Line on MERGE statement pointing to Temp01 table.

       

      NOTE: I explicitly tested that Merge Statement (Both as running Stand Alone and also by calling via Anonymous PLSQL block) and its Working absolutely fine. And SITE table do exist.

       

      PS: I would be thankful, if there is any other better way to write this code ? I am not a regular PLSQL developer and so may practice writing code badly.

       

      Regards,

      AAG.

       

       

      Using 11.2.0.3:

        • 2. Re: Global Temp Table in PL/SQL With XML
          odie_63

          Who is the owner of :

          - TEMP01 table?

          - SITE table?

          - XML_FUNC function?

           

          You don't need that temp table anyway.

          • 3. Re: Global Temp Table in PL/SQL With XML
            AAG

            Thanks for looking into my problem.

             

            Owner of all these three objects is DBA.

             

            There is a requirement for me to use TEMP table. That is just part of the Function i am trying to accompolish. I will have to add business requirements more later.

             

            Can you please execute those same objects in your machine and see for the error and solution ? You can replace site table with any of your tables accordingly.

             

            Regards,

            • 4. Re: Global Temp Table in PL/SQL With XML
              AAG

              Any one ? I don't understand the mistake i am making.

              • 5. Re: Global Temp Table in PL/SQL With XML
                odie_63

                Owner of all these three objects is DBA.

                Are you sure?

                 

                Post the output of :

                select object_name, owner, object_type

                from all_objects

                where object_name in ('TEMP01', 'SITE', 'XML_FUNC');

                You have to grant explicit select privilege on the table to the owner of the function if the owners are different.

                 

                This works as expected for me (DEV user owns all 3 objects) :

                 

                Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

                Connected as dev

                 

                SQL>

                SQL> create global Temporary table Temp01 (

                  2    TICKET_ID  NUMBER

                  3  , REGION     Varchar2(10)

                  4  , YEAR       NUMBER

                  5  , CO_ID      VARCHAR2(10)

                  6  )

                  7  ON COMMIT DELETE ROWS;

                 

                Table created

                 

                SQL>

                SQL> create table site (

                  2    id     number

                  3  , srcnm  varchar2(10)

                  4  , code   varchar2(10)

                  5  , typenm varchar2(10)

                  6  );

                 

                Table created

                 

                SQL> create sequence shared_seq;

                 

                Sequence created

                 

                SQL>

                SQL>

                SQL> create or replace FUNCTION XML_FUNC (

                  2    p_xmlval IN varchar2

                  3  )

                  4  RETURN varchar2

                  5  is

                  6    l_xmlval  xmltype := xmltype(p_xmlval);

                  7  BEGIN

                  8 

                  9    INSERT INTO Temp01

                10    (

                11      TICKET_ID,

                12      REGION,

                13      YEAR,

                14      CO_ID

                15    )

                16    SELECT EXTRACTVALUE(l_xmlval, '/TICKET/TICKET_ID') ID,

                17           EXTRACTVALUE(l_xmlval, '/TICKET/REGION') REGION,

                18           EXTRACTVALUE(l_xmlval, '/TICKET/YEAR') YEAR,

                19           EXTRACTVALUE(l_xmlval, '/TICKET/CO_ID') CO_ID

                20    FROM DUAL;

                21 

                22    MERGE INTO site se

                23    USING TEMP01 T

                24    ON (

                25          T.co_id = se.code

                26      AND se.typenm = 'TYPE'

                27      and se.srcnm = T.region

                28    )

                29    WHEN NOT MATCHED THEN

                30      INSERT (ID, SRCNM, CODE, TYPENM)

                31      VALUES(SHARED_SEQ.NEXTVAL, T.region , T.co_id, 'TYPE');

                32 

                33    return 'SUCCESS';

                34 

                35  END;

                36  /

                 

                Function created

                 

                SQL>

                SQL>

                SQL> set serveroutput on

                SQL>

                SQL>

                SQL> declare

                  2 

                  3    l_outr varchar2(50);

                  4 

                  5  begin

                  6 

                  7    l_outr:= XML_FUNC('<TICKET>

                  8      <TICKET_ID>38498051</TICKET_ID>

                  9      <REGION>USA</REGION>

                10      <YEAR>2014</YEAR>

                11      <CO_ID>XYZ123</CO_ID>

                12    </TICKET>');

                13 

                14    dbms_output.put_line(l_outr);

                15 

                16  end;

                17  /

                 

                SUCCESS

                 

                PL/SQL procedure successfully completed

                 

                SQL> select * from site;

                 

                        ID SRCNM      CODE       TYPENM

                ---------- ---------- ---------- ----------

                         1 USA        XYZ123     TYPE

                 

                • 6. Re: Global Temp Table in PL/SQL With XML
                  AAG

                  Thanks for your reply.

                   

                  After giving explicit Privileges (SELECT and INSERT) on site table it worked.

                  I have a high level privileges and roles to which select and insert on site table were assigned already.

                  I will have to dig a little deep to find out why my privs was not working or misplaced.

                   

                  Thanks for your help though.


                  Regards,

                  • 7. Re: Global Temp Table in PL/SQL With XML
                    odie_63

                    I will have to dig a little deep to find out why my privs was not working or misplaced.

                    When a stored program is compiled using owner's rights ("AUTHID DEFINER" option, the default), privileges granted through roles don't apply.

                     

                    PL/SQL Subprograms

                    1 person found this helpful
                    • 8. Re: Global Temp Table in PL/SQL With XML
                      AAG

                      Thanks , that helps me understand !