I'd like to try to execute this query via Oracle SQL Developer, but I always got the error message which is

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

DEFINE po_header_ids VARCHAR(1000) := '1,2,3';

    SELECT 
        PHA.SEGMENT1
    FROM 
        PO.PO_HEADERS_ALL       PHA
    WHERE     1=1
        AND (
            :po_header_ids = 0 OR 
            :po_header_ids IS NULL OR
            PHA.PO_HEADER_ID IN (
                SELECT regexp_substr(:po_header_ids,'[^,] ',1,level)
                FROM dual CONNECT BY
                regexp_substr(:po_header_ids ,'[^,] ',1,level) IS NOT NULL
            )
        )

parameters which are entered in the oracle sql deverloper

Can someone tell me which part is wrong?

Thank you.

Here's the query that I executed successfully and the data type of the PO_HEADER_ID

PO_HEADER_ID Data Type

DEFINE po_header_ids varchar(1000);
SELECT regexp_substr(:po_header_ids,'[^,] ',1,level) 
FROM dual CONNECT BY
regexp_substr(:po_header_ids,'[^,] ',1,level) IS NOT NULL

Output of the above query

CodePudding user response：

• DEFINE declares a substituition variable which will be prefixed by & in the code and is processed in the client application (i.e. in SQL Developer) as if a find-replace occurs on the statement.
• VARIABLE declares a bind-variable which will be prefixed by : in the code and is processed by the SQL engine on the server.

You also need to change: :po_header_ids = 0 to :po_header_ids = '0' as the bind variable is a string and not a number.

So you want:

VARIABLE po_header_ids VARCHAR2(1000);

BEGIN
  :po_header_id := '1,2,3';
END;
/

SELECT PHA.SEGMENT1
FROM   PO.PO_HEADERS_ALL       PHA
WHERE  :po_header_ids = '0'
OR     :po_header_ids IS NULL
OR     PHA.PO_HEADER_ID IN (
         SELECT TO_NUMBER(regexp_substr(:po_header_ids,'[^,] ',1,level))
         FROM   dual
         CONNECT BY
                regexp_substr(:po_header_ids ,'[^,] ',1,level) IS NOT NULL
       );

db<>fiddle here