Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    6

    Unanswered: Dynamic where and spool output required

    Hi,

    I need to insert data in a table through(via sql, no PL/SQL code), which selects data on the basis of some SQL variable. See example below:

    INSERT INTO table_name1(col1, col2, col3)
    SELECT col1, col2, col3 FROM table_name2 WHERE condition1
    AND condition2

    -- Condition2 is dynamic and is dependent on SQL variable, something like
    SELECT DECODE(QL_VAR, 1, ' AND condition2 is true', ' AND condition2 is
    false') INTO :condition2 FROM DUAL;

    Now, can someone please let me know, how I can achieve this?
    As when I substitute :condition2 in the INSERT query above it gives me error!

    What I am trying to do is :

    INSERT INTO table_name1(col1, col2, col3)
    SELECT col1, col2, col3 FROM table_name2 WHERE table_name2.col1 = 123
    :condition2;

    Also, after inserting data into this table, I need to spool data from it and again it will be dependent on the SQL variable.

    What I am trying to do is:

    SPOOL abc.lst

    SELECT :selectdata
    FROM table_name1

    SPOOL OFF

    Can someone please help me and let me know whether this is possible?

    Also, I am trying not to use PL/SQL (ref cursors, etc.)..

    Please help!

    Cheers,
    Vineet

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you need to user pl/sql: read up on execute immediate
    you could create the procedure on the fly each time.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    use an &, not a :

    define condition2 = "and col2 = 'ABC'"

    INSERT INTO table_name1(col1, col2, col3)
    SELECT col1, col2, col3 FROM table_name2
    WHERE table_name2.col1 = 123
    &condition2;

    notice the double quotes around the entire string. this allows the use of single quotes within

    not sure what your issue with the spool is? just spool out the select from above
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Apr 2006
    Posts
    6
    Thanks guys for your responses.

    I have achieved this through CASE in the where clause. I look for the parameter value in the where clause and satisfy the condition on the basis of it through CASE.

    But anyway, thanks for providing the alternatives also.

    Cheers,
    Vineet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •