Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Polymorphic Table Function with parentheses in partition by case not working?

zhang juntaoApr 16 2025 — edited Apr 16 2025

I was playing around with polymorphic table functions, meet a issue.

env: https://livesql.oracle.com/

init table function:

CREATE PACKAGE row_num_p IS
FUNCTION describe(tab IN OUT dbms_tf.table_t, 
ini NUMBER DEFAULT 1, 
inc NUMBER DEFAULT 1) 
RETURN dbms_tf.describe_t;

PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1);
END;

CREATE PACKAGE BODY row_num_p IS
FUNCTION describe(tab IN OUT dbms_tf.table_t, 
ini NUMBER DEFAULT 1, 
inc NUMBER DEFAULT 1) 
RETURN dbms_tf.describe_t AS
BEGIN
RETURN dbms_tf.describe_t(new_columns =>
dbms_tf.columns_new_t(1 =>
dbms_tf.column_metadata_t(name => 'ROW_ID', 
TYPE => dbms_tf.type_number)));
END;
PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1) IS
row_cnt CONSTANT PLS_INTEGER := dbms_tf.get_env().row_count;
rid NUMBER := ini;
col dbms_tf.tab_number_t;
BEGIN
dbms_tf.xstore_get('rid', rid);
FOR i IN 1 .. row_cnt LOOP col(i) := rid + inc*(i-1); END LOOP;
dbms_tf.put_col(1, col);
dbms_tf.xstore_set('rid', rid + inc*row_cnt);
END;

END;

CREATE FUNCTION row_num(tab TABLE,
ini NUMBER DEFAULT 1, 
inc NUMBER DEFAULT 1) 
RETURN TABLE
PIPELINED TABLE POLYMORPHIC USING row_num_p;	

this sql this working fine (case1):

SELECT deptno, ename, job, sal, row_id
FROM row_num (
	tab=> (scott.emp), 
	ini => (0), 
	inc => (0.25)
)
WHERE deptno IN (10, 30);

when I execute this sql (case2):

SELECT deptno, ename, job, sal, row_id
FROM row_num (
	tab=> (scott.emp partition by deptno), 
	ini => (0), 
	inc => (0.25)
)
WHERE deptno IN (10, 30);

which will return missing right parenthesis like:

ORA-00907: missing right parenthesis

https://docs.oracle.com/error-help/db/ora-00907/
00907. 00000 - "missing right parenthesis"
*Document: YES
*Cause: A left parenthesis was entered without a closing
right parenthesis, or there was unrecognized information in the
parentheses. All parentheses must be entered in pairs.
*Action: Correct the syntax and retry the statement.
Error at Line: 3 Column: 22

Show info

I am not sure if this is a improvment of Polymorphic Table Function Sytax?

Because case1 the arguments call with parentheses are working fine, why the case2 with parentheses (scott.emp partition by deptno)not supported?

Can this corner case be improved?

This post has been answered by Solomon Yakobson on Apr 16 2025
Jump to Answer
Comments
Post Details
Added on Apr 16 2025
5 comments
208 views