How to Bind Variable using Query String

CREATE OR REPLACE FUNCTION XX_GET_VALUE_F (p_person_id      NUMBER,

                                           p_column_name    VARCHAR2)

   RETURN VARCHAR2

IS

   l_column_value   VARCHAR2 (2000) := NULL;

   l_query_string   VARCHAR2 (2000) := NULL;

BEGIN

   l_query_string :=

         'declare

   l_column_value varchar2(2000);

   begin

                select '

      || p_column_name

      || ' into :l_column_val from per_people_x where person_id =  '

      || p_person_id

      || ';'

      || ' end;';

   EXECUTE IMMEDIATE l_query_string USING OUT l_column_value;

   RETURN l_column_value;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END;

select XX_GET_VALUE_F(70811,'LAST_NAME') from dual

Get Node value from Transaction Document using HR_XML_UTIL

CREATE OR REPLACE FUNCTION get_xml_node_value_f (

   p_transaction_id       IN NUMBER,

   p_desired_node_value   IN VARCHAR2,

   p_xpath                IN VARCHAR2,

   p_eo_name              IN VARCHAR DEFAULT NULL)

   RETURN VARCHAR2

AS

   l_xml_node_value   VARCHAR2 (32767) := NULL;

BEGIN

   l_xml_node_value :=

      apps.hr_xml_util.

       get_node_value (p_transaction_id       => p_transaction_id,

                       p_desired_node_value   => p_desired_node_value,

                       p_xpath                => p_xpath,

                       p_eo_name              => p_eo_name);

   RETURN l_xml_node_value;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END get_xml_node_value_f;

Example on how to Extract Value:

select get_xml_node_value_f(106003,'DateEnd','//PerAbsenceAttendancesEORow') from dual

Global Temporary Table in Oracle

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant once commit is fired or the session has been aborted abruptly. 

“ON COMMIT DELETE ROWS”

This statement is passed implicitly once the global temporary table is created.

Syntax:

CREATE GLOBAL TEMPORARY TABLE TEMP1 (eid NUMBER)

ON COMMIT DELETE ROWS -- Not Mandatory to Pass as this is been passed implicitly by Oracle

“ON COMMIT PRESERVE ROWS”

This statement keeps the records intact even when the transaction has been committed.

Syntax:

CREATE GLOBAL TEMPORARY TABLE TEMP2 (eid NUMBER)

ON COMMIT PRESERVE ROWS

Example:

CREATE GLOBAL TEMPORARY TABLE g1

(

   eid     NUMBER,

   ename   VARCHAR2 (100)

);

INSERT INTO g1

     VALUES (1, 'BIJOY');

INSERT INTO g1

     VALUES (2, 'HELLO123');

INSERT INTO g1

     VALUES (3, 'NEETU');

select * from g1

1	BIJOY
2	HELLO123
3	NEETU

commit; 

The moment one commits, records are deleted from the temporary table and memory is released!!