Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    10

    Unanswered: forms, data blocks, and pl/sql

    i'm just learning forms, so be gentle.

    What I'm trying to accomplish is creating a canvas whose text items get values froms a stored pl/sql procedure.

    Can anyone point me to some sample code that shows how to do this?

    thanks in advance.

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: forms, data blocks, and pl/sql

    You will want to create a data block where the source is a stored procedure instead of the default (table). You can always use the Data Block Wizard to help you in creating your block.

    Originally posted by bhambud
    i'm just learning forms, so be gentle.

    What I'm trying to accomplish is creating a canvas whose text items get values froms a stored pl/sql procedure.

    Can anyone point me to some sample code that shows how to do this?

    thanks in advance.

  3. #3
    Join Date
    Jun 2003
    Posts
    10

    Re: forms, data blocks, and pl/sql

    Originally posted by carloa
    You will want to create a data block where the source is a stored procedure instead of the default (table). You can always use the Data Block Wizard to help you in creating your block.
    Got that part; now does anyone have an example of what the pl/sql looks like? The following below gives pls-00049.

    CREATE OR REPLACE PROCEDURE gtest3
    is
    query_output REFCURSOR;
    BEGIN
    open :query_output for
    select line from dookie;
    end;

    All I want is to get the value of dookie.line displayed on the form.

  4. #4
    Join Date
    Jun 2003
    Location
    Austria/Vienna
    Posts
    2

    Re: forms, data blocks, and pl/sql

    Originally posted by bhambud
    i'm just learning forms, so be gentle.

    What I'm trying to accomplish is creating a canvas whose text items get values froms a stored pl/sql procedure.

    Can anyone point me to some sample code that shows how to do this?

    thanks in advance.
    Hi,

    I would suggest you are going to use blocks based on a pl/sql table. ( but this will require forms 6i or above )

    1 Step. create a pl/sql procedure in your database taking an pl/sql table as in/out parameter. (eg. 'type mytable is table of YOUR_DATA_TYPE index by binary_integer ' )

    2. In Forms using the block-wizard select 'block based on an stored procedure'. You will be asked for the name of the procedure. After you have declared your procedure
    the block wizard examines ist and let you choose which data you want to display.

    3. You can define a procedure for SELECT, INSER, UPDATE, DELETE if you want.

    4. You will get an new block with a query-procedure trigger ( do not edit this trigger manually, forms will take care of that )

    5. populate your pl/sql table in your procedure and you will automatically see the data in your forms application.

    6. if you need input params for your query, you can define
    multiple 'in' parameters in your pl/sql procedure.
    to bind them to an text-field of an block use the 'query data source arguments' parameter in the property palette of this block. ( eg. ':block1.textitem1' )

    hope this helps

  5. #5
    Join Date
    Jun 2003
    Posts
    10

    finis

    I found a solution. Here's how it goes.

    STEPS
    1. create a stored procedure defined like this in a text file.

    create or replace procedure bigun3
    ( BID IN NUMBER,
    OBATNUM OUT VARCHAR2 ,
    opsdate OUT varchar2 ,
    oasdate OUT varchar2 ,
    oecdate OUT varchar2 ,
    oacdate OUT varchar2 )
    is
    begin
    declare batnum varchar2vari(32);
    psdate date;
    asdate date;
    ecdate date;
    acdate date;
    cursor sbat is select batch_no, plan_start_date,
    actual_start_date, expct_cmplt_date, actual_cmplt_date
    from pm_btch_hdr@smt2gemms where
    batch_id = BID;
    begin
    open sbat;
    fetch sbat into batnum, psdate, asdate, ecdate, acdate;
    obatnum := batnum;
    opsdate := to_char(psdate, 'MM DD');
    oasdate := to_char(asdate, 'MM DD');
    oecdate := to_char(ecdate, 'MM DD');
    oacdate := to_char(acdate, 'MM DD');
    end;
    end;
    /

    (yes I know that you'd need to close the cursor, but this is an EXAMPLE, ok?).

    The procedure works like this, given an input of BID, return a batch_no, and 4 dates.

    once compiled in sqlplus, this can run there by the execute command.

    2. create a button on a form. The form should already have been setup to use a table as a data-block. In this example, I used a table called bfd.

    3. go into the pl/sql editor for that button's when button pressed event and add the following code:

    bigun3 ('2376577', :bfd.tag_location, :bfd.default_warehouse,
    :bfd.default_warehouse, :bfd.default_warehouse, :bfd.default_warehouse);

    this will execute the 'bigun3' procedure. We supply the input arg of '2376577' and get back 5 output values. Note that the :bfd.<columnname> is how the text items in the form get the data.

    4. run the form.

    That's pretty much it.

    The problem really is trying to understand how a value is passed to a stored procedure and how a value is retrieved from one from WITHIN FORMS. Once the syntax is understood, it makes some sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •