Passing array to a procedure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Passing array to a procedure

Post by novice4eva »

Hi there, i was hoping that someone would help to sort out this problem i am having 8O
The problem is this:
I am trying to pass an array to a pl/sql procedure, well this is my procedure(if it helps), the procedure shows no errors

Code: Select all

CREATE OR REPLACE PACKAGE Pkg_Eval 
 AS
    
    TYPE ARRTYPE IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;
    PROCEDURE PR_EVAL_FORM_ENTITIES_DEF (
        v_mode IN VARCHAR2, -- MANDATORY,
        v_form_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.form_id%TYPE DEFAULT NULL,
        v_criteria_id IN ARRTYPE,
        v_metric_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.metric_id%TYPE DEFAULT NULL,
        v_status /*CHAR(1)*/ IN EVALUATION_FORM_ENTITIES_DEF.status%TYPE DEFAULT NULL,
        v_entered_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.entered_by%TYPE DEFAULT NULL,
        v_entered_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
        v_last_updated_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
        v_last_updated_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.last_updated_by%TYPE DEFAULT NULL
    
        );
    
END Pkg_Eval;
 
AND the body goes LIKE this:
 
PROCEDURE PR_EVAL_FORM_ENTITIES_DEF (
    v_mode IN VARCHAR2, -- MANDATORY,
    v_form_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.form_id%TYPE DEFAULT NULL,
    v_criteria_id IN ARRTYPE,
    v_metric_id /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.metric_id%TYPE DEFAULT NULL,
    v_status /*CHAR(1)*/ IN EVALUATION_FORM_ENTITIES_DEF.status%TYPE DEFAULT NULL,
    v_entered_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.entered_by%TYPE DEFAULT NULL,
    v_entered_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
    v_last_updated_dt /*DATE(7)*/ IN VARCHAR2 DEFAULT NULL,
    v_last_updated_by /*VARCHAR2(10)*/ IN EVALUATION_FORM_ENTITIES_DEF.last_updated_by%TYPE DEFAULT NULL
    ) IS 
    /**
    *
    * This PL/SQL code is a procedure used to insert, update and delete the evaluation_form_entities_def records 
    * to the table .Here, the first argument is mode which accepts a MANDATORY value as in
    * ('I' for INSERT 'U' for UPDATE 'D' for DELETE)
    *
    * @autoproc version 1.2
    * @code version 1.0
    * @author DEEWENDRA G. SHRESTHA
    * @modification 06-Apr-2007, DEEWENDRA G. SHRESTHA
    */
    BEGIN
         IF UPPER(v_mode) = 'I' THEN
            FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
                INSERT INTO EVALUATION_FORM_ENTITIES_DEF(
                    form_id,
                    criteria_id,
                    metric_id,
                    STATUS,
                    entered_by,
                    entered_dt,
                    last_updated_dt,
                    last_updated_by
                        ) VALUES (
                    v_form_id,
                    criteria,
                    v_metric_id,
                    v_status,
                    v_entered_by,
                    SYSDATE,
                    SYSDATE,
                    v_last_updated_by
                        );
            END LOOP;
        ELSIF UPPER(v_mode) = 'U' THEN
        FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
            UPDATE EVALUATION_FORM_ENTITIES_DEF SET 
                    form_id=v_form_id,
                    criteria_id=criteria,
                    metric_id=v_metric_id,
                    STATUS=v_status,
                    last_updated_dt=SYSDATE,
                    last_updated_by=v_last_updated_by
             WHERE 1=1 
                 AND form_id=v_form_id
                 AND criteria_id=criteria;
            END LOOP;
        ELSIF UPPER(v_mode) = 'D' THEN
        FOR criteria IN v_criteria_id.FIRST..v_criteria_id.LAST LOOP
            DELETE FROM EVALUATION_FORM_ENTITIES_DEF
             WHERE 1=1 
                 AND form_id=v_form_id
                 AND criteria_id=criteria;
        END LOOP;
        END IF;
        COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20099, SQLERRM);
    END PR_EVAL_FORM_ENTITIES_DEF;
And as for the PHP part:

Code: Select all

$connection=$this->getConnection();
$query  = OCIParse($connection, $Sql);
oci_bind_array_by_name($query , ":criteria", $criteria, 20, 1, SQLT_CHR);
return OCIExecute($query,OCI_DEFAULT);
Any suggestions would be great. Thanks a lot
Last edited by novice4eva on Wed Dec 24, 2008 8:46 pm, edited 1 time in total.
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Post by novice4eva »

Everything works fine now, it was some FK integrity prob...sorry again
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

It motivated me to explore the oci_* functions... I was just about to post that everything seemed to work ok overhere ;)

The only thing i'd probably change is the following:

Code: Select all

oci_bind_array_by_name($query , ":criteria", $criteria, count($criteria), 1, SQLT_CHR);
This way the maximum number of allowed items is always equal to the real number of items...
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Post by novice4eva »

:D Thanks, that was a neat idea....
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Post by novice4eva »

Well I am stuck again :(
First of all a notice if someone knows this for the code:

Code: Select all

oci_bind_array_by_name($query , ":criteria", $criteria, count($criteria), 1, SQLT_CHR);
The second last parameter is the max length of the value ie. if in your database - it say varchar2(10), then this '1' should be replaced by '10' or else you will get only first character of the entered value, i found it just now after hours of head banging but the problem didn't stop there..... remember the foreign key violation thing, it still persists :evil:
If i remove the constraint, the value gets inserted, other remarkable thing is that the entered value is valid, but still for some reason "FOREIGN KEY VIOLATION" seems to never let go. The foreign key violation is for the column i inserted using TYPE ie:

Code: Select all

TYPE ARRTYPE IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;
i have already tortured myself with about 5 hrs of investment on this prob ......... :banghead:

PLZZZZ HELPPPPPPPPPPPPPPPPPPPPPPPPPP 8O
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

novice4eva wrote:

Code: Select all

oci_bind_array_by_name($query , ":criteria", $criteria, count($criteria), 1, SQLT_CHR);
Instead of wasting hours banging your head you could have simply consulted oci_bind_array_by_name.
Using -1 as value for the second last parameter is the lazy way of making it work. That's what i used when i was experimenting after your initial post (http://www.timvw.be/using-a-collection- ... /#comments)

As soon as you disable the constraint, and insert some data, you can't enable the constraint anymore, since there will always be a violation (normally oracle prevents you from re-enabling the constraint :p)... So you'll have to scan your data and update where the constraints are violated before you can re-enable the constraint again..
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Post by novice4eva »

Ok i am going to give you the real picture, i am using "toad" here, thank god there is TOAD - in this case :D or else i would be wondering for ever. Let me start fresh, say i deleted all the contents of the table in which multiple insertion has to be performed, now say all constraints are enabled...i do insert now and POOF nothing...nowz the time i am going :x
If the data are there in the table, as you said re-enabling would return an error message, that is OK, then i delete all the contents again...Next i disable the foreign key constraint on the column where multiple insertion has to be performed, and try to insert, then -> yeppee it works. Now i enable the foreign key constraint back on(obviously after deleting all the contents again) and manually feed the data which i wanted to through an array, now the same data will get inserted! NOW WHERE DID THE FOREIGN KEY VIOLATION GO???? 8O

Hope you understood my real dilemma :roll:
Post Reply