Skip to Main Content
Forums

Oracle Developer Tools for VS Code

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to enter binds in Oracle PL/SQL for VS Code?

User_OB8D3Oct 7 2021

Recently started using the Oracle PL/SQL extension for VS Code. To execute the querry I use Ctrl+R and the result is displayed. But in this particular querry I have to input the binds. Unlike Toad for oracle or Oracle SQL Developer, VS Code does not let me enter binds and since it does not let me enter the binds, it gives me the following error
ORA-01008: not all variables bound
How can I rectify this small problem? How can I input my binds in VS Code?
A small part of the querry I wanted to run is shown below.

SELECT ces.event_seq_num,
  1 causality_exists
FROM case_event_assess ces
WHERE ces.case_id           = :case_id
AND ces.prod_seq_num        = :dummy1
AND ces.license_id          = 0
AND ces.datasheet_id        = 0
AND ( ces.rpt_causality_id IS NOT NULL
OR ces.det_causality_id    IS NOT NULL
OR ces.PRT_causality_id    IS NOT NULL)
This post has been answered by Christian.Shay -Oracle on Oct 11 2021
Jump to Answer

Comments

Gaz in Oz

One convoluted way would be to use REGEXP_LIKE()
For example, using WITH() to mimic dummy table and dummy data:

SQL> ed
Wrote file afiedt.buf

 1 with f0101 (aban8, abalph, abat1) as (
 2    select   33, 'Text', 'V' from dual union all
 3    select  998, 'Some', 'V' from dual union all
 4    select 1023, 'More', 'V' from dual union all
 5    select 1024, 'text', 'V' from dual
 6 )
 7 select aban8, abalph
 8 from  f0101
 9 where abat1 = 'V'
 10 and   regexp_like(aban8, '^(' || replace(replace(:p_aban, ',', '|'), ' ') || ')$')
 11* or    :p_aban IS NULL
SQL> exec :p_aban := '33,998';

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
       33 Text
      998 Some

2 rows selected.

SQL> exec :p_aban := NULL;

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
       33 Text
      998 Some
     1023 More
     1024 text

4 rows selected.

SQL> exec :p_aban := '1023, 1024';

PL/SQL procedure successfully completed.

SQL> /

    ABAN8 ABAL
---------- ----
     1023 More
     1024 text

2 rows selected.

SQL>
1 person found this helpful
YoungTL

Thanks a lot.
If nothing else, I'll try use the procedure.
You know in the Jaspersoft Studio I can use java.util.Collection ,it work really good,
but i can't find this type in JasperReport Server's input control.
I think maybe can add this type ,but i don't know how to add it.

User_H3J7U

Jaspersoft Studio User Guide/Parameters/IN & NOTIN

1 person found this helpful
YoungTL

As you see I had try use $X {in},but what input control type i should use,
there not any Collection  item to select.
the condition should be manual input,there more then thousands result,
I can't use multi_select Query ,let user to select ,maybe they only want to see 3 or 5 result, from thousands result to select 3 or 5 result isn't good.

o4.jpgo5.jpg

1 - 4

Post Details

Added on Oct 7 2021
#oracle-developer-tools-for-vs-code
4 comments
1,022 views
1 mention