This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 4, 2013 9:12 AM by 668136 RSS

Dynamic SQL execute immediate

668136 Newbie
Currently Being Moderated
Hi all,

We have one table where we keep the query that is going to be executed and the using bind variables.

But im facing some problems when i try to use it in execute immediate.

supose

function validate is
varaux varchar2(200);
begin
for r1 in (select query,
using_bind
from table_1) loop
execute immediate r1.query into varaux using r1.using_bind;
return varaux;
end loop;
return null;
end;

I know that's not the way to do it, but can you give me directions in how to...

Thanks
Bruna

Edited by: Bruna on 4/Jan/2013 3:07
  • 1. Re: Dynamic SQL execute immediate
    KeithJamieson Expert
    Currently Being Moderated
    Your example does not require execute imemdiate and a bind variable.

    Here instead you should use a parameterised cursor.
    Also validate is not a legal name for a function.
    CREATE OR REPLACE 
    FUNCTION VALIDATE_IT(p_value in varchar2)
    return varchar2
    is
    VARAUX VARCHAR2(200);
    CURSOR R1(p_value in varchar2)
    IS 
    Select * from dual where dummy = p_value;
    BEGIN
    OPEN R1(p_value);
    LOOP
    FETCH R1 INTO VARAUX;
    EXIT WHEN R1%NOTFOUND;
    end loop;
    RETURN VARAUX;
    end validate_it; 
  • 2. Re: Dynamic SQL execute immediate
    Bawer Journeyer
    Currently Being Moderated
    Bruna wrote:
    I know that's not the way to do it, but can you give me directions in how to...
    than check doc:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
  • 3. Re: Dynamic SQL execute immediate
    Karthick_Arp Guru
    Currently Being Moderated
    We have one table where we keep the query that is going to be executed and the using bind variables.
    I would suggest you read

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1943344500346351703
  • 4. Re: Dynamic SQL execute immediate
    6363 Guru
    Currently Being Moderated
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
  • 5. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    3360 wrote:
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
    You should get out of that "box"
  • 6. Re: Dynamic SQL execute immediate
    6363 Guru
    Currently Being Moderated
    Bruna wrote:
    3360 wrote:
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
    You should get out of that "box"
    There is a right way to do things and a wrong way. You are doing it the wrong way.

    Or do you think this is a brilliant, genius idea that no one else has ever thought of? If so you should read the links posted, it has been thought of many times by people who didn't know any better what a mess they were making.
  • 7. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    3360 wrote:
    Bruna wrote:
    3360 wrote:
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
    You should get out of that "box"
    There is a right way to do things and a wrong way. You are doing it the wrong way.

    Or do you think this is a brilliant, genius idea that no one else has ever thought of? If so you should read the links posted, it has been thought of many times by people who didn't know any better what a mess they were making.
    thank you very much for your reply
  • 8. Re: Dynamic SQL execute immediate
    BluShadow Guru Moderator
    Currently Being Moderated
    Bruna wrote:
    3360 wrote:
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
    You should get out of that "box"
    You should learn to understand the fundamentals of good software engineering, especially in terms of databases.

    Storing queries as data in tables is very poor.
  • 9. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    BluShadow wrote:
    Bruna wrote:
    3360 wrote:
    Bruna wrote:

    We have one table where we keep the query that is going to be executed and the using bind variables.
    Repeat this until you truly understand and believe it.

    Tables are for data, views and stored procedures are for executable code.

    {message:id=10590811}

    Queries are executable code and should not be stored in tables.
    You should get out of that "box"
    You should learn to understand the fundamentals of good software engineering, especially in terms of databases.

    Storing queries as data in tables is very poor.
    again, thank you very much for your reply

    Edited by: Bruna on 4/Jan/2013 6:07
  • 10. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    "Queries are executable code and should not be stored in tables."
    "Storing queries as data in tables is very poor."

    "Repeat this until you truly understand and believe it."
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"


    Oh my God

    Look bad software engineering...

    select series_query
    from APEX_APPLICATION_PAGE_FLASH_S

    Regards
  • 11. Re: Dynamic SQL execute immediate
    BluShadow Guru Moderator
    Currently Being Moderated
    Bruna wrote:
    "Queries are executable code and should not be stored in tables."
    "Storing queries as data in tables is very poor."

    "Repeat this until you truly understand and believe it."
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"


    Oh my God

    Look bad software engineering...

    select series_query
    from APEX_APPLICATION_PAGE_FLASH_S

    Regards
    Don't be an arse. There are always exceptions to the rule, and I doubt you are developing a development system like Application Express.
  • 12. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    BluShadow wrote:
    Bruna wrote:
    "Queries are executable code and should not be stored in tables."
    "Storing queries as data in tables is very poor."

    "Repeat this until you truly understand and believe it."
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"
    "*Tables are for data, views and stored procedures are for executable code.*"


    Oh my God

    Look bad software engineering...

    select series_query
    from APEX_APPLICATION_PAGE_FLASH_S

    Regards
    Don't be an arse. There are always exceptions to the rule, and I doubt you are developing a development system like Application Express.
    Ok i understand you are from Oracle so you can... humiliate

    Cant i use BLOB, CLOB?
    Cant i use Dynamic SQL?
    Isnt Oracle the best tool to do the job?

    Since i put this thread i was called stupid, ignorant, arse...

    But its ok because you are from ORACLE.

    Thanks for all the help,

    Kisses
  • 13. Re: Dynamic SQL execute immediate
    6363 Guru
    Currently Being Moderated
    Bruna wrote:

    Since i put this thread i was called stupid, ignorant, arse...
    You were not called at least 2 out of 3, and the third is questionable.

    Although you seem to be trying very hard to prove that you are all of the above with very little help from anyone.
  • 14. Re: Dynamic SQL execute immediate
    668136 Newbie
    Currently Being Moderated
    3360 wrote:
    Bruna wrote:

    Since i put this thread i was called stupid, ignorant, arse...
    You were not called at least 2 out of 3, and the third is questionable.

    Although you seem to be trying very hard to prove that you are all of the above with very little help from anyone.
    i'm not trying to prove anything... again humiliation... you are very good on that, congratulations. but in the rest... pufff

    if im not getting any help thats because there are more simple questions that gives points.

    i know this is not a simple question, thats why i asked for help

    but leave it, its almost done :)

    All the best
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points