Solved

Oracle Stored procedures

Posted on 2001-07-12
5
239 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

14 Experts available now in Live!

Get 1:1 Help Now