Date conversion PL/SQL Bind value :0 not defined

the colon characters in the code will throw off your strings in sql*plus

set define off

at the beginning of your script that way when it encounters something like

:00 it won't try to parse it as a bind, but rather as the literal string

Hi sdstuber! Thanks for that explanation.

Before executing that script in sqlplus I'd like to run it in SQL Developer or some other tool.

When running that script in sqlplus I receive "Bind variable prompt_end_ok not declared".

Do you have another advise?

can you post your full script?

Basically I could post the entire procedure so you can try and see the error yourself.

It creates a directory, an external table, checks your db_name and gathers the archive logs out of your alert.log in a given time range.
That is for the case when v$archived_log doesn't list these entries any more and I still need to do an until time recovery.

set serveroutput on
set define off

begin
declare
v_alertpos varchar2(300);
v_allog varchar2(30);
v_msg varchar2(100);
v_tabcount number(1);
v_dircount number(1);
type r_cursor is REF CURSOR;
c_arch r_cursor;
type rec_arch is record
  (filename  varchar2(100));
v_arch rec_arch;
v_accept_ok date := &prompt_accept_ok;
v_end_ok date := &prompt_end_ok;

begin

SELECT '''' || value || '''' into v_alertpos FROM v$parameter WHERE name='background_dump_dest';
SELECT count(*) into v_dircount from dba_directories where directory_name = 'ALERT_DUMP_DIR';
SELECT count(*) into v_tabcount from dba_tables where table_name = 'ALERT_LOG_EXTERNAL';

v_msg := 'Background dumpdest is set to: ';
dbms_output.put_line(v_msg || v_alertpos);

if v_dircount = 1 then 
execute immediate 'drop directory alert_dump_dir';
dbms_output.put_line('Directory ALERT_DUMP_DIR has been dropped!');
execute immediate 'create directory alert_dump_dir as ' || v_alertpos;
dbms_output.put_line('Directory ALERT_DUMP_DIR has been recreated!');
else
execute immediate 'create directory alert_dump_dir as ' || v_alertpos;
dbms_output.put_line('Directory ALERT_DUMP_DIR has been created!');
end if;

select '''alert_' || instance_name || '.log''' into v_allog from v$instance;
dbms_output.put_line('Alert Log is assumed to be:' || v_allog);

if v_tabcount != 1 then 
execute immediate 'CREATE TABLE ALERT_LOG_EXTERNAL (TEXT VARCHAR2(255)) 
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ALERT_DUMP_DIR 
ACCESS PARAMETERS (records delimited by newline nobadfile nologfile) 
LOCATION (' || v_allog || ')) REJECT LIMIT UNLIMITED';
elsif v_tabcount = 1 then
dbms_output.put_line ('Table ALERT_LOG_EXTERNAL already exists. Table will not be recreated.');
end if;

BEGIN

open c_arch FOR 'with w as (SELECT FILEDIR || ''/'' || DB_NAME AS PATHNAME, ARCH_FORMAT, INST_ID 
FROM ( SELECT ( SELECT regexp_substr( REPLACE(display_value,''LOCATION='',''''), 
''/[^[:space:]]+'' ) FROM v$parameter WHERE name = ''log_archive_dest_1'' ) FILEDIR, 
( SELECT name FROM v$database ) DB_NAME, ( SELECT instance_number FROM 
v$instance ) INST_ID, ( SELECT display_value FROM v$parameter WHERE name = 
''log_archive_format'' ) ARCH_FORMAT FROM dual )) SELECT filename FROM ( SELECT 
CASE WHEN ARCH_FORMAT LIKE ''%arch_%r_%s_%t.arc'' THEN w.PATHNAME || ''_arch_'' || ( 
SELECT resetlogs_id FROM V$DATABASE_INCARNATION WHERE status = ''CURRENT'' ) || 
''_'' || v.eintrag || ''_'' || w.inst_id || ''.arc'' ELSE ''LOG_ARCHIVE_FORMAT defers 
from standard settings! Please customize this query!'' END AS filename FROM ( 
SELECT zeitpunkt, regexp_substr(eintrag, ''[[:digit:]]+'',2,3) eintrag FROM ( 
SELECT LAST_VALUE(TO_CHAR(zeitpunkt,''DD.MM.YY HH24:MI:SS'') IGNORE NULLS) 
OVER(ORDER BY rownum ROWS UNBOUNDED PRECEDING) AS zeitpunkt, eintrag FROM ( 
SELECT rownum, CASE WHEN ( text LIKE ''___ ___ __ __:__:__ 20__'' ) THEN 
TO_DATE(text, ''Dy Mon DD HH24:MI:SS YYYY'', ''nls_date_language=american'') END AS 
zeitpunkt, CASE WHEN ( text NOT LIKE ''___ ___ __ __:__:__ 20__'' ) THEN text END 
AS eintrag FROM alert_log_external ) ) WHERE eintrag IS NOT NULL AND 
to_date(zeitpunkt,''DD.MM.YY HH24:MI:SS'') BETWEEN to_date( ''v_accept_ok'', ''DD.MM.YY HH24:MI:SS'') 
AND to_date(''v_end_ok'', ''DD.MM.YY HH24:MI:SS'') AND zeitpunkt IS NOT NULL 
AND eintrag LIKE ''Archived Log entry%'' ) v, w ) ORDER BY filename'; 

loop
fetch c_arch into v_arch;
exit when c_arch%notfound;
dbms_output.put_line(v_arch.filename);
end loop;
end;

execute immediate 'drop directory alert_dump_dir';

execute immediate 'drop table ALERT_LOG_EXTERNAL';

end;
end;

Select allOpen in new window

try this...
ee.txt

oops just noticed an extra conversion that isn't correct


AND TO_DATE(zeitpunkt, 'DD.MM.YY HH24:MI:SS')
                                             BETWEEN :v_accept_ok AND :v_end_ok  


that should be just


AND zeitpunkt BETWEEN :v_accept_ok AND :v_end_ok  


zeitpunkt is already a date from the inner query


also  

OVER (ORDER BY ROWNUM ROWS UNBOUNDED PRECEDING

doesn't make sense

what is it you're trying to do there really?

also

LAST_VALUE(TO_CHAR(zeitpunkt, 'DD.MM.YY HH24:MI:SS') IGNORE NULLS)

should probably be

LAST_VALUE(zeitpunkt IGNORE NULLS)

regardless of what the OVER clause ends up being.

Thank you sdstuber for your corrections.

Unfortunately I still cannot get the procedure to work.
It still gives me an error that not all variables are bound.
And that is after I substitued my original part with your correction i.e.

"AND zeitpunkt BETWEEN :v_accept_ok AND :v_end_ok"  

Regardless of my superficial date_conversions, which just slow down the query I am not yet able to run the procedure and insert dates that are handled correctly after being prompted.

Sorry for taking your time but this is really bugging me.

Did you try to run the script yourself? Did you succeed?

I just ran your ee.txt posted above in sqlplus and subsituted your suggested changes.
However I still received that bind variable 00 not declared error.

Ver strange but I'll try more! It's just on the brink of being solved...

Thank you!

this worked for me
ee.txt

No, still not working.
We must have different env settings.

How did you enter your date string?

I did as the following:

11.09.12 08:00:00

Thanks for your help!
I really appreciate it!

Unfortunately I need to stop testing now and can only continue tomorrow.

or alternately...

change your code to


v_accept_ok   DATE := TO_DATE('&prompt_accept_ok','dd.mm.rr hh24:mi:ss');

then enter your date as you did before.
do something similar with v_end_ok

AThanks again sdstuber. I am happy to be assisted by the guru himself.

That is why I used all these date conversions you suggested to fix. Originally, I declared v_accept_ok as varchar2 and tried to convert it to date in my where

dynamic sql makes it more complicated and hides some of the issues.
substitution adds another layer of complexity.

putting the substitution early in the variable initialization helps.  The only concern then is making sure that your substitution becomes a DATE type properly, then you simply keep all dates as dates unless you absolutely must convert them back to strings.  Which you do not need to do in this code.

glad I could help.

Never mind, I am on my way and using the mobile ee version, which is somewhat uncomfortable. Hence the nonsense post above. I meant to say that Itried to convert v_accept_ok to my desired date format in the where clause. I'll try your code, which makes totally sense and will inform you of the outcome tomorrow.

Best regards and thanks, skahlert2010

Wow, you're fast! Please let me close this topic and award you tomorrow if that's alrightto you.

if you got your answer, please do

Hello sdstuber!

I am happy to tell you that my script is working like a charme thanks to your help.

Maybe you or others can need it if they need to check the alert.log for archives that were generated in a given period but are no longer present in v$archived_log due to high traffic.

Thank you!
archive.txt

Outstanding help and explanation! Rapid feedback! Greatly appreciate it! Thanks a lot!