Related questions

Multiline bind parameter

2022-05-04 10:15 Rima imported from Stackoverflow

I'm trying the following query from DBeaver (backend is oracle):

SELECT  * FROM mytable where mycolumn in (REPLACE( :req_id_list, CHR(13), ','))

when it prompts for the value of req_id_list I want to paste the values from Excel, which will be one value per line. the query is failing with ORA-00907: missing right parenthesis. is there a way to convert the multiline value to a CSV one ?

1 answer

  • answered 2022-05-04 10:50 Littlefoot

    I don't use DBeaver so this is a SQL*Plus example. Basically, you'll have to "split" that multi-line input value into separate rows (that's what subquery in lines #8 - 10 does):

    Declaring a bind variable:

    SQL> var req_id_list varchar2(20)

    Storing A + carriage return + C into it:

    SQL> exec :req_id_list := 'A' || chr(13) || 'C';

PL/SQL procedure successfully completed.

    Query itself (lines #1 - 5 represent sample data; I expect A and C to be returned as that's what I passed to req_id_list):

    SQL> with mytable (mycolumn) as
  2    (select 'A' from dual union all
  3     select 'B' from dual union all
  4     select 'C' from dual
  5    )
  6  select *
  7  from mytable
  8  where mycolumn in (select regexp_substr(replace(:req_id_list, chr(13), '#'), '[^#]+', 1, level)
  9                     from dual
 10                     connect by level <= regexp_count(:req_id_list, chr(13)) + 1
 11                    );

M
-
A
C

SQL>

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Why does this Sql query shows the possible changes but does not implement it?

    So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

    SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

    Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

  • how to get weeklytotal and yesterday record in mysql in one table

    Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

    Table name-dailydata-

    Sample data

    employee_id date total
    20 2022-04-25 10
    20 2022-04-26 20
    20 2022-04-27 20
    20 2022-04-28 10
    20 2022-04-29 20
    20 2022-04-30 30
    20 2022-04-31 40
    20 2022-05-01 50
    40 2022-04-26 20

    expected output

    employee_id weekly_total yesterday_record
    20 200 40
    40 20 0

    mysql query to get weekly data

    select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  • Procedure to display the Employees of a specific Department

    Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

    Name of the argument as Input : DeptNo

    Write the code to create the procedure

    Schema

    I wrote the Query like this but i didnt get expected output

    CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end

    Expected output:

    Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

  • How to remove non-numeric characters and search for phone number?

    SELECT * FROM Customer WHERE REGEXP_REPLACE(HOME_PHONE, '[^0-9]', '') LIKE '%1234567'

    This is my SQL query and I get error, Some fields have more than 7 character that’s why I am using % for searching last 7 characters in phone number. FYI I’m using oracle database.

  • Does oracle jdbc driver only support oracle database (21c,18c,19c etc) or does it support the oracle virtualbox as well?

    Does oracle jdbc driver only support oracle database (21c,18c,19c etc) or does it support the oracle virtualbox as well? I have the virtualbox downloaded and somehow the oracle jdbc is not getting installed properly.

  • Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)

    I have multi-part SDO_GEOMETRIES in Oracle 18c.

    I can extract the parts as individual rows using the SDO_UTIL.EXTRACT_ALL() function:

    with cte as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))')    as shape from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') as shape from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))')                       as shape from dual           --2D: (X,Y)
    )
select
    a.object_value,
    sdo_util.to_wktgeometry(a.object_value)
from
    cte,
    table(sdo_util.extract_all(shape)) a

OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                                         
-------------------- ---------------------------------------
[MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
[MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
[MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)

    db<>fiddle here

    I want to add the following columns to the query:

    • MULTILINE_NUM (the original multi-part line number)
    • PART_NUM
    MULTILINE_NUM PART_NUM OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                               

------------- -------- -------------------- ---------------------------------------
            1        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
            1        2 [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
            2        1 [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
            2        2 [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
            2        3 [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
            3        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)

    When adding those columns, I don't want to "fake it" after-the-fact using window functions, etc.

    I want to extract that information directly from the geometries, to ensure the numbers are correct (i.e., part number shouldn't be arbitrary; it should reflect the part number order from the original geometry).

    Is there a way to get the MULTILINE_NUM and PART_NUM from the table(sdo_util.extract_all(shape)) query?

  • Export table to a csv file in DBeaver with command

    I am using the following command to export the data

    \copy (select * From table_name) To 'my_path' With CSV DELIMITER ',' HEADER; But I get error syntax error at or near "\". If I use copy (select * From table_name) To 'my_path' With CSV DELIMITER ',' HEADER; I get the error COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy. I really do not know which command I should run. Thanks in advance for your help:))

  • java.lang.OutOfMemoryError: Java heap space in DBeaver -- when opening sqlite database as a file

    First I was using the portable version of dbeaver. Then, I decided to install it. (both are community edition) In both cases I edited the dbeaver.ini file (multiple times) incrementally increasing memory to see if anything would work. Finally I tried setting this: -Xmx100000m (~100GB of RAM!) and it still wouldn't open without the error. I have 32GB RAM installed and I did initially keep the setting under my actual max RAM, but then I thought, "What the heck? Let it cache to the SSD." But it still didn't work!

    The sqlite database is only a little over 2GB. A little big but surely not unusual for a database.

    Admittedly, this is just about my first time using dbeaver and sqlite so it's probably user error.