Re: user interaction with procedure

On May 10, 8:20 am, Andrea <netsecur..._at_tiscali.it> wrote:
> On 10 Mag, 14:56, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On May 10, 7:50 am, Andrea <netsecur..._at_tiscali.it> wrote:
>
> > > hi,
> > > i would like to run this simple procedure:
>
> > > create or replace procedure p_test as
> > > BEGIN
> > > FINE := 'NO';
> > > EXIT WHEN FINE = 'YES';
> > > accept ID char prompt "INSERT DOT FOR CONTINUE"
> > > select * from prova;
> > > IF ID := '.' THEN
> > > FINE := 'YES';
> > > END IF;
> > > END LOOP;
> > > END;
> > > /
>
> > > but it sho me error:
>
> > > 6/8 PLS-00103: Encountered the symbol "ID" when expecting one of the
> > > following:
> > > := . ( @ % ;
>
> > > can someone help me please?
>
> > > thanks
>
> > You cannot use 'accept' in a PL/SQL block; it is a SQL*Plus command.
> > AFAIK you cannot have an interactive PL/SQL block. What, exactly, are
> > you trying to accomplish? There is likely a much better way to do
> > whatever it is you're trying to do.
>
> i have this sql for to verify a input barcode from user :
>
> accept ID char prompt "INSERT BARCODE TO VERIFY:"
>
> select a.identifier,b.card_number_1
> card_number,a.surname,a.name,b.clear_code,a.parameter_2,
> a.parameter_3,b.on_ctu_disable
> from ac_employee a,ac_card b,ac_card_site c
> where a.identifier like '%&ID'
> and b.clear_code like '%&ID'
> and c.card_id in (select card_id from ac_card where clear_code like
> '%&ID');
>
> now i would like to input more than one barcode (even a list of
> barcode), and i have think that a loop with a procedure is the better
> choice..
> there is some method for gather this result?
>
> thanks- Hide quoted text -
>
> - Show quoted text -

I should think either a Pro*C/C++, OCI or OCCI program would be more suited to such activity. PL/SQL is not intended to be an interactive language. Even a shell script would provide such functionality as you can read input from the keyboard; without using a list of values a shell script would need to open a connection for each barcode verified, verify or reject the barcode entered and close that connection, looping for more input from the user. A Pro*C/C++, OCI or OCCI program could open one connection and use it to verify any number of barcodes, a more efficient mechanism.

I would consult the documentation at http://tahiti.oracle.com.

David Fitzjarrell Received on Thu May 10 2007 - 08:44:34 CDT