PLS00215: String length constraints must be in range (1..32767)

I am new to pl/sql. I want to create a procedure that has three parameters called ‘startMonth’, ‘endMonth’, ‘thirdMonth’. In the procedure, I am executing a sql query which is in ‘run_sql’ column in table_query. Values for ‘startMonth’, ‘endMonth’, ‘thirdMonth’ are needed to this query. This is how I wrote the procedure. My plan is to put all the sql queries in a separate table and execute in the for loop in the procedure. There I am creating a table called table1 and in the next month I want to drop it and create the table again. This is how I have written the procedure.

CREATE OR REPLACE procedure schema.sixMonthAverage (startMonth varchar,endMonth varchar ,thirdMonth varchar )
IS
start_date varchar := startMonth;
end_date varchar := endMonth;
begin
for c_rec in(select run_sql from table_query)
 loop
 dbms_output.put_line(startmonth);
 dbms_output.put_line(endmonth);
      execute immediate c_rec.run_sql using start_date, end_date;
      Execute IMMEDIATE 'commit';
 END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception');
END;

This is the query in the run_sql column in table_query.

create table table1
as
select account_num,bill_seq,bill_version,
to_char(start_of_bill_dtm,''YYYYMM-DD'') st_bill_dtm, 
to_char(bill_dtm - 1,''YYYYMM-DD'') en_bill_dtm,
to_char(actual_bill_dtm,''YYYYMM-DD'') act_bill_dtm, 
round((invoice_net_mny + invoice_tax_mny)/1000,0) mon_bill,
bill_type_id,bill_status
from billsummary
where to_char(bill_dtm - 1,''YYYYMM'') between'||chr(32)||
startMonth ||chr(32)||'and'|| chr(32)||endMonth ||chr(32)||
'and cancellation_dtm is null;

But when I try to compile the procedure it gives me the error ‘PLS00215: String length constraints must be in range (1..32767). Though I searched for the error I could not find the exact reason. It seems to be a problem in variable assigning. But I could not resolve it.

–Update

As it is given in the answer I converted the strings to dates.

CREATE OR REPLACE procedure REPO.sixMonthAverage (startMonth varchar2,endMonth varchar2 ,thirdMonth varchar2 )
IS
start_date date := TO_DATE(startMonth, 'yyyymm');
end_date date := TO_DATE(endMonth, 'yyyymm');

But when executing the query it gives the error message that ORA-00904: “END_DATE”: invalid identifier. But it does not show any error message for the start_date and what would be the reason for this error message?

Answer

The error is pointing you to where the problem is. String declarations (char, varchar, varchar2 – but you should only be using varchar2, not varchar) need a length; so for example:

CREATE OR REPLACE procedure sixMonthAverage (startMonth varchar2,endMonth varchar2 ,thirdMonth varchar2 )
IS
start_date varchar2(10) := startMonth;
end_date varchar2(10) := endMonth;
...

Notice the procedure arguments do not specify a length; only the local variable declarations.

If those represent dates then they, and passed-in arguments, should probably be dates, not strings. It depends what your dynamic SQL is expecting though – if that is converting the strings to dates and specifying the format mask then I guess it’s OK; otherwise you should be passed dates, or convert the strings to dates. The example you showed doesn’t seem to have any bind variables to populate, though.

Dropping and recreating tables is generally not something you want to be doing though. You could delete/truncate and repopulate a table; or use partitioning if you want to keep more than one month; or use a view (or materialized view).