Oracle Stored procedures

Hi.

I want to be able to, at runtime, populate a listbox with all the stored procedures in an oracle database.  Then, when I click one, I want to see the required parameters in another listbox.  (Just like the lists you get with the TStoredProc component.)

Thanks in advance,

MoonCalf.
LVL 1
MoonCalfAsked:
Who is Participating?
 
JaymolCommented:
Then add this as well, after adding another ListBox, ListBox2, to the form....

procedure TForm1.ListBox1Click(Sender: TObject);
var
  TmpSP : TStoredProc;
  TmpI  : Integer;
begin
  TmpSP:=TStoredProc.Create(nil);
  TmpSP.DatabaseName:=Database1.DatabaseName;
  TmpSP.StoredProcName:=ListBox1.Items[ListBox1.ItemIndex];
  TmpSP.Prepare;
  ListBox2.Clear;
  For TmpI:=0 to TmpSP.ParamCount-1 do
    ListBox2.Items.Add(TmpSP.Params[TmpI].Name);
  TmpSP.Free;
end;

And there you go!

John.
0
 
JaymolCommented:
Hi MoonCalf.

Try this bit of code....

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, BDE;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    Button1: TButton;
    ListBox1: TListBox;
    procedure GetStoredProcNames;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.GetStoredProcNames;
var
  Database: TDatabase;
  Cursor: HDBICur;
  Name: string;
  Desc: SPDesc;
begin
  ListBox1.Items.BeginUpdate;
  try
    ListBox1.Items.Clear;
    Database1.Connected:=True;
    try
      Check(DbiOpenSPList(Database.Handle, False, True, nil, Cursor));
      try
        while DbiGetNextRecord(Cursor, dbiNOLOCK, @Desc, nil) = 0 do
          with Desc do
          begin
            NativeToAnsi(Database.Locale, szName, Name);
            ListBox1.Items.Add(Name);
          end;
      finally
        DbiCloseCursor(Cursor);
      end;
    finally
      Database1.Connected:=False;
    end;
  finally
    ListBox1.Items.EndUpdate;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  GetStoredProcNames;
end;

end.


You just need a form with a Button and ListBox on it.

This is NOT a generic procedure.  You'll have to reword it to make it work in any other instances.

Hope this helps you out,

John.
0
 
JaymolCommented:
Oh yeah, and a TDatabase component.

(I actually just pinched this from the TStoredProc code in the DBTables unit.)

John.
0
 
JaymolCommented:
Sorry, that was a bit buggy.....try this.


procedure TForm1.GetStoredProcNames;
var
  Cursor: HDBICur;
  Name: string;
  Desc: SPDesc;
begin
  ListBox1.Items.BeginUpdate;
  try
    ListBox1.Items.Clear;
    Database1.Connected:=True;
    try
      Check(DbiOpenSPList(Database1.Handle, False, True, nil, Cursor));
      try
        while DbiGetNextRecord(Cursor, dbiNOLOCK, @Desc, nil) = 0 do
          with Desc do
          begin
            NativeToAnsi(Database1.Locale, szName, Name);
            ListBox1.Items.Add(Name);
          end;
      finally
        DbiCloseCursor(Cursor);
      end;
    finally
      Database1.Connected:=False;
    end;
  finally
    ListBox1.Items.EndUpdate;
  end;
end;
0
 
MoonCalfAuthor Commented:
Perfect.

Thanks John.

MoonCalf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.