Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Angry Unanswered: Oralce PL/SQL Dynamic SQL Problem

    Hello, newby on line here. Having a problem with using dynamic sql in Oracle PL/SQL.

    I have created a table as follows:

    CREATE TABLE PLSQLTEST
    (PERSON_CODE NUMBER,
    REG_KEY NUMBER,
    START_DATE DATE)

    I have a source table which also (amongst others) contains the above columns, same names, same datatypes.

    Here's my PL/SQL that is driving me insane!!-

    DECLARE
    CURSOR LRA_CURSOR IS
    SELECT PERSON_CODE, REG_KEY, START_DATE FROM EA_LEARNER_REGISTERS_ATT
    WHERE PERSON_CODE ='128000';
    LRA_VAL LRA_CURSOR%ROWTYPE;
    BEGIN
    OPEN LRA_CURSOR;
    LOOP
    FETCH LRA_CURSOR INTO LRA_VAL;
    EXIT WHEN LRA_CURSOR%NOTFOUND;
    execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||LRA_VAL.START_DATE||')';
    END LOOP;
    CLOSE LRA_CURSOR;
    END;
    .
    /

    This works fine for a similar script populating the first 2 columns alone (person_code and reg_key), but when I include the the start_date column I receive the following (error at bottom):

    SQL> DECLARE
    2 CURSOR LRA_CURSOR IS
    3 SELECT PERSON_CODE, REG_KEY, START_DATE FROM EA_LEARNER_REGISTERS_ATT
    4 WHERE PERSON_CODE ='128000';
    5 LRA_VAL LRA_CURSOR%ROWTYPE;
    6 BEGIN
    7 OPEN LRA_CURSOR;
    8 LOOP
    9 FETCH LRA_CURSOR INTO LRA_VAL;
    10 EXIT WHEN LRA_CURSOR%NOTFOUND;
    11 execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY|
    |', '||LRA_VAL.START_DATE||')';
    12 END LOOP;
    13 CLOSE LRA_CURSOR;
    14 END;
    15 .
    SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at line 11

    Please! This is driving me to an early grave!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE is not needed with INSERT

    INSERT INTO PLSQLTEST VALUES
    (LRA_VAL.PERSON_CODE,LRA_VAL.REG_KEY,LRA_VAL.START _DATE);

  3. #3
    Join Date
    Dec 2003
    Posts
    10

    Red face

    Ah.. my apologies for the lack of clarity... I'm going somewhere with this....

    Ulitmately, the source data has the following fields:
    Date_1, Date_2, Date_3, Date_4, Date_5, Date_6, Date_7 etc. ect..
    (This architicture is from a COTS app and cannot be changed at source)

    The reason for the dynamic sql is so that I can include an additional loop to perform the following:

    FOR XXX IN 1..53 LOOP
    execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||
    LRA_VAL.DATE_||'XXX)';END LOOP

    So my understanding is that I will need the execute immediate?
    Any idea why it refuses to populate the columns with the date format?
    The source data actually comes in the format DD-MON-YY

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    you use execute immediate for dynamic sql where say you dont know which tables / columns you will be using at compile time only at run time

    Try to use straight sql wherever you can so you can do this which is much shorter and faster

    INSERT INTO PLSQLTEST VALUES
    SELECT PERSON_CODE, REG_KEY, START_DATE FROM EA_LEARNER_REGISTERS_ATT
    WHERE PERSON_CODE ='128000';


    (Specify the column names though)

  5. #5
    Join Date
    Dec 2003
    Posts
    10
    Yes, thanks Alan, but how can I include a variable in the specification of the column within the select query?

    One solution is as follows:

    Select person_code, reg_key, date_1 from table1 UNION
    Select person_code, reg_key, date_2 from table1 UNION
    Select person_code, reg_key, date_3 from table1 UNION
    Select person_code, reg_key, date_4 from table1 UNION
    Select person_code, reg_key, date_5 from table1 etc.. ;

    which seems horribly inefficient.
    What I really want is a loop to extract the relvant column incrementally from the cursor (which contains all the columns) so... using pseudo code say:

    Insert into newtable person_code, reg_key, date_1 from cursor;
    Insert into newtable person_code, reg_key, date_2 from cursor;
    Insert into newtable person_code, reg_key, date_3 from cursor;

    Which I wanted to achieve via something like:

    FOR X IN 1..53 LOOP
    INSERT INTO newtable VALUES cursor.person_code, cursor.reg_key, cursor.Date_X;
    END LOOP

    (????!)

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Mark, try something like this:

    Code:
    CREATE OR REPLACE PROCEDURE dyn_sql
    IS
       TYPE curvar_type IS REF CURSOR;
    
       curvar                        curvar_type;
       recvar                        my_table%ROWTYPE;
       myquery                     VARCHAR2 (1000);
    
    BEGIN
       myquery := 'SELECT * FROM my_table;';
    
       OPEN curvar FOR myquery;
    
       WHILE TRUE LOOP
          FETCH curvar INTO recvar;
    
          EXIT WHEN curvar%NOTFOUND;
    
          -- do stuff with recvar.field_1, recvar.field_2, etc.
    
       END LOOP;
    
       CLOSE curvar;
    END;
    Hope this steers you in the right direction.

    JoeB

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >FOR XXX IN 1..53 LOOP
    >execute immediate 'INSERT INTO PLSQLTEST VALUES
    >('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||
    >LRA_VAL.DATE_||'XXX)';END LOOP

    Not that this matters directly,
    but why, oh, why, do the "||" preceed "LRA_VAL.PERSON_CODE"?

    I suspect the problem result from implicit conversion of the DATE variables into strings. You may need to use TO_DATE within the VALUES clause. I'll stipulate that for dynamic SQL you MUST create a string, while Oracle expects DATE variables for DATE columns.

  8. #8
    Join Date
    Dec 2003
    Posts
    10
    I've tried every possible comination of TO_DATE and TO_CHAR conversions that I can think of to try to hammer this in, but to no avail.

    The double pipe concatenation is used to build up the string expression to be executed by the execute immediate statement. This way, you can effectively drop in literal values via the cursor variables in a similar way as you would with ASP.

    Incidentally,

    TO_CHAR(DATE_01, 'DD-MM-YY') returns values like -71
    TO_CHAR(DATE_01, 'DD-MON-YY') returns error: Column not allowed here, and
    SELECT TO_CHAR(DATE_01, 'D/MM/YYYY') returns values like .000286286859433152018322359003721729173

    Any ideas what is going on with these dates? I've triple checked and the table columns are defined as DATE formats.

    If I type directly into the SQL*PLUS window any of the above, I recieve the expected date string.
    Last edited by markwiddowson; 12-15-03 at 06:41.

  9. #9
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    The following quote will ersolve your problem and do as you require (though am not sure if this is the best way to do it)

    DECLARE
    nPERSON_CODE PLSQLTEST.PERSON_CODE%TYPE;
    nREG_KEY PLSQLTEST.REG_KEY%TYPE;
    dSTART_DATE PLSQLTEST.START_DATE%TYPE;
    BEGIN
    for i in 1..53 loop
    execute immediate 'SELECT PERSON_CODE,
    REG_KEY, DATE_' || i ||
    ' FROM EA_LEARNER_REGISTERS_ATT'
    into nPERSON_CODE ,nREG_KEY, dSTART_DATE;

    insert into PLSQLTEST values
    (nPERSON_CODE,nREG_KEY,dSTART_DATE);
    end loop;
    END;
    /

    The problem with
    FOR XXX IN 1..53 LOOP
    execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||
    LRA_VAL.DATE_||'XXX)';END LOOP

    is that it tried to evaluate LRA_VAL.DATE_ and concatenate it with 'XXX' which could not happen as it could not find a column with name DATE_ in LRA_VAL.

    Regards,
    Oracle can do wonders !

  10. #10
    Join Date
    Dec 2003
    Posts
    10
    Many thanks for that last suggestion. It kind of works... the trouble is that the query returns more than one row, hence I was trying to make use of the cursor loop when using the insert into statement.
    To make life more complicated, the reg_key values are not incremental, hence the introdcution of an additional loop does not resolve this.
    I will try to come up with some hybrid solution, but where is the date format going wrong when using the cursor loop?

    The response in respect of being unable to resolve LRA_VAL.DATE_||x is probably correct, though my test scripts have not yet introduced the the Date_x variable. The thing is failing with a constant value there of:

    execute immediate 'INSERT INTO PLSQLTEST VALUES ' ||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||
    LRA_VAL.DATE_12||')';

    Will try using %TYPE instead of %ROWTYPE?.....

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can write SQL which creates a properly formatted SQL file and then invoke this newly created SQL file. This avoids having to EXECUTE IMMEDIATE and can be MUCH easier to troubleshoot.

  12. #12
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    For multiple records fetch, you might want to use this:
    DECLARE
    nPERSON_CODE PLSQLTEST.PERSON_CODE%TYPE;
    nREG_KEY PLSQLTEST.REG_KEY%TYPE;
    dSTART_DATE PLSQLTEST.START_DATE%TYPE;

    cDestination PLSQLTEST%ROWTYPE;
    TYPE RefCurTyp IS REF CURSOR;
    cSource RefCurTyp;

    vQuery varchar2(500);

    BEGIN
    for i in 1..53 loop
    vQuery := 'SELECT PERSON_CODE, REG_KEY, DATE_' || i ||
    ' FROM EA_LEARNER_REGISTERS_ATT';
    open cSource for vQuery;

    loop
    fetch cSource into cDestination;
    exit when cSource%NOTFOUND;
    insert into PLSQLTEST values(cDestination.PERSON_CODE,cDestination.REG_K EY,cDestination.START_DATE);
    end loop;

    close cSource;
    end loop;
    END;
    /

    -----------------------------------------------------

    Originally posted by markwiddowson
    Many thanks for that last suggestion. It kind of works... the trouble is that the query returns more than one row, hence I was trying to make use of the cursor loop when using the insert into statement.
    To make life more complicated, the reg_key values are not incremental, hence the introdcution of an additional loop does not resolve this.
    I will try to come up with some hybrid solution, but where is the date format going wrong when using the cursor loop?

    The response in respect of being unable to resolve LRA_VAL.DATE_||x is probably correct, though my test scripts have not yet introduced the the Date_x variable. The thing is failing with a constant value there of:

    execute immediate 'INSERT INTO PLSQLTEST VALUES ' ||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||
    LRA_VAL.DATE_12||')';

    Will try using %TYPE instead of %ROWTYPE?.....
    Oracle can do wonders !

  13. #13
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: Oralce PL/SQL Dynamic SQL Problem

    HI,

    THis problem is due to a column name that was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement

    Originally posted by markwiddowson
    Hello, newby on line here. Having a problem with using dynamic sql in Oracle PL/SQL.

    I have created a table as follows:

    CREATE TABLE PLSQLTEST
    (PERSON_CODE NUMBER,
    REG_KEY NUMBER,
    START_DATE DATE)

    I have a source table which also (amongst others) contains the above columns, same names, same datatypes.

    Here's my PL/SQL that is driving me insane!!-

    DECLARE
    CURSOR LRA_CURSOR IS
    SELECT PERSON_CODE, REG_KEY, START_DATE FROM EA_LEARNER_REGISTERS_ATT
    WHERE PERSON_CODE ='128000';
    LRA_VAL LRA_CURSOR%ROWTYPE;
    BEGIN
    OPEN LRA_CURSOR;
    LOOP
    FETCH LRA_CURSOR INTO LRA_VAL;
    EXIT WHEN LRA_CURSOR%NOTFOUND;
    execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY||', '||LRA_VAL.START_DATE||')';
    END LOOP;
    CLOSE LRA_CURSOR;
    END;
    .
    /

    This works fine for a similar script populating the first 2 columns alone (person_code and reg_key), but when I include the the start_date column I receive the following (error at bottom):

    SQL> DECLARE
    2 CURSOR LRA_CURSOR IS
    3 SELECT PERSON_CODE, REG_KEY, START_DATE FROM EA_LEARNER_REGISTERS_ATT
    4 WHERE PERSON_CODE ='128000';
    5 LRA_VAL LRA_CURSOR%ROWTYPE;
    6 BEGIN
    7 OPEN LRA_CURSOR;
    8 LOOP
    9 FETCH LRA_CURSOR INTO LRA_VAL;
    10 EXIT WHEN LRA_CURSOR%NOTFOUND;
    11 execute immediate 'INSERT INTO PLSQLTEST VALUES ('||LRA_VAL.PERSON_CODE||', '||LRA_VAL.REG_KEY|
    |', '||LRA_VAL.START_DATE||')';
    12 END LOOP;
    13 CLOSE LRA_CURSOR;
    14 END;
    15 .
    SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at line 11

    Please! This is driving me to an early grave!!!
    SATHISH .

  14. #14
    Join Date
    Dec 2003
    Posts
    10

    Cool

    Many, many thanks to all for your help with this problem....
    Can now happily say that there is a solution in place and working !

    Merry christmas to all who helped me.

    Mark.

Posting Permissions

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