A unique value that look you like a list of numbers, is not actually a list of numbers but a single character value. The first thing is that you need an appropriate data type for storing a list of numbers. I would like to pass a list of values to a pl/sql function where the list will be used in an IN clause. I can't find an example to do based on pl/sql, but it seems feasible. Ideally, I would like to do the following:ĬREATE or REPLACE FUNCTION (dept_list in varchar2) Pass a list of values to a pl/sql function I use it because that's what dbms_utility.lname_array uses. I don't know if the Varchar2 (4000) really needs to be which is great. When you are prompted, provide the list of values separated by a single comma without any extra spaces. Select * from table( comma_to_table( :BNDS )) Now you can write a query, say for pt, as follows, and have executed statement ask the value list in the clause as a single binding variable: select * TYPE bind_tab_typ AS TABLE OF VARCHAR2(4000) įUNCTION comma_to_table(iv_raw IN VARCHAR2)ĭbms_ma_to_table(list => iv_raw First, add the following to your schema: create or replace I'll introduce you to a list of characters, so you'll have to tweak it for other types of data. Your needs for a invite only bind to the value in the clause list, I think I have a possible solution. Thank you for trying the SQL and PL/SQL instance before coming here - it was definitely the right approach, and you've got some very good suggestions there. You have an idea on how I can get a variable string binding (such as 1, 2, 10) in a set of NUMBER or VARCHAR2? This way I would be able to launch a standard query in SQL Developer to test my application.Ĭan someone tell me what is the best approach to this? With the UDT, the interface request always variable binding standard. When we start the application, a pop up asking for a value of the variable binding. In SQL developer, it is easy to test a query using the standard binding variable.
#Variables in oracle sql developer how to#
I'm learning how to do this in SQL, as well as the use of UDT in this thread: How to move a list as a bind variable?īut with this solution, I've lost nice SQL Developer user interface.
"īut with this solution, I lost all the benefit of the use of bound variables (analysis hard against soft parse, possibility of SQL injection, etc.). The above query will work well with"& prmRegionID = 2" and with "& prmRegionID = 2, 3". I know that I can replace ": prmRegionID" by a proxy '& prmRegionID '. The problem is that I can't find how to set ": prmRegionID = 2, 3. The following query in SQL Developer so work I put ": prmRegionID = 2. How can I pass a list as a bind variable in SQL Developer?
How to pass a list as a bind variable in SQL Developer?
0 kommentar(er)
