bind variables

[kirilb]

Hi, I understood about different usage of bind variables betw. TAOD and PL/SQL Developer (e.g. : and & ). But there must be other differences as well, because just replacing : with & does not help, I still get error messages. So, how should be rewritten the TOAD code into PL/SQL?:

VAR  datum VARCHAR2(30)
EXEC :datum := '30-06-2015'
SELECT * FROM TABLE WHERE DATE = TO_DATE (:datum, 'DD.MM.YYYY')

 

[Theod]

In sql-window just so:

SELECT * FROM TABLE WHERE DATE = TO_DATE ('&datum', 'DD.MM.YYYY')

 

[kirilb]

Thanks, it works! So popup asks me for me the values? But what about if I have many parameters, is not there a way to define them at the begining instead of popup?

 

[Maxmix]

1. Paste your select into empty Test Window,
2. in the bottom pane your could "fetch" all your bind variables from entered text or input them by hand,
3. set appropriate datatype next to bind variable name ,
4. enter values for bind variables,
5. press Execute.

The results will be on separate tab SQL Output(here all rows are fetched, so you might want to stop fetching with Shift+Esc).

Chapter 5 in the documentation explains how to utilise Test Windows for running PL/SQL, but these Test Windows are also good for running parameterized SELECTs.

 

[kirilb]

So far works everything, but have questions re handling of PL/SQL Developer when executing many consecutive delete/insert commands within single script: I get error message "failed to execute policy function". When I execute them separatly they work, but not together. Below sample of one script (followed then by many other delete/insert):

DELETE FROM TABLE_A
      WHERE Datum_per = TO_DATE ('&datum', 'DD.MM.YYYY');
INSERT INTO TABLE_A
SELECT BLA BLA...;
COMMIT;