Oracle - Union statement alternative

i am new to oracle and I have a select statement with into v_sql.

SELECT    'select distinct mi.*, v.pa_id, v.agent from ( '
             || view_source
        INTO v_sql
        FROM tablename1 WHERE pk_pid = p_Process;

in the above statement, i really don't understand what v_sql holds? and view_source is the column data(holds the CLOB) and is that possible to add another CLOB column to the v_sql on the same statement? like view_source_Process?

SELECT    'select distinct mi.*, v.pa_id, v.agent from ( '
                 || view_source union view_source_process
            INTO v_sql
            FROM tablename1 WHERE pk_pid = p_Process;

and below code is the continuous part for the v_sql:

v_sql :=
               v_sql
            || ') mi join ta_people_suppliers ps on (ps.id = '''
            || p_requestor
            || ''' and ps.supplierid = mi.supplierid )
                            join ta_people_states s on (s.id = ps.id and s.perspective = ''MAKE'')
                            join vw_items_people v on (v.pk_itemid = mi.pk_itemid)
                            where mi.state = s.state';

         OPEN p_Items FOR v_sql;

Solution

Your code:

SELECT    'select distinct mi.*, v.pa_id, v.agent from ( '
          || view_source
INTO v_sql
FROM tablename1 WHERE pk_pid = p_Process;

This PL/SQL command puts a value into variable declared as v_sql.
Someone is creating a dynamic sql statement storing it into v_sql variable The command is selecting FROM tablename1. Characters || are used to concatenate strings ('abc' || 'def' results as 'abcdef'). The select is concatenating fixed string (within single quotes) with the value of view_source column in tablename1. The "view_source" is, most likely, the column name in table named "tablename1". It could be some variable or parameter in PL/SQL but then it would be senseless to select it from the table tablename1. Since dynamic sql (fixed part) ends with '... from ( ' it is obvious that it should be followed by another sql statement as a subquery. That subquery sql is stored in column view_source in tabale tablename1. Lets suppose that that value is - for pk_pid = p_Process (p_Process is probably a parameter or a variable)

'Select pk_itemid, supplierid, state, some_other_column From tablename2'

Then the result of your command would be that value of variable v_sql now is following string:

select distinct mi.*, v.pa_id, v.agent from ( Select pk_itemid, supplierid, state, some_other_column From tablename2

This sql command is incomplete and that should be completed with the next command in your code:

v_sql :=
               v_sql
            || ') mi join ta_people_suppliers ps on (ps.id = '''
            || p_requestor
            || ''' and ps.supplierid = mi.supplierid )
                            join ta_people_states s on (s.id = ps.id and s.perspective = ''MAKE'')
                            join vw_items_people v on (v.pk_itemid = mi.pk_itemid)
                            where mi.state = s.state';

         OPEN p_Items FOR v_sql;

The command is adding text to existing value of v_sql variable. Again, p_requestor is, most likely, another parameter (or variable). Lets say that its value is '1001'. With that assumption the command's concatenations results with the value of v_sql as below:

select distinct mi.*, v.pa_id, v.agent from ( Select pk_itemid, supplierid, state, some_other_column From tablename2) mi join ta_people_suppliers ps on (ps.id = '1001' and ps.supplierid = mi.supplierid )
                            join ta_people_states s on (s.id = ps.id and s.perspective = ''MAKE'')
                            join vw_items_people v on (v.pk_itemid = mi.pk_itemid)
                            where mi.state = s.state

Formated for readability it would be:

select  distinct mi.*, 
        v.pa_id, 
        v.agent 
from    ( Select pk_itemid, supplierid, state, some_other_column 
          From tablename2
        ) mi 
join    ta_people_suppliers ps on (ps.id = '1001' and ps.supplierid = mi.supplierid )
join    ta_people_states s on (s.id = ps.id and s.perspective = ''MAKE'')
join    vw_items_people v on (v.pk_itemid = mi.pk_itemid)
where   mi.state = s.state

Now you have a variable containing a dynamicaly generated sql statement that could be used further in your PL/SQL code.

Regarding UNION part of the question, you can not union column to a column but you can union one select statement to another such as:

Select 1 as A_NUMBER, 'A' as A_LETTER From Dual UNION ALL
Select 2 as A_NUMBER, 'B' as A_LETTER From Dual

/*  R e s u l t :
A_NUMBER  A_LETTER
--------  --------
       1  A
       2  B         */

You can CONCATENATE values of two columns using || as in your PL/SQL code or something like here:

WITH 
    tbl AS (Select 'John' as NAME, 'Doe' as SURNAME From Dual)
SELECT NAME || ' ' || SURNAME as NAME_SURNAME From tbl;

/*
NAME_SURNAME
------------
John Doe     */