Odac X %rowtype

Paulo · Post by **Paulo** » Mon 13 Jun 2011 14:16

Hello, is there any way to invoke procedure with %ROWTYPE parameter using Odac 6.25.1.13 for Delphi 7?

Example:

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

BEGIN
  INSERT INTO SOMETABLE VALUES PAR_TEST;
END;

Thanks.

EDIT:
http://www.devart.com/forums/viewtopic. ... d8a3d963ba

Seems theres no support for record. Any idea how can i workaround or any other way to pass a record without creating a procedure with several parameters?

AlexP · Post by **AlexP** » Tue 14 Jun 2011 10:25

Hello,

You cannot use %ROWTYPE records as parameters. But you can create your own object and use it as a procedure parameter, for example:

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;

[/quote]

Paulo · Post by **Paulo** » Tue 14 Jun 2011 12:55

Ty for your reply.

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;

Since i have two parameters as out sys_refcursor, im using TOraSQL, TOraQuery and TOraDataSource:

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 */}

This is a test to edit sales plan using global temporary table and, after user confirm, we should update the plan table. I'm able to update, but i'm stuck on insert new records

Do you think theres any better pratice to use in this case?

Tyvm, sorry for my english

AlexP · Post by **AlexP** » Thu 16 Jun 2011 07:05

Hello,

Please send us scripts to create your procedures (for receiving and saving data), scripts for creating and filling tables, and also a small test application demonstrating working with these procedures to alexp*devart*com.
Please give a more detailed description of the problem (what goes wrong on inserting data), and we will try to help you.

Paulo · Post by **Paulo** » Mon 27 Jun 2011 20:30

Sorry for not sending you the codes, i was in hurry to finish that project. Now all works like a charm. I was not able to insert because i forget to use ROWID ;S

Tyvm.

AlexP · Post by **AlexP** » Wed 29 Jun 2011 08:24

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.