Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: execute dynamic sql and output the results to a string.

    Hi all
    I am fairly ok with oracle and so need some help in fixing this procedure...
    As mentioned in the title, I need to run dynamic sql's that gets created by a cursor. The below procedure gets me 2 sql resultsets and I need to run each sql and output the result.I just managed to create something close to what i require but unable to move ahead. I get an error at line 9. It says 'statement ignored'....
    Appreciate if someone could look through my eyes :-)
    Many thanks!

    CREATE OR REPLACE procedure CDW.SPARSEMETA is
    r1c1 varchar2(2000);
    cursor c1 is
    SELECT 'SELECT ' || COLUMNNAME || ' ' || ' FROM ' || TABLENAME || ' WHERE ' || WHERE_CLAUSE FROM ETL_SPARSE_META;
    begin
    For eachsql in c1
    loop
    execute immediate eachsql into r1c1;
    dbms_output.put_line(r1c1);
    end loop;
    end;

    am I expecting too much from too little ? .......hehe!!
    -Mad

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    10
    Hi
    Thanks for the reply.
    I changed my pl sql to this and able to get the output values but the execute immediate isn't working. Any thoughts?

    ================================
    CREATE OR REPLACE procedure SPARSEMETA_x is
    r1c1 varchar2(200);
    r1c2 varchar2(200);
    r1c3 varchar2(200);
    ip varchar2(1000);
    nop varchar2(1000);
    oop varchar2(1000);
    cursor c1 is
    SELECT COLUMNNAME ,TABLENAME , WHERE_CLAUSE FROM ETL_SPARSE_META;
    begin
    OPEN c1 ;
    loop
    FETCH c1 INTO r1c1, r1c2,r1c3;
    exit when c1%notfound;
    --dbms_output.put_line( 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3);
    ip := 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3;
    dbms_output.put_line(ip);
    nop:= '';
    nop:= r1c1;
    --execute immediate 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3
    --into nop;
    oop := oop || ' ' || nop;
    end loop;
    dbms_output.put_line(oop);
    close c1;
    end;

    =====================================

    Regards,
    -Mad

  4. #4
    Join Date
    Jul 2012
    Posts
    10
    Btw, this is my output to the procedure above.
    Select CUST_ID FROM AXA_HEALTH_CONTACT_T WHERE ADDRESS2 = 'Sector 50';
    Select POLICY_REF FROM AXA_HEALTH_CLAIM_T WHERE POLICY_REF = '123';
    Select CUST_ID FROM AXA_HEALTH_CUSTOMER_T WHERE FORENAME = 'SMITH'
    CUST_ID POLICY_REF CUST_ID

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by celestialcow View Post
    I changed my pl sql to this and able to get the output values but the execute immediate isn't working. Any thoughts?
    Alas! It is a serious disaster that you face this mysterious "isn't working" Oracle behaviour. You are very unlucky to fall into such a dreadful situation; in fact I never faced it, Oracle did not document it anywhere, so I cannot assist more.

    For all other Oracle users, Oracle provides quite meaningful description of the error, so you may find out its reason/solution then. Without it, I may only guess: that dynamic query does returns zero (0) or more than 2 rows. What shall be done in the inner LOOP in that situation?

  6. #6
    Join Date
    Jul 2012
    Posts
    10
    oh, I should have printed out this error earlier......

    SQL> exec sparsemeta_x
    Select CUST_ID FROM AXA_HEALTH_CONTACT_T WHERE ADDRESS2 = 'Sector 50';
    BEGIN sparsemeta_x; END;

    *
    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "CDW.SPARSEMETA_X", line 20
    ORA-06512: at line 1

    ==================

    Regards,
    -Mad
    Last edited by celestialcow; 07-10-12 at 11:33.

  7. #7
    Join Date
    Jul 2012
    Posts
    10
    oh..luvly!
    Thanks for the reply.
    I got it. I was passing the semicolon in the where clause from the resultset.
    Removed it and ...

    ==================
    Select CUST_ID FROM AXA_HEALTH_CONTACT_T WHERE ADDRESS2 = 'Sector 50'
    Select POLICY_REF FROM AXA_HEALTH_CLAIM_T WHERE POLICY_REF = '123'
    Select CUST_ID FROM AXA_HEALTH_CUSTOMER_T WHERE FORENAME = 'SMITH'
    989 123 989
    ======================================

    Regards,
    -Mad

  8. #8
    Join Date
    Jul 2012
    Posts
    10
    Now, this procedure wouldnt compile........
    Placing the exception out side the loop works but wouldnt finish the loop ...wantsomething that throws excep and get back to the next one in the loop.

    CREATE OR REPLACE procedure SPARSEMETA_x is
    r1c1 varchar2(200);
    r1c2 varchar2(200);
    r1c3 varchar2(200);
    ip varchar2(1000);
    nop varchar2(1000);
    oop varchar2(1000);
    cursor c1 is
    SELECT COLUMNNAME ,TABLENAME , WHERE_CLAUSE FROM ETL_SPARSE_META;
    begin
    OPEN c1 ;
    loop
    FETCH c1 INTO r1c1, r1c2,r1c3;
    exit when c1%notfound;
    --dbms_output.put_line( 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3);
    ip := 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3;
    dbms_output.put_line(ip);
    nop:= '';
    nop:= r1c1;
    execute immediate 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3
    into nop;
    --oop := oop || ' ' || nop;
    exception
    WHEN NO_DATA_FOUND THEN
    nop:='No Value';
    oop := oop || ' ' || nop;
    end loop;
    dbms_output.put_line(oop);
    close c1;
    end;

    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE SPARSEMETA_X:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    23/1 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    begin case declare end exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge pipe

    28/1 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
    one of the following:
    end not pragma final instantiable order overriding static

    LINE/COL ERROR
    ======================

    Any thoughts on why I am unable to place an exception there?

    Regards,
    -Mad

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what should happen when SELECT returns 2 or more rows?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2012
    Posts
    10
    for the time , i am dealing with just the no data found exception and have left the data to fetch me only one record per sql.
    Thanks

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR replace PROCEDURE Sparsemeta_x 
    IS 
      r1c1 VARCHAR2(200); 
      r1c2 VARCHAR2(200); 
      r1c3 VARCHAR2(200); 
      ip   VARCHAR2(1000); 
      nop  VARCHAR2(1000); 
      oop  VARCHAR2(1000); 
      CURSOR c1 IS 
        SELECT columnname, 
               tablename, 
               where_clause 
        FROM   etl_sparse_meta; 
    BEGIN 
        OPEN c1; 
    
        LOOP 
            FETCH c1 INTO r1c1, r1c2, r1c3; 
    
            exit WHEN c1%NOTFOUND; 
    
            --dbms_output.put_line( 'Select ' || r1c1 || ' FROM ' || r1c2 || ' WHERE ' || r1c3); 
            ip := 'Select ' 
                  || r1c1 
                  || ' FROM ' 
                  || r1c2 
                  || ' WHERE ' 
                  || r1c3; 
    
            dbms_output.Put_line(ip); 
    
            nop := ''; 
    
            nop := r1c1; 
    
            BEGIN 
                EXECUTE IMMEDIATE 'Select '|| r1c1|| ' FROM '|| r1c2|| ' WHERE '|| 
                r1c3 
                INTO 
                nop 
                ; 
            EXCEPTION 
                WHEN no_data_found THEN 
                  NULL; 
            END; 
    
            oop := oop 
                   || ' ' 
                   || nop; 
        END LOOP; 
    
        dbms_output.Put_line(oop); 
    
        CLOSE c1; 
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have to end the loop before the exception block except if the begin/exception/end is completely withing the loop.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Jul 2012
    Posts
    10
    wow!!! Superb....thanks a lot.

    Regards,
    -Mad

  14. #14
    Join Date
    Jul 2012
    Posts
    10
    the end result i wanted...perfect!!

    SQL> exec sparsemeta_x
    Select CUST_ID FROM AXA_HEALTH_CONTACT_T WHERE ADDRESS2 = 'Sector 50'
    Select POLICY_REF FROM AXA_HEALTH_CLAIM_T WHERE POLICY_REF = '12345'
    Select CUST_ID FROM AXA_HEALTH_CUSTOMER_T WHERE FORENAME = 'SMITH'
    989 No Value 989

    PL/SQL procedure successfully completed.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the end result i wanted...perfect!!
    only until 2 or more rows in result set
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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