Expert: Elliot Mak - 6/16/2009

Question
Greetings Elliot

Here is my requirement ..

I am given the "column_ID"s of a table. Using that i have to identify corresponding column_names (for the given column_ids) and have to query the detail table and concatenate the data in it. I would need to do in pl/sql programming.

For that, I am following the below approach.

FOR rec IN (SELECT column_name FROM user_tab_columns
WHERE table_name = 'PSD_TRANS_DTL'
AND column_id IN (SELECT column_id FROM tbl_pods_reproc_fields
WHERE region_code = 'US'AND column_id BETWEEN l_grp_start
AND l_grp_end))

LOOP
IF v_col_grp IS NULL
THEN
v_col_grp := rec.column_name;
ELSE
v_col_grp := v_col_grp || '||' || rec.column_name;
END IF;
END LOOP;

v_col_grp := v_col_grp || '||';
RETURN v_col_grp;
END prep_matr_col_grp;

BEGIN

v_col_group1 := prep_matr_col_grp (2, 73);
v_col_group2 := prep_matr_col_grp (74, 117);
v_col_group3 := prep_matr_col_grp (118, 147);
v_col_group4 := prep_matr_col_grp (148, 200);


/* Working on Original record */

v_orig_new := 'O';

SELECT v_col_group1 || 'End Of Compare' INTO orig_out1
FROM PSD_TRANS_DTL
WHERE sqdc_transaction_key = old_sqdc_tran_key;

END;
==========================
It is doing good till select the column_names and conatenating them. But not working where i am trying to select the data for those column_names from the actual detail table --
For ex - my
v_col_grp1 = column1||col2||col3||col4

select v_col_grp1 into orig_out from <table_name>

below value is getting passed to orig_out

orig_out = column1||col2||col3||col4

PN: I am the getting the actual value here into my variable. the script is treating it as a varchar and just returning them back to me.

Please suggest me a solution for this on how could i rectify this in my program.

Thanks in advance.
Uma

Answer
Hi Uma,

I have a little bit difficult time to understand the situation.  Here is what I did based on what understand to replicate what you did

1. create a table, all_experts_tbl with following
- column_id

CREATE TABLE all_experts_tbl
(column_id NUMBER);

2. Create another table
CREATE TABLE all_experts_main
(field_1    NUMBER
, field_2    NUMBER
, field_3    NUMBER
, field_4    NUMBER
, field_5    NUMBER
, field_6    NUMBER
, field_7    NUMBER
, field_8    NUMBER
, field_9    NUMBER
, field_10    NUMBER
);

1. create a table, all_experts_tbl with following
- column_id

CREATE TABLE all_experts_tbl
(column_id NUMBER);

2. Create another table
CREATE TABLE all_experts_main
(field_1    NUMBER
, field_2    NUMBER
, field_3    NUMBER
, field_4    NUMBER
, field_5    NUMBER
, field_6    NUMBER
, field_7    NUMBER
, field_8    NUMBER
, field_9    NUMBER
, field_10    NUMBER
);

3. populate the table with values
DECLARE
  l_number   NUMBER:=0;
BEGIN

  WHILE l_number <= 10
  LOOP
     INSERT INTO all_experts_tbl values (l_number);
     l_number := l_number + 1;
  END LOOP;
  
END;
/

4. create the function
CREATE OR REPLACE FUNCTION all_experts_func (in_start IN NUMBER, in_end IN NUMBER)
RETURN VARCHAR2 IS

  -- Declare cursor
  CURSOR c_main IS SELECT column_name
                    FROM   user_tab_columns
                    WHERE   table_name = 'ALL_EXPERTS_MAIN'
                    AND    column_id IN (   SELECT   column_id
                                         FROM      all_experts_tbl
                                         WHERE    column_id BETWEEN in_start and in_end);

  -- Declare Variable
  l_output_variable      VARCHAR2(128);                                          

BEGIN
  
  -- Set the variable to NULL
  l_output_variable := NULL;
  
  FOR rec IN c_main
  LOOP
     
        IF l_output_variable IS NULL
        THEN
           l_output_variable := rec.column_name;
        ELSE
           l_output_variable := l_output_variable || '||' || rec.column_name;
        
        END IF;
  END LOOP;

  l_output_variable := l_output_variable || '||';
  
  RETURN l_output_variable ;
  
END;
/

5. Create the procedure to mimic
CREATE OR REPLACE PROCEDURE all_experts_proc IS

  -- Declare Variables
  l_col_group1    VARCHAR2(100);
  l_output_var   VARCHAR2(100);

BEGIN

  l_col_group1 := all_experts_func(2,4);
  
  SELECT    l_col_group1 || 'End of Compare'
  INTO      l_output_var
  FROM      DUAL
  WHERE      1 = 1;
  
  DBMS_OUTPUT.PUT_LINE ('When the condition is true, l_output_var is ' || l_output_var);

  l_output_var := NULL;
  BEGIN
  
     SELECT    l_col_group1 || 'End of Compare'
     INTO      l_output_var
     FROM      DUAL
     WHERE      1 = 0;
  
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE ('When the where condition is false, l_output_var is ' || l_col_group1);
     END;            
  

END;
/

And I got the following results:
ALLEXPERTS (ALLEXPERTS) > set serveroutput on;
ALLEXPERTS (ALLEXPERTS) > execute all_experts_proc();
When the condition is true, l_output_var is FIELD_2||FIELD_3||FIELD_4||End of
Compare
When the where condition is false, l_output_var is FIELD_2||FIELD_3||FIELD_4||

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
ALLEXPERTS (ALLEXPERTS) >

Depending on the logic you have (that is not spell out in your case), if the WHERE sqdc_transaction_key = old_sqdc_tran_key condition is false, it may return the original variable value.  But then if that condition returns FALSE, then you should have an error message from Oracle because you do not have the exception handling.

Based on what I replicate, I was able to get the data the way you have expected (when the condition is TRUE).  But of course I did not see a major issue with your code, but since it is a snippet of it, I can tell if you are missing anything or not.

HOpe this helps, if you shall have additional questions, please don't hesitate to let me know.

Cheers
Elliot