Devart Forums

Code: Select all

CREATE OR REPLACE PROCEDURE TEST_ODAC( PAR_TEST IN SOMETABLE%ROWTYPE) IS

BEGIN
  INSERT INTO SOMETABLE VALUES PAR_TEST;
END;

Code: Select all

CREATE TABLE T_TEST
(
ID NUMBER,
TEXT VARCHAR2(10)
)
/

CREATE OR REPLACE TYPE T_TEST_ROW AS OBJECT(ID NUMBER, TEXT VARCHAR2(10))
/

CREATE OR REPLACE PROCEDURE SP_INS_TEST_ROW(A_ROW IN T_TEST_ROW)
AS
BEGIN
INSERT INTO T_TEST VALUES(A_ROW.ID, A_ROW.TEXT);
END;
/

Code: Select all

var
  OraStoredProc: TOraStoredProc;
begin
  OraStoredProc:= TOraStoredProc.Create(nil);
  OraStoredProc.Session:= OraSession1;
  OraStoredProc.StoredProcName:='SP_INS_TEST_ROW';
  OraStoredProc.Prepare;
  OraStoredProc.ParamByName('A_ROW').DataType := ftObject;
  OraStoredProc.ParamByName('A_ROW').ParamType:= ptInput;
  OraStoredProc.ParamByName('A_ROW').AsObject.AllocObject(OraSession1.OCISvcCtx, 'T_TEST_ROW');
  OraStoredProc.ParamByName('A_ROW').AsObject.AttrAsInteger['id'] := 1;
  OraStoredProc.ParamByName('A_ROW').AsObject.AttrAsString['text'] := 'test';
  OraStoredProc.Execute;

Code: Select all

CREATE OR REPLACE PACKAGE TEST_ODAC AS

   TYPE TEST_TYPE IS RECORD ( 
      FIELD1   SOMETABLE.FIELD1%TYPE,
      FIELD2   SOMETABLE.FIELD2%TYPE,
      FIELD2   SOMETABLE.FIELD3%TYPE);




   PROCEDURE START_TEST (TEST_TYPE IN TEST_TYPE,
                              AVG_TMP OUT SYS_REFCURSOR,
                              DETAIL_TMP OUT SYS_REFCURSOR);
END TEST_ODAC;

Code: Select all

procedure TfrmSomeForm.StartSalesPlan;
var
    odacPrc: TOraSQL;
    oqryPnjSales: TOraQuery;
    odsPnjSales: TOraDataSource;
    odsAvgPrc: TOraDataSource;
    oqryAvgPrc: TOraQuery;

begin
with odacPrc do begin
    odacPrc.ParamCheck := True;
    SQL.clear;
    SQL.Text := ' DECLARE '+
                '   TEST_TYPE TEST_ODAC.TEST_TYPE; '+
                ' BEGIN '+
                  ' TEST_TYPE.FIELD1:= :FIELD1; '+
                  ' TEST_TYPE.FIELD2 := :FIELD2; '+
                  ' TEST_TYPE.FIELD3 := :FIELD3; '+
                  ' TEST_ODAC.START_TEST(TEST_TYPE => TEST_TYPE, AVG_TMP => :AVG_TMP, DETAIL_TMP => :DETAIL_TMP); '+
                ' END;';



    ParamByName('FIELD1').DataType  := ftInteger;
    ParamByName('FIELD1').ParamType := ptInput;
    ParamByName('FIELD1').AsInteger := StrToInt('111'); {/* TODO: User selection */}
    ParamByName('FIELD2').DataType  := ftInteger;
    ParamByName('FIELD2').ParamType := ptInput;
    ParamByName('FIELD2').AsInteger := 1; {/* TODO: User selection*/}
    ParamByName('FIELD3').DataType  := ftDateTime;
    ParamByName('FIELD3').ParamType := ptInput;
    ParamByName('FIELD3').AsDate := StrToDate('01/12/2011'); {/* TODO: User selection */}
    ParamByName('AVG_TMP').DataType := ftCursor;
    ParamByName('DETAIL_TMP').DataType := ftCursor;
    ParamByName('AVG_TMP').ParamType := ptOutput;
    ParamByName('DETAIL_TMP').ParamType := ptOutput;
    Prepare;
    Execute;
  end;

  oqryPnjSales.Cursor := odacPrc.ParamByName('DETAIL_TMP').AsCursor;
  oqryPnjSales.Open;

  for i := 0 to oqryPnjSales.FieldCount -1 do
    oqryPnjSales.Fields[i].Required := False; {/* Is need since we want to insert directly to GLOBAL TEMP TABLE, used to recalc AVG_TMP and DETAIL_TMP, after all edits and user confirm the updates, we should take care of keys using procedure to save on the table planning sales table*/}


  oqryAvgPrc.Cursor := odacPrc.ParamByName('AVG_TMP').AsCursor;
  oqryAvgPrc.Open;

{/* CODE TO BIND query to datasource to cxGrid and let user edit the GLOBAL TEMPORARY TABLE. Later, after user confirm all updates, call another procedure to update the sales plan table */}