, Guest>> || || |
/ Oracle
 Oracle & ODP.Net (10i)  [new]
Alex the Marrch Caat
Member

:
: 2
, REF CURSOR. PL/SQL developer - result set. ODP.Net - , . ?!!!
, , - - !
, - , , ...
- 10i, . Connection , , ExecuteScalar ExecuteNonQuery, .

datareader- - , 8 . dataset, , . , - ..

- :
:
PROCEDURE rsrusr_access_tree_get(
  p_rsr_code  IN db_rsr_details.code%TYPE,
  p_usr_id   IN db_users.id%TYPE,
  p_access_tree IN OUT t_cursor )
 IS 
  
  CURSOR cr_access_tree IS
  SELECT
   rsr.id AS rsr_id,
   rsrt.code AS rsrtyp_code,
   rsrd.code AS rsr_code,
   rsrd.rsr_name AS rsr_name,
   rsr.assembly_id,
   rsr.inherit_allowed,
   nvl( urs.access_level, 0 ) AS access_level,
   rsr.lvl
  FROM
   ( 
   SELECT
    id,
    rsrdet_id,
    inherit_allowed,
    assembly_id,
    LEVEL AS lvl
   FROM
    db_resources
   CONNECT BY 
    PRIOR id = parent_rsr_id
   START WITH
    rsrdet_id IN (
     SELECT
      id
     FROM
      db_rsr_details
     WHERE
      code = p_rsr_code
     )
   ) rsr,
   db_rsr_details    rsrd,
   db_rsr_types     rsrt,
   db_vw_user_resources urs
  WHERE 
   urs.rsr_id(+) = rsr.id AND
   rsrd.id = rsr.rsrdet_id AND
   rsrt.id = rsrd.rsrtyp_id AND
   urs.usr_id(+) = p_usr_id;
    
  v_access_stack dbms_sql.number_table;
  v_codes_stack  dbms_sql.varchar2_table;
  v_rn      PLS_INTEGER := 1;
  
 BEGIN
  DELETE FROM dt_user_res_tree;
  
  v_access_stack( 1 ) := rsrusr_access_get(
   p_rsr_code => p_rsr_code,
   p_usr_id => p_usr_id );
   
  v_codes_stack(0) := ''; 
   
  FOR v_access_node IN cr_access_tree LOOP
   IF v_access_node.lvl > 1 THEN
    IF v_access_node.inherit_allowed = 'Y' THEN
     v_access_stack( v_access_node.lvl ) := 
      greatest( 
       v_access_node.access_level,
       v_access_stack( v_access_node.lvl - 1 ) );
    ELSE   
     v_access_stack( v_access_node.lvl ) := v_access_node.access_level;
    END IF;
   ELSE
    v_access_stack( 1 ) := rsrusr_access_get(
     p_rsr_id => v_access_node.rsr_id,
     p_usr_id => p_usr_id );
    
   END IF;
   
   
   INSERT INTO dt_user_res_tree(
    rn,
    rsrtyp_code, 
    rsr_code,
    rsr_name,
    parent_code,
    assembly_id,
    lvl,
    access_level )
   VALUES(
    v_rn,
    v_access_node.rsrtyp_code,
    v_access_node.rsr_code,
    v_access_node.rsr_name,
    v_codes_stack( v_access_node.lvl - 1 ),
    v_access_node.assembly_id,
    v_access_node.lvl,
    v_access_stack( v_access_node.lvl ) );
    
   v_codes_stack( v_access_node.lvl ) := v_access_node.rsr_code;
    
   v_rn := v_rn + 1; 
  END LOOP; 
 
  OPEN p_access_tree FOR 
   SELECT
    *
   FROM
    dt_user_res_tree
   ORDER BY
    rn;
     
 END rsrusr_access_tree_get;

PL/SQL, :
Declare t1 dc_resources.t_cursor;
t3 db_adm.dt_user_res_tree%rowtype; 

Begin 
   dc_resources.rsrusr_access_tree_get ('DELTA', 156, t1);

   FETCH t1 INTO t3;
   while t1%found loop
      dopl(t3.RSRTYP_CODE);
      dopl(t3.RSR_CODE);
      
      FETCH t1 INTO t3; 
   end loop;
End;

.Net, :
      OracleCommand comm = (OracleCommand)helper.CreateDbCommand(helper.CreateDbConnection());
      comm.CommandType = CommandType.StoredProcedure;
      comm.CommandText = rsrTreeProcedure;

      OracleParameter parm;

      parm = (OracleParameter)helper.CreateDbParameter("P_RSR_CODE",DbType.String);
      parm.Direction = ParameterDirection.Input;
      parm.Size = 2;
      parm.Value = deltaRsrCode;
      comm.Parameters.Add(parm);

      parm = (OracleParameter)helper.CreateDbParameter("P_USR_ID", DbType.Decimal);
      parm.Direction = ParameterDirection.Input;
      parm.Size = 10;
      parm.Value = user.Id;
      comm.Parameters.Add(parm);

      parm = (OracleParameter)helper.CreateDbParameter();
      parm.ParameterName = "P_ACCESS_TREE";
      parm.OracleDbType = OracleDbType.RefCursor;
      parm.Direction = ParameterDirection.InputOutput;
      comm.Parameters.Add(parm);
:
      comm.ExecuteNonQuery();
      OracleDataAdapter da = (OracleDataAdapter)helper.CreateDbDataAdapter();
      DataSet ds = new DataSet();
      da.Fill(ds, ((OracleRefCursor)(comm.Parameters["P_ACCESS_TREE"].Value)));

      comm.ExecuteNonQuery();
      OracleDataReader reader = ((OracleRefCursor)comm.Parameters["P_ACCESS_TREE"].Value).GetDataReader();

      OracleDataReader reader = comm.ExeceuteReader();

      OracleDataAdapter da = (OracleDataAdapter)helper.CreateDbDataAdapter();
      da.SelectCommand = comm;
      DataSet ds = new DataSet();
      da.Fill(ds);
17 08, 11:45[5300236]      |
 Re: Oracle & ODP.Net (10i)  [new]
ODP Programmer
Guest
, , , .

- ODP
...

: deltaRsrCode ?
17 08, 12:56[5300295]      |
 Re: Oracle & ODP.Net (10i)  [new]
Alex the Marrch Caat
Member

:
: 2
: , . . - , .
18 08, 14:26[5304285]      |
/ Oracle