Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- (RESULT) NUMBER
- P1 VARCHAR2 IN
- P2 VARCHAR2 IN
- P3 VARCHAR2 IN
- P4 VARCHAR2 OUT
- q := TADOQuery.Create(nil);
- q.Connection := conn;
- q.SQL.Add('BEGIN');
- q.SQL.Add(' SELECT GetData(:IN_1,:IN_2,:IN_3,:OUT_1) into :OUT_2 from dual;');
- q.SQL.Add('END;');
- q.Parameters.ParamByName('IN_1').DataType:=ftString;
- q.Parameters.ParamByName('IN_1').Direction:=pdInput;
- q.Parameters.ParamByName('IN_1').Size:=3;
- q.Parameters.ParamByName('IN_1').Value:='001';
- q.Parameters.ParamByName('IN_2').DataType:=ftString;
- q.Parameters.ParamByName('IN_2').Direction:=pdInput;
- q.Parameters.ParamByName('IN_2').Size:=15;
- q.Parameters.ParamByName('IN_2').Value:='88000000000';
- q.Parameters.ParamByName('IN_3').DataType:=ftString;
- q.Parameters.ParamByName('IN_3').Direction:=pdInput;
- q.Parameters.ParamByName('IN_3').Size:=64;
- q.Parameters.ParamByName('IN_3').Value:='';
- q.Parameters.ParamByName('OUT_1').DataType:=ftString;
- q.Parameters.ParamByName('OUT_1').Direction:=pdOutput;
- q.Parameters.ParamByName('OUT_1').Size:=255;
- q.Parameters.ParamByName('OUT_1').Value:='';
- q.Parameters.ParamByName('OUT_2').DataType:=ftInteger;
- q.Parameters.ParamByName('OUT_2').Direction:=pdOutput;
- q.Parameters.ParamByName('OUT_2').Value:='0';
- q.Open;
- responseEdit.Text:=q.Parameters.ParamByName('OUT_1').Value;
- CallableStatement cs = conn.prepareCall("{ call ? := GetData(?,?,?,?)}");
- q.SQL.Add('BEGIN');
- q.SQL.Add(' :OUT_2 := GetData(:IN_1,:IN_2,:IN_3,:OUT_1);');
- q.SQL.Add('END;');
- stp := TADOStoredProc.Create(nil);
- stp.Connection := conn;
- //stp.ProcedureName:='GetData'; //also tried this
- stp.ProcedureName:=':OUT_2 := GetData(:IN_1,:IN_2,:IN_3,:OUT_1)';
- stp.Parameters.CreateParameter('OUT_2',ftInteger,pdOutput,4,0);
- stp.Parameters.CreateParameter('IN_1',ftString,pdInput,3,'101');
- stp.Parameters.CreateParameter('IN_2',ftString,pdInput,15,phoneEdit.Text);
- stp.Parameters.CreateParameter('IN_3',ftString,pdInput,64,' ');
- stp.Parameters.CreateParameter('OUT_1',ftString,pdOutput,255,' ');
- stp.ExecProc;
- cmd := TADOCommand.Create(nil);
- cmd.Connection := conn;
- cmd.CommandType := cmdStoredProc;
- cmd.CommandText := ' :OUT_2 := GetData(:IN_1,:IN_2,:IN_3,:OUT_1); ';
- ...
- CREATE OR REPLACE FUNCTION ADMIN.GETDATA(IN_1 IN VARCHAR2,
- IN_2 IN VARCHAR2,
- IN_3 IN VARCHAR2,
- OUT_1 IN OUT VARCHAR2) RETURN INTEGER
- IS
- BEGIN
- OUT_1 := IN_1 || IN_2 || IN_3;
- RETURN 5;
- END;
- BEGIN
- DECLARE OUTVAR VARCHAR(255);
- RETVAR INTEGER;
- BEGIN
- RETVAR:= ADMIN.GETDATA('A','B','C', OUTVAR);
- DBMS_OUTPUT.PUT_LINE(OUTVAR);
- DBMS_OUTPUT.PUT_LINE(RETVAR);
- END;
- END;
- ABC
- 5
- var Proc: TADOStoredProc;
- P: TParameter;
- Results: String;
- begin
- Proc := TADOStoredProc.Create(nil);
- try
- (* Set up the connection to an Oracle database *)
- Proc.Connection := MyADOConnection;
- (* Define the function Name *)
- Proc.ProcedureName := 'GETDATA';
- (* Let the FrameWork retrieve all the parameters from DataBase *)
- //Proc.Parameters.Refresh;
- //Set parameters values
- //Proc.Parameters.ParamByName('IN_1').Value := 'A';
- //Proc.Parameters.ParamByName('IN_2').Value := 'B';
- //Proc.Parameters.ParamByName('IN_3').Value := 'C';
- //Proc.Parameters.ParamByName('OUT1').Value := ''; //This will be overrided by the database.
- // **********************
- // OR
- // **********************
- //Define it manually!
- begin
- //Defining the Return Value
- Proc.Parameters.CreateParameter('RETVAL', ftBCD (* or ftInteger *), pdReturnValue, 0, Unassigned);
- //Defining Input parameters 1, 2 and 3;
- Proc.Parameters.CreateParameter('IN1', ftString , pdInput, 4000 (* This is the Max *), 'A');
- //Collect the reference to update later
- P := Proc.Parameters.CreateParameter('IN2', ftString , pdInput, 4000 (* Length for a *), Unassigned);
- Proc.Parameters.CreateParameter('IN3', ftString , pdInput, 4000 (* ftString param *), 'C');
- Proc.Parameters.CreateParameter('OUT1', ftString , pdOutput, 4000, Unassigned);
- P.Value := 'B';
- Proc.ExecProc;
- // Expected : [Return Value : 5]~[Out Var: ABC]
- Results := Format('[Return Value : %s]~[Out Var: %s]',
- [VarToStr(Proc.Parameters.ParamByName('RETVAL').Value),
- VarToStr(Proc.Parameters.ParamByName('OUT1').Value)]);
- Proc.Close;
- // ACTUAL : [Return Value : 5]~[Out Var: ABC]
- ShowMessage(Results);
- //IT WORKS!!
- end;
- finally
- FreeAndNil(Proc);
- end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement