Re: Dbms_sql dynamics

Tom Best wrote:
>
> Procedure set_trans_state (
> v_trans_id IN integer
> ) IS
>
> crs number;
> stmt VARCHAR2(2000);
> dummy number;
>
> BEGIN
> DBMS_OUTPUT.PUT_LINE ('-- setting transaction state in CTM_TRANS
> table');
>
> crs := dbms_sql.open_cursor;
> stmt := 'update ctmadmin.ctm_trans set status = 2, end_date = sysdate
> where transid = ' || v_trans_id;
> DBMS_OUTPUT.PUT_LINE (stmt);
> DBMS_SQL.PARSE (crs, stmt, DBMS_SQL.V7);
> dummy := DBMS_SQL.EXECUTE(crs);
> DBMS_SQL.CLOSE_CURSOR(crs);
>
> END set_trans_state;
>
> HTH,
> Tom Best

Tanx, but

i need requete give in procedure's parameter like this :

Procedure set_trans_state (requete IN VARCHAR2, resultset OUT VARCHAR2) IS

     crs     number;
     stmt    VARCHAR2(2000);
     dummy   number;

 BEGIN  

     crs := dbms_sql.open_cursor;
     stmt := requete;
     DBMS_OUTPUT.PUT_LINE (stmt);
     DBMS_SQL.PARSE (crs, stmt, DBMS_SQL.NATIVE);
     dummy := DBMS_SQL.EXECUTE(crs);
     DBMS_SQL.CLOSE_CURSOR(crs);
 

 END set_trans_state;

I'm under oracle 8i. I want execute anyone select, coz i don't know table and column.

I should execute this under Pro*C, so i must return resultset to this.

>
> Frederic Logier <fl_at_accelance.net> wrote in message
> news:397BF7B6.D531DE5E_at_accelance.net...
> > hi,
> >
> > i'm looking for some sample for dbm_sql dynamic requete.
> >
> > I want a package executing anyone select.
> >

-- 
Frédéric LOGIER
fl_at_accelance.fr
______________________________________________________

ACCELANCE - www.accelance.fr
97, rue Racine - 69100 Villeurbanne
Tel: +33 (0)4 37 43 12 22 / Fax: +33 (0)4 37 43 12 20
______________________________________________________