Re: IGNORED SQL STATEMENT IN WHERE CLAUSE
Date: 1996/10/31
Message-ID: <3278bda7.4384093_at_dcsun4>
What do you mean 'it is being ignored'?
is it returning no data in the insert? what does ignored mean?
When you reference dbms_pipe below, do you really mean dbms_sql? or do you have a daemon process that is doing the actual dynamic sql. If you do have a pipe server and it builds some sql and executes it, don't forget that it can only see committed data in the database so if the process sending it sql is sending it sql that wants to 'see' data that process just created, the pipe server won't be able to see it (since it is not committed).
could you elaborate a little more on the problem....
On Mon, 28 Oct 1996 11:47:37 -0500, Jennifer Brickner <brickner_at_vistachrome.com> wrote:
>CALLING ALL DBMS_PIPE EXPERTS:
>
>I am retrieving a where clause from a table and compiling it in a stored
>procedure using dbms_pipe. One of my statements is being ignored!!! I
>definitely do not get an error during runtime or in "WHEN OTHERS" table.
> I am stumped!!!!! Does anybody have any idea what it could be?
>
>HERE IS MY SQL STATEMENT (WORKS FINE FROM PROMPT)
> build_sql := 'insert into leads (lead_contact_id, lead_gener_id)'||
> 'select distinct con_id, '||uniq_seq ||
> ' from contact_answer_v where ('||
> rec.buye_criteria_WHERE || ') and ' ||
> 'not exists (select 1 from leads l, lead_generations lg
>' ||
> 'where contact_answer_v.con_id =
>l.lead_contact_id ' ||
> 'and l.lead_gener_id = lg.id ' ||
> 'and lg.id < ' || uniq_seq ||
> ' and lg.buyer_id = ' || rec.buye_id || ')';
> begin
> err_stage := 1;
> cursor_1 := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_1,build_sql,dbms_sql.v7);
> return_1 := dbms_sql.execute(cursor_1);
> dbms_sql.close_cursor(cursor_1);
> cursor_1 := 0;
> commit;
> exception
> when others then
> err_num := SQLCODE;
> err_msg := SUBSTR(SQLERRM,1,100);
> insert into hb3_errors values
> (0,
> sysdate,
> user,
> 1,
> 1,
> err_num,
> 'ERROR_STAGE '||ERR_STAGE||ERR_MSG,
> 'GENLEAD');
>commit;
>HERE IS VARIABLE FOR WHERE_CLAUSE
>exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>271307 and cont_valid_ans_seq
>= 1 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) AND
>
>CALL_MEDIUM = 'EC' AND
>
>
>CALL_CALL_START > '26-SEP-96' AND
>
>
>( exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>745521 and cont_valid_ans_seq
> = 2 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) OR
>
> exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>745521 and cont_valid_ans_seq
>= 3 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) OR
>
> exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>745521 and cont_valid_ans_seq
>= 4 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) OR
>
> exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>745521 and cont_valid_ans_seq
>= 5 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) OR
>
> exists (select 1 from contact_answers where cont_valid_ans_ques_id =
>745521 and cont_valid_ans_seq
>= 6 and contact_answer_v.cont_call_recor_id =
>contact_answers.cont_call_recor_id ) ) AND
>
>CON_MOVE_DATE < (sysdate+180) -- IGNORED STATEMENT
>
>Regards,
>Jennifer
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Oct 31 1996 - 00:00:00 CET