Devart Forums

Code: Select all

unit dbxtest1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, sqlexpr, db, dbxdevartoracle;

type
  TForm1 = class(TForm)
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
var
  SQLConnection: TSQLConnection;
  OracleGetSerial: TSQLStoredProc;
  paramList: TParams;
  procParams: TList;

{$R *.dfm}
initialization

  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.ConnectionName := 'Devart Oracle';
    SQLConnection.DriverName := 'DevartOracle';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['DataBase'] := 'hbgmedusaora';
    SQLConnection.Params.Values['User_Name'] := 'asu'; // not schema owner!
    SQLConnection.Params.Values['Password'] := 'asu';
    SQLConnection.Connected := True;

    paramList  := TParams.Create;
    procParams := TList.Create;
    try
      OracleGetSerial := TSQLStoredProc.Create(nil);
      OracleGetSerial.SQLConnection  := TSQLConnection(SQLConnection);
      OracleGetSerial.StoredProcName := 'GetSerial'; // Defined in, say schema NX.
      TSQLConnection(SQLConnection).GetProcedureParams('GetSerial', procParams);

      if procParams.count = 0 then // Returns 4 if logged in as schema owner, but erroneously 0 when logged in as another user,
                                   // despite PUBLIC SYNONYM pointing to procedure NX.GetSerial.
        raise Exception.Create('GetSerial PLSQL function not installed or user has no rights to execute.');

      LoadParamListItems(ParamList, ProcParams);
      OracleGetSerial.Params.Assign(ParamList);
    finally
      FreeProcParams(ProcParams);
      paramList.Free;
    end;

  finally
    SQLConnection.Free;
  end;
end.

Code: Select all

-- Connect for example as 'sys as sysdba' and proper password

drop user ASU;
drop user NX cascade;
drop public synonym getserial;

CREATE USER ASU IDENTIFIED BY ASU ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "ASU";
GRANT "CONNECT" TO "ASU";
GRANT "RESOURCE" TO "ASU";

create USER NX identified by NX account unlock;
grant unlimited tablespace to NX;

-- Changed last argument from OUT to IN, because it doesn't matter for the example.
-- The example can be made even easier:
-- CREATE PROCEDURE "NX"."getserial" (Result_W IN integer) IS 

CREATE PROCEDURE "NX"."getserial" (tabellid IN varchar2, TabellNamn_I IN varchar2, antal IN integer, Result_W IN integer) IS 
tmp_number integer; 
BEGIN 
tmp_number := 0; 
-- Commented out code that is not relevant anyway
END getserial;
/

create public synonym getserial for NX.getserial;
grant execute on getserial to PUBLIC;

-- Uncomment the lines below to make sure it is possible to run the procedure via 
-- public synonym in sqlplus, but not via the example program.
-- conn ASU/ASU;
-- exec getserial('1','2',3,4);