Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Unanswered: Acces a partition in a stored procedure

    Hi,
    i would like to access a table by partition, but i would like to give the partition name in arguments.
    Here is my PL/SQL procedure i wrote :
    DECLARE
    CURSOR c_vin IS
    SELECT VIN,
    SUBSTR(CODE_ATTRIBUT,6,2) AS CLASSE_ATTRIBUT,
    SUBSTR(CODE_ATTRIBUT,8,2) AS VALEUR_ATTRIBUT
    FROM
    DQAQT11 PARTITION (DQAQT10_P23);
    END;

    And how i would like to write :

    PROCEDURE FinalLoad(p_partition IN VARCHAR2) IS

    CURSOR c_vin IS
    SELECT VIN,
    SUBSTR(CODE_ATTRIBUT,6,2) AS CLASSE_ATTRIBUT,
    SUBSTR(CODE_ATTRIBUT,8,2) AS VALEUR_ATTRIBUT
    FROM
    DQAQT11 PARTITION (p_partition);


    I would like to call the procedure with the partition name in arg.
    DQAQA1A.FinalLoad(partition_name)
    Can you help me ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Acces a partition in a stored procedure

    You would have to use dynamic SQL, not static SQL:
    Code:
    PROCEDURE FinalLoad(p_partition IN VARCHAR2) IS
    
      TYPE rc_type IS REF CURSOR;
      rc rc_type;
      v_sql VARCHAR2(2000) := 
        'SELECT VIN,
               SUBSTR(CODE_ATTRIBUT,6,2) AS CLASSE_ATTRIBUT,
               SUBSTR(CODE_ATTRIBUT,8,2) AS VALEUR_ATTRIBUT
          FROM DQAQT11 PARTITION (' || p_partition || ')';
    
    BEGIN
      OPEN rc FOR v_sql;
      ...

  3. #3
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    thanx for your answer.
    Bur i usually use cursor like this in dynamic SQL :

    DECLARE
    CURSOR c_vin IS
    SELECT VIN,
    SUBSTR(CODE_ATTRIBUT,6,2) AS CLASSE_ATTRIBUT,
    SUBSTR(CODE_ATTRIBUT,8,2) AS VALEUR_ATTRIBUT
    FROM
    DQAQT11 PARTITION (DQAQT10_P23);

    BEGIN
    FOR r_vin IN c_vin
    LOOP
    BEGIN
    SELECT RANG
    INTO p_rang
    FROM DQAQT19
    WHERE CODE_CLASSE_ATTRIBUT = r_vin.CLASSE_ATTRIBUT;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('La classe ' || r_vin.CLASSE_ATTRIBUT || ' n''est pas référencée dans dqaqt19');
    END;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Date fin : '||SYSDATE);
    DBMS_OUTPUT.PUT_LINE ('Alimentation de la table DQAQT1A');
    COMMIT;
    END;

    When you use dynamic SQL with
    BEGIN
    OPEN rc FOR v_sql;

    How do you loop on 'rc' ?
    Can i write :

    BEGIN
    OPEN rc FOR v_sql;
    for rec_rc IN rc loop
    ...

    or should i fetch the cursor, and how ?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you cannot use a cursor FOR loop. Just use explicit fetch from rc in the normal way:

    LOOP
    FETCH rc INTO x, y, z;
    EXIT WHEN rc%NOTFOUND;
    ...

  5. #5
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Originally posted by andrewst
    No, you cannot use a cursor FOR loop. Just use explicit fetch from rc in the normal way:

    LOOP
    FETCH rc INTO x, y, z;
    EXIT WHEN rc%NOTFOUND;
    ...
    Okay, thanx.
    That's how i wrote while waiting your answer.
    I've just one question.
    I use my partition in another procedure which i wrote like this :

    PROCEDURE initZoneAttribut(v_zone_attribut IN OUT varray_type, p_vin IN DQAQT11.VIN%TYPE, p_partition IN VARCHAR2) IS
    p_zone_attr VARCHAR2(2);
    BEGIN
    FOR i IN 1..980
    LOOP
    SELECT SUBSTR(ZONE_ATTRIBUTS, (i*2)-1, 2)
    INTO p_zone_attr
    -- FROM DQAQT1A
    FROM DQAQT1A PARTITION (p_partition)
    WHERE VIN = p_vin;
    v_zone_attribut(i) := NVL(p_zone_attr, ' ');
    END LOOP;
    END initZoneAttribut;
    As you see, i have the same problem, except i don't use a cursor.
    I re-write this procedure like this :

    PROCEDURE initZoneAttribut(v_zone_attribut IN OUT varray_type, p_vin IN DQAQT11.VIN%TYPE, p_partition IN VARCHAR2) IS
    p_zone_attr VARCHAR2(2);
    v_sql VARCHAR2(2000);
    BEGIN
    FOR i IN 1..980
    LOOP
    v_sql :=
    'SELECT SUBSTR(ZONE_ATTRIBUTS, ' || (i*2)-1 || ', 2)
    INTO p_zone_attr
    -- FROM DQAQT1A
    FROM DQAQT1A PARTITION (' || p_partition || ')
    WHERE VIN = p_vin';
    EXECUTE IMMEDIATE v_sql;
    v_zone_attribut(i) := NVL(p_zone_attr, ' ');
    END LOOP;
    END initZoneAttribut;
    Did i have to use 'EXECUTE IMMEDIATE', or could i write this procedure in another way ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, EXECUTE IMMEDIATE was the way to go. But you need to take the variable names out of the SQL and put them in the EXECUTE:

    -- Remove INTO and use bind variable :vin
    v_sql :=
    'SELECT SUBSTR(ZONE_ATTRIBUTS, ' || (i*2)-1 || ', 2)
    FROM DQAQT1A PARTITION (' || p_partition || ')
    WHERE VIN = :vin';

    EXECUTE IMMEDIATE v_sql INTO p_zone_attr USING p_vin;

  7. #7
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Thanks for all

Posting Permissions

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