Solved

Oracle Stored procedures

Posted on 2001-07-12
5
237 Views
Last Modified: 2010-10-05
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.
0
Comment
Question by:MoonCalf
  • 4
5 Comments
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277145
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
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277153
Oh yeah, and a TDatabase component.

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

John.
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6277164
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
 
LVL 6

Accepted Solution

by:
Jaymol earned 50 total points
ID: 6277315
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
 
LVL 1

Author Comment

by:MoonCalf
ID: 6277325
Perfect.

Thanks John.

MoonCalf.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now