I'm trying to debug a piece of code that ChatGPT suggested but I'm not sure if its even possible. I get no data found error when running this:
SELECT CASE
WHEN v_column_no = 1 THEN LASTNAMES1
WHEN v_column_no = 2 THEN LASTNAMES2
WHEN v_column_no = 3 THEN LASTNAMES3
WHEN v_column_no = 4 THEN LASTNAMES4
WHEN v_column_no = 5 THEN LASTNAMES5
WHEN v_column_no = 6 THEN LASTNAMES6
END
INTO v_last_name
FROM EXTERNAL_LAST_NAMES_ALL
WHERE LASTNAME_ID = TRUNC(DBMS_RANDOM.VALUE(1,5171));
I was expecting to get back one random value from one random column (the table has 5170 rows). But I get an error.
Is there an elegant solution to randomly selecting a column in PL/SQL?
SQL demands that selected columns are exact - you can not use selection of dynamicaly constructed column list.
Is there an elegant solution to randomly selecting a column in PL/SQL?
1. You can use dynamic sql commands with Execute Immediate:
-- S a m p l e D a t a :
Create Table EXTERNAL_LAST_NAMES_ALL
( LASTNAMES1 VarChar2(24), LASTNAMES2 VarChar2(24), LASTNAMES3 VarChar2(24),
LASTNAMES4 VarChar2(24), LASTNAMES5 VarChar2(24), LASTNAMES6 VarChar2(24),
LASTNAME_ID Number(6)
);
Insert Into EXTERNAL_LAST_NAMES_ALL
Select 'Smith', 'Jones', 'Robertson', 'Jordan', 'Mandel', 'Doe', 1 From Dual Union All
Select 'Doe', 'Smith', 'Jones', 'Robertson', 'Jordan', 'Mandel', 2 From Dual Union All
Select 'Mandel', 'Doe', 'Smith', 'Jones', 'Robertson', 'Jordan', 3 From Dual;
... code that returns 3 randomly selected columns from 3 random ID rows:
SET SERVEROUTPUT ON;
Declare
v_column_no Number(6);
v_last_name EXTERNAL_LAST_NAMES_ALL.LASTNAMES1%TYPE;
v_last_name_id EXTERNAL_LAST_NAMES_ALL.LASTNAME_ID%TYPE;
v_column_name VarChar2(32);
v_sql VarChar2(512);
Begin
For i In 1..3 Loop
v_column_no := TRUNC( DBMS_RANDOM.VALUE( 1, 6 ) );
v_last_name_id := TRUNC( DBMS_RANDOM.VALUE( 1, 3 ) );
--
Select Case When v_column_no = 1 Then 'LASTNAMES1'
When v_column_no = 2 Then 'LASTNAMES2'
When v_column_no = 3 Then 'LASTNAMES3'
When v_column_no = 4 Then 'LASTNAMES4'
When v_column_no = 5 Then 'LASTNAMES5'
When v_column_no = 6 Then 'LASTNAMES6'
End "RANDOM_COL_NAME"
INTO v_column_name
From Dual;
--
v_sql := 'Select ' || v_column_name || ' From EXTERNAL_LAST_NAMES_ALL Where LASTNAME_ID = ' || v_last_name_id;
Execute Immediate v_sql INTO v_last_name;
DBMS_OUTPUT.Put_Line( 'Pass No.: ' || i || Chr(10) ||
'Random column no: ' || v_column_no || Chr(10) ||
'Random lastname id: ' || v_last_name_id || Chr(10) ||
'SQL Statement: ' || v_sql || Chr(10) ||
'Selected lastname: ' || v_last_name || Chr(10) ||
'___________________________________________________');
End Loop;
End;
/
/* R e s u l t :
Pass No.: 1
Random column no: 1
Random lastname id: 1
SQL Statement: Select LASTNAMES1 From EXTERNAL_LAST_NAMES_ALL Where LASTNAME_ID = 1
Selected lastname: Smith
___________________________________________________
Pass No.: 2
Random column no: 5
Random lastname id: 2
SQL Statement: Select LASTNAMES5 From EXTERNAL_LAST_NAMES_ALL Where LASTNAME_ID = 2
Selected lastname: Jordan
___________________________________________________
Pass No.: 3
Random column no: 3
Random lastname id: 2
SQL Statement: Select LASTNAMES3 From EXTERNAL_LAST_NAMES_ALL Where LASTNAME_ID = 2
Selected lastname: Jones
___________________________________________________
PL/SQL procedure successfully completed. */
NOTE:
Instead of Case When selection From Dual you can select the column name from all_tab_columns table, but check the COLUMN_IDs - they are from 1 to n in order of definition in Create Table command. That is why I created ID column last in Sample Data - so lastnames 1 to 6 are column_id 1 to 6 in all_tab_columns.
Select COLUMN_NAME INTO v_column_name
From all_tab_columns
Where TABLE_NAME = 'EXTERNAL_LAST_NAMES_ALL' And
COLUMN_ID = v_column_no;
2. Using SYS_REFCURSOR
SET SERVEROUTPUT ON;
Declare
c SYS_REFCURSOR;
v_column_no Number(6);
v_column_name VarChar(32);
v_last_name_id EXTERNAL_LAST_NAMES_ALL.LASTNAME_ID%TYPE;
v_last_name EXTERNAL_LAST_NAMES_ALL.LASTNAMES1%TYPE;
v_sql VarChar2(512);
Begin
FOR i IN 1..3 LOOP
v_column_no := TRUNC( DBMS_RANDOM.VALUE( 1, 6 ) );
v_last_name_id := TRUNC( DBMS_RANDOM.VALUE( 1, 3 ) );
--
Select COLUMN_NAME Into v_column_name
From all_tab_columns
Where TABLE_NAME = 'EXTERNAL_LAST_NAMES_ALL' And
COLUMN_ID = v_column_no;
--
v_sql := 'Select ' || v_column_name || ' as rand_lastname From EXTERNAL_LAST_NAMES_ALL Where LASTNAME_ID = ' || v_last_name_id;
OPEN c FOR v_sql;
FETCH c INTO v_last_name;
DBMS_OUTPUT.Put_Line( 'Pass No.: ' || i || Chr(10) ||
'Random column no: ' || v_column_no || Chr(10) ||
'Random lastname id: ' || v_last_name_id || Chr(10) ||
'Selected lastname: ' || v_last_name || Chr(10) ||
'___________________________________________________');
CLOSE c;
END LOOP;
End;
/
/* R e s u l t :
Pass No.: 1
Random column no: 2
Random lastname id: 1
Selected lastname: Jones
___________________________________________________
Pass No.: 2
Random column no: 4
Random lastname id: 1
Selected lastname: Jordan
___________________________________________________
Pass No.: 3
Random column no: 5
Random lastname id: 1
Selected lastname: Mandel
___________________________________________________
PL/SQL procedure successfully completed. */