SELECT as many rows as indicated by column value

This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.


CREATE TABLE S
(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;

Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;

SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);

Using Dynamic Sql to work around PL/SQL bugs

A coworker encountered this problem. We search the Internet for a solution, but nothing was found. I am hoping that by posting this, others having the same problem will quickly find a solution. Furthermore, this example illustrates a more general principle which is that sometimes things work in SQL which do not work in PL/SQL and a simple solution is to use dynamic sql.

This problem is encountered when trying to access an NVARCHAR2 using a TABLE function in PL/SQL.

ORA-12714: invalid national character set specified



Originally written as:

FOR c IN ( SELECT * FROM TABLE (p_question_tab_i) ) LOOP

Processing….

END LOOP;

Raises error: ORA-12714: invalid national character set specified

Then re-written using dynamic SQL:

OPEN que_cursor FOR 'SELECT * FROM TABLE (:question_tab)'

USING p_question_tab_i;

The above example works correctly…

question_tab TABLE OF QUESTION_OBJ
is
SELECT_TYPE VARCHAR2(10)
DISPLAY_TEXT NVARCHAR2(4000)
PROMPT_TYPE_CODE VARCHAR2(50)

Technically, this is not a PL/SQL bug. It is, however, a feature that is supported in SQL but not PL/SQL. This often happens with new SQL features where PL/SQL has not 'caught up' yet.