[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date conversion PL/SQL Bind value :0 not defined

Posted on 2012-09-12
20
Medium Priority
?
1,137 Views
Last Modified: 2012-09-13
Dear experts,

I am having trouble with a cursor I declared.

I need to filter for a date/time in my where condition and need the user to be able to enter these values on runtime.

When hardcoding the date as a string the cursor runs fine. When prompting for the input and enetering it in the exact same format, oracle fusses about a "bind variable is not declared error". I have not been able to resolve that issue using date conversions.

Maybe you could suggest a solution to my problem.

The problematic variables are ''v_accept_ok'' and ''v_end_ok''.


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;

Open in new window


Thank you!
0
Comment
Question by:skahlert2010
  • 10
  • 10
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38390881
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
0
 

Author Comment

by:skahlert2010
ID: 38390946
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?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38390960
can you post your full script?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:skahlert2010
ID: 38390961
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;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391050
try this...
ee.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391069
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?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391134
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.
0
 

Author Comment

by:skahlert2010
ID: 38391179
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?
0
 

Author Comment

by:skahlert2010
ID: 38391198
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!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391233
this worked for me
ee.txt
0
 

Author Comment

by:skahlert2010
ID: 38391280
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38391305
that's your problem.  You have a substitution variable so when you enter

11.09.12 08:00:00


your code

  v_accept_ok   DATE := &prompt_accept_ok;

is changed to

  v_accept_ok   DATE := 11.09.12 08:00:00 ;

which is not legal.

try using  

TO_DATE('11.09.12 08:00:00','dd.mm.rr hh24:mi:ss')

or

trunc(sysdate)-1+8/24

or anything else that is actually a DATE type value
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391312
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
0
 

Author Comment

by:skahlert2010
ID: 38391503
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391531
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.
0
 

Author Comment

by:skahlert2010
ID: 38391532
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
0
 

Author Comment

by:skahlert2010
ID: 38391546
Wow, you're fast! Please let me close this topic and award you tomorrow if that's alrightto you.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38391660
if you got your answer, please do
0
 

Author Comment

by:skahlert2010
ID: 38393823
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
0
 

Author Closing Comment

by:skahlert2010
ID: 38393824
Outstanding help and explanation! Rapid feedback! Greatly appreciate it! Thanks a lot!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question