Re: database link, dynamic sql and stored procedure

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Thu, 22 Jul 2004 00:19:48 GMT
Message-ID: <Xns952DB04B2DCCDSunnySD@68.12.19.6>

"Marco Toci" <m.toci_at_amnesty.it> wrote in news:97464bc39a60d0a873f37f6d09a5c4ea.49383_at_mygate.mailgate.org:

>

>>Here is a free clue, single quote marks are needed around strings when
>>the are used within WHERE clauses; but you already knew that. Right?

> oh, sorry, i forget to say that "CV_MEMBERS" is a synonym for
> ibf_members_at_emdb.forum_cv, so the query should be
>
> update
> ibf_members_at_emdb.forum_cv
> set
> org_perm_id =cv_list
> where
> ibf_members_at_emdb.forum_cv.id=u_id;
>
>
>

>> I suggest that you build the complete UPDATE statement as a single
>> VARCHAR2 variable and just display it via DBMS_OUTPUT without trying
>> to EXECUTE IMMEDIATE. then cut & paste the results into SQL*Plus to
>> see where the actual errors are.

> well... i tried that, and the query displayed works on sql*plus, but
> still doesn't works on the "execute immediate" statement.
>
> i tried this:
>
> In sql*plus i tried
>
> update ibf_members_at_emdb.forum_cv set org_perm_id ='1,2,4' where
> ibf_members_at_emdb.forum_cv.id=100;
>
> and it works
> but if i put it in my stored procedure, using
> execute immediate ' begin update ibf_members_at_emdb.forum_cv set
> org_perm_id =''1,2,4'' where ibf_members_at_emdb.forum_cv.id=100; end;'
>
> i have
> ORA-00904: : invalid identifier
> ORA-02063: preceding line from EMDB_at_FORUM_CV
> ORA-06512: at line 1
> ORA-06512: at "STAGE.MDT", line 297
> ORA-06512: at line 1
>
>
> so ... what else may i try? :-(
> many, many thanks

go to http://asktom.oracle.com/
do a keyword search on "EXECUTE IMMEDIATE" and try to learn from the examples in the various responses. Received on Wed Jul 21 2004 - 19:19:48 CDT