Re: Using dynamic SQL in Developer 2000

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/06/10
Message-ID: <865984676.5912_at_dejanews.com>#1/1

In article <339C10D5.55D2_at_iil.intel.com>,   Maoz Mussel <mmussel_at_iil.intel.com> wrote:
> I would like to run dynamic SQL statement in Developer 2000.
> In particular, I would like to run the following query:
> SELECT count (*) FROM table_name WHERE|| where_var
>
> Where 'where_var' will be a variable including the where statement.
> I know I should be doing it using DBMS_SQL (is there another way ???),
> but:
> 1) Current version of D2k doesn't support pl/sql 2 which I need to
> run such a query.
> 2) I try to run it directly from the server, and couldn't found the
> correct syntax.

First, you need to create a stored procedure since Forms cannot call DBMS_SQL.COLUMN_VALUE directly. This is due to Forms using PL/SQL version 1. You can use the following script to create the necessary procedure:

create or replace
procedure aadbms_column_value

    (CursNum in     integer,
     ColNum  in     integer,
     ColVal  in out varchar2,
     ColLen  in out integer)
  is
     ColErr  number;

begin
  dbms_sql.column_value(CursNum,ColNum,ColVal,ColErr,ColLen); end;

/
show errors procedure aadbms_column_value

The following code can be run from a procedure or trigger in a Form. I used a table called 'NAME'. You can change the table name and add a where clause to your version. TblData is used for text data, but it works with count(*). If you need numeric data, you may need to define your column differently.

  Declare
    Cmd Varchar2(2000):='Select COUNT(*) from NAME';

    ColLen   Integer;
    CursNum  Integer;
    Result   Integer;

    TblData Varchar2(10);

  Begin
    CursNum := DBMS_SQL.Open_Cursor;
    DBMS_SQL.Parse(CursNum,Cmd,1);
    DBMS_SQL.Define_Column(CursNum,1,TblData,10);     Result := DBMS_SQL.Execute(CursNum);     Result := DBMS_SQL.Fetch_Rows(CursNum);     AADBMS_Column_Value(CursNum,1,TblData,ColLen);     DBMS_SQL.Close_Cursor(CursNum);
    Message('Success: Count(*) = '||TblData);   Exception When Others then
    Declare SQLerr Varchar2(200) := SQLerrm;     Begin

      IF CursNum is not null then
        DBMS_SQL.Close_Cursor(CursNum);
      End If;
      Message(' DBMS_SQL Error: ',SQLerr);
      Raise Form_Trigger_Failure;

    End;
  End;

Regards,
Steve Cosner

---

Try out QA, a dynamic data update/display form--an essential tool for Oracle developers: http://members.aol.com/stevec5088

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Tue Jun 10 1997 - 00:00:00 CEST