0

I have written a Oracle query in Javascript file which will return the last_name,first_name,ID,Dept of employee based on the parameters it receives. Here is the Query:

var sqlstr="Select last_name,first_name,ID,Dept from employee where dept=? and ID=?";

var sql= new SQL(connection,sqlstr);
sql[1]=dept;
sql[2]=ID;

The list is :

 var dept=["CS","maths","english"]; 
 var ID=[1,2,3,4,5];

Right now I have hard coded the values of both the list but these list are dynamic and they can have different number of values like dept here are 3 but there can be 4 or 5 or more, same is with ID list. I am receiving an error of Unsupported type because I am passing a list to SQL query. I want to execute this sql queries for all the values in the lists, so how can I do this?

markalex
  • 8,623
  • 2
  • 7
  • 32
Johnst33
  • 55
  • 5
  • 1
    What language do you use? You may construct list of values by using Oracle's buitlin nested table types `sys.odcilist` and then bind variables of such type. But you'll need to `select * from table(:bindvar)` and test for `in` – astentx Apr 19 '23 at 12:32
  • Second, generally you can't simply pass list like that for comparison. Assuming you expect behavior of `dept in ('cs', 'maths')` you'll need to generate query with parameter for every value of array: `dept in (?, ?)` – markalex Apr 19 '23 at 12:33
  • We'll need to know your programming language. Reason being, is that you'll have to construct valid SQL directly using logic in your programming language first... so for example, 1. check if variable is a list, if it is, make a string that is "IN(" then loop through all values and append 'CS','maths','english' then append ")" – Josh Apr 19 '23 at 12:33
  • 2
    [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause/337792#337792), [Pass List Into SQL Parameter](https://stackoverflow.com/questions/22176471/pass-liststring-into-sql-parameter) – Ponder Stibbons Apr 19 '23 at 12:35
  • Just loop through your array and execute the SQL once for each value. While there are way to pass in non-scalar values, it isn't worth the trouble and parsing overhead. – Paul W Apr 19 '23 at 12:47
  • @PaulW, you think that 15 separate request for example in question are better that overhead of passing multiple parameters? – markalex Apr 19 '23 at 12:50
  • Absolutely. Lists create a headache for the shared pool, especially variable-sized lists. 15 requests should be milliseconds. Don't complicate it. – Paul W Apr 19 '23 at 12:52
  • @markalex creating a loop will create an overhead is there any other metod? – Johnst33 Apr 19 '23 at 13:03
  • @Johnst33, surely there are other methods: look into links provided by Ponder. But I'm not familiar with requests into db straight from JS, so you'll need to wait answer for people with appropriate expertise. – markalex Apr 19 '23 at 13:09
  • @Johnst33 the loop is the simplest way to do this. Try it. Assuming your table is properly indexed I guarantee you won't even notice it it'll be so fast. Just make sure you aren't closing and opening your connection each time. Keep the same connection open and loop only on executing the SQL. 15 records isn't even a blink of an eye. Millions of records would be different, and require different solutions. – Paul W Apr 19 '23 at 13:22
  • Oracle has a lot of examples for almost any aspect of development for each feature, so it would be good to check the docs first. [`node-oracledb`](https://node-oracledb.readthedocs.io/en/latest/user_guide/bind.html#binding-multiple-values-to-a-sql-where-in-clause) has special section about how to bind an `in` list with the host array, which also covers cases when you may want to select dynamic in `col in (?,...,?)`, multiple single statements or collection variable. – astentx Apr 19 '23 at 13:44
  • Can you please tell me from where this :bv is coming in ```WHERE first_name IN (:bv)```? @astentx – Johnst33 Apr 19 '23 at 14:37
  • `const inlist = ['Christopher', 'Hazel', 'Samuel']; const binds = { bv: { type: 'SYS.ODCIVARCHAR2LIST', val: inlist } }; const result = await connection.execute(sql, binds, options);` It's passed at `execute` call – astentx Apr 19 '23 at 14:53

0 Answers0