Convert input to date with oracle

Question

I’m having trouble returning the results of the following query, where I put the variable &mes, if I put '01-Sep-2017' would bring the results I need, but in the dialog box wanted the user to type himself with the patterns used for date, with the correct '01/09/2017', however when entering this date is presented the error ORA-01858: the non-numeric Haracter was found Where a Numeric was expected

Select a.pro_st_alternativo,
   a.pro_st_descricao,
   mgadm.adm_pck_estoque.F_SALDO_QTDE(20,21,a.pro_pad_in_codigo,a.pro_in_codigo,1,null,null,to_char(to_date(&mes), 'dd/mm/yyyy'),'AN')Saldo_mes,

   a.uni_st_unidade,
   a.pro_bo_controlalotes,
   a.pro_st_cestoque,
   a.pro_st_defitem,
   A.PRO_CH_DEFFISCALITEM,
   A.PRO_ST_ORIGEM

From mgadm.est_produtos a
Where A.PRO_PAD_IN_CODIGO = 4;

Answer 1

There is a function in PL/SQL called to_date. She is responsible for carrying out this conversion, thus:

to_date(&mes, 'DD/MM/YYYY')

Some example of masks:

TO_DATE('2003/07/09', 'yyyy/mm/dd')
retorna: July 9, 2003

TO_DATE('070903', 'MMDDYY')
Retorna: July 9, 2003

TO_DATE('20020315', 'yyyymmdd')
Retorna: Mar 15, 2002

You can find the complete list of masks here.