Re: Problem returning PL/SQL table types

Hi,

[Quoted] 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 [Quoted] to do it this way when it appears to be silly ! The users have specifically [Quoted] requested "like" searches to be default in this top block without having to [Quoted] add in % signs. Originally we were doing as you suggested and sticking a % [Quoted] after the field. Oracle was converting this to a literal (apparently this is [Quoted] an unfixed bug in v7.3.4) so we were getting no re-use of the parsed code in [Quoted] the SGA, which as this is the main search screen was causing pretty serious [Quoted] 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 [Quoted] 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 [Quoted] happy with, but the whole experience has left me wondering how on earth you [Quoted] can possibly return a pl/sql table from a stored procedure as this is where [Quoted] 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 [Quoted] same type as the one declared in the package?

Thanks,
Jacqui

[Quoted] "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 [Quoted]
> 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
> >
> >
>
>
Received on Fri Dec 01 2000 - 09:46:17 CET