Re: Problem returning PL/SQL table types

In article <3a2772e4$1_at_news.norweb.net>,   "Jacqui Spencer" <jacqui.spencer_at_lancscc.gov.uk> wrote:
> Hi,
>
> Thanks for the suggestion, you're right of course that would seem the
> sensible way at first sight, I probably should have explained why I'm
 trying
> to do it this way when it appears to be silly ! The users have
 specifically
> requested "like" searches to be default in this top block without
 having to
> add in % signs. Originally we were doing as you suggested and
 sticking a %
> after the field. Oracle was converting this to a literal (apparently
 this is
> an unfixed bug in v7.3.4) so we were getting no re-use of the parsed
 code in
> the SGA, which as this is the main search screen was causing pretty
 serious
> performance issues. I'm doing it this way because then I can use the
> DBMS_SQL package and manually parse the code before binding the
 variables
> thus making oracle see bind variables and reuse the SGA code. I've got
> around my problem by plonking the procedure in the form, which I'm
 fairly
> happy with, but the whole experience has left me wondering how on
 earth you
> can possibly return a pl/sql table from a stored procedure as this is
 where
> I'd prefer the code to sit, is it possible and if it is how do get the
> variable you supply to take the OUT parameter to recognise that it's
 the
> same type as the one declared in the package?
>
> Thanks,
> Jacqui
>

We are all presuming that you have tried to do a pre-query trigger to convert the data with in the variable to have a concatenated '%'

       if :block1.variable1 is not null
       then
            :block1.variable1 := :block1.variable1 || '%';
            -- repeat for all other variables
       end if;

I would suggest you review the performance of your system. Re-use or not, the query should be rapid. In general, if each user is looking up a different entity, Oracle will have to generate a new SQL for it. Reuse  would only happen if the identical query was run each time.

Remember the rationale for using forms is for forms to do most/all of the work. Building and populating you own block should be done only as a last resort or when a feature is missing. 'Like' queries are built into forms.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers



> "ml" <mluo_at_yesic.com> wrote in message


> news:6XCV5.144$HT23.32375129_at_news.randori.com...

> > Hi,

> > Try to use the way Forms provides:

> > 1. Set the second block based on a view or table.

> > 2. The first block seems to be a control block. You can add a push

 button
 on


> > this block, and in the trigger, construct the where_clause string

 based on


> > the input values.

> > write other codes :

> > ...

> > SET_BLOCK_PROPERTY('THE_SECOND_BLOCK_NAME',  DEFAULT_WHERE,

 where_clause);


> > GO_BLOCK('THE_SECOND_BLOCK_NAME');

> > EXECUTE_QUERY;

> >

> > ...

> > END;

> >

> >

> > That's all.

> >

> > ml

> >

> >

> > "Jacqui Spencer" <jacqui.spencer_at_lancscc.gov.uk> wrote in message

> > news:3a264b38$1_at_news.norweb.net...


> > > Hi,

> > >

> > > I had a form with two blocks in it I wanted to enter details in

 the
 first


> > > (IN) in order to query the second (OUT). The fields between the

 two
 blocks


> > > were different so basing the OUT block on a stored procedure

 seemed like


> > > hassle as I suppose I'd have had to include all the fields from

 the IN
 block


> > > in order to pass them in. The query on the out block also had to

 be
 dynamic


> > > so I created a procedure using the DBMS_SQL package to take the

 values
 from


> > > the first block create the appropriate query and fetch the

 results into
 a


> > > PL/SQL table which would be an OUT parameter. The PL/SQL table is

 declared


> > > with explicit datatypes not table%rowtype if this makes a

 difference. I


> > > wanted to use this same procedure in a few different forms so

 storing it
 on


> > > the database was the plan.

> > >

> > > My problem is this  - I now have a package with a PL/SQL table

 declared


> > > above the procedure which returns it and this compiles fine, what

 I
 can't
 do


> > > though is call the procedure from a form. I've tried declaring the

 pl/sql


> > > table type almost everywhere, in the package, in the call, in

 both, and
 it


> > > always complains that the supplied parameters are of the wrong

 type.
 I've


> > > tried it as an IN OUT parameter and couldn't get this to work

 either.
 How
 on


> > > earth do you have a PL/SQL table as an OUT parameter ? According

 to my


> > > manuals it's possible in a procedure but not a function.

> > >

> > > In the example above I've had to put the procedure in the form and

 return


> > > the results direct to the block, but because I wanted to reuse

 the code
 I'd


> > > like to be able to crack this problem and get it as a stored

> > > package/procedure.

> > >

> > > Any help would be great - it's driving me nuts now.

> > > TIA,

> > >

> > > Jacqui

> > > Jacqui.Spencer_at_its.lancscc.gov.uk

> > >

> > >

> >

> >

>

>




Sent via Deja.com http://www.deja.com/
Before you buy.