Oracle How to Dynamicaly change argument/parameter in function with loop

Question

How to dynamicaly change argument/parameter in pipeline function with loop? Here is the example.

CREATE OR REPLACE FUNCTION TESTES(
P_VALUE1 VARCHAR2,
P_VALUE2 VARCHAR2,
P_VALUE3  VARCHAR2 
)RETURN VARCHAR2

PIPELINED
AS
L_DYNM VARCHAR2(1000);
L_QUERY VARCHAR2(1000);
L_RETURN VARCHAR2(1000);

BEGIN
FOR i IN 1..3 LOOP
    L_DYNM := 'P_VALUE' || i || ',';
END LOOP;

L_QUERY := 'SELECT' || L_DYNM ||'FROM DUAL';


/* SELECT SME_PACE.SYS_REGEX_DEBIT(P_VALUE) INTO L_RETURN FROM DUAL; */
FOR V_REC IN (
    EXECUTE IMMEDIATE L_QUERY
        INTO L_RETURN
)
LOOP
    PIPE ROW (
        OBJECT_TESTES(
            V_REC.FIELD1, V_REC.FIELD2, V_REC.FIELD3
    );
END LOOP;
END;

So what i want to try to do actually is run SELECT P_VALUE1, P_VALUE2, P_VALUE3 From dual Dynamically and use it on pipeline row.

Theres a reason why i have to do it like that, i need an answer is it possible doing it like this? so i can apply it on my project.

Thanks

Do you literally need just those three columns? Or are the number of columns returned potentially different each time? — Jon Heller
– Jon Heller, Commented Mar 9, 2020 at 23:47

MT0 · Accepted Answer · 2020-03-09 12:04:51Z

You have lots of inconsistencies in your function:

The return type is VARCHAR2 but you are trying to use a PIPELINE that would return a collection.
You are overwriting the value of L_DYNM each iteration of the loop.
You are trying to select three dynamic column names from the DUAL table when it only has a single DUMMY column.

Then you have syntax errors:

Unmatched closing brackets.
EXECUTE IMMEDIATE inside a FOR statement.

You probably want something like:

CREATE TYPE object_testes IS OBJECT(
  a VARCHAR2(100),
  b VARCHAR2(100),
  c VARCHAR2(100)
);

CREATE TYPE object_testes_table IS TABLE OF object_testes;

CREATE OR REPLACE FUNCTION TESTES(
  P_VALUE1 VARCHAR2,
  P_VALUE2 VARCHAR2,
  P_VALUE3  VARCHAR2 
) RETURN object_testes_table PIPELINED
AS
  p_cur SYS_REFCURSOR;
  p_sql VARCHAR2(4000);
  p_field1 VARCHAR2(100);
  p_field2 VARCHAR2(100);
  p_field3 VARCHAR2(100);
BEGIN
  p_sql := 'SELECT ' || DBMS_ASSERT.ENQUOTE_NAME( p_value1 ) || ', '
                     || DBMS_ASSERT.ENQUOTE_NAME( p_value2 ) || ', '
                     || DBMS_ASSERT.ENQUOTE_NAME( p_value3 ) || ' '
           || 'FROM DUAL';

  OPEN p_cur FOR p_sql;

  LOOP
    FETCH p_cur INTO p_field1, p_field2, p_field3;
    EXIT WHEN p_cur%NOTFOUND;
    PIPE ROW (
      OBJECT_TESTES(
        p_field1,
        p_field2,
        p_field3
      )
    );
  END LOOP;

  CLOSE p_cur;
END;
/

Then:

SELECT * FROM TABLE( testes( 'DUMMY', 'DUMMY', 'DUMMY' ));

Outputs:

A  | B  | C 
:- | :- | :-
X  | X  | X

db<>fiddle here

Naveen Kumar · Accepted Answer · 2020-03-09 11:55:03Z

0

Try the following way. This will do the same thing but without using select P_Value1 dual.

Create Object:

CREATE TYPE sample_record AS OBJECT (P_VALUE1 VARCHAR2(10),
P_VALUE2  VARCHAR2(10),
P_VALUE3  VARCHAR2(10)
);/

Then Create array of created object.

CREATE TYPE sample_records IS TABLE OF sample_record; /

Function to convert object into Pipe Row:

CREATE OR REPLACE FUNCTION test (P_VALUE1 VARCHAR2,P_VALUE2 VARCHAR2,P_VALUE3  VARCHAR2) 
RETURN sample_records PIPELINED AS
     BEGIN
               PIPE ROW(sample_record(P_VALUE1,P_VALUE2,P_VALUE3));    
     RETURN;
END;
/

Then call the above function in the following way. You call it in the loop also:

select * from table(test('A','B','C'));

answered Mar 9, 2020 at 11:55

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Collectives™ on Stack Overflow

Oracle How to Dynamicaly change argument/parameter in function with loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related