AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
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;
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);
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.
