Comments embedded. On Dec 11, 8:45 am, trub3101 <[email protected]> wrote: > Hi David, > > Thank you for your swift reply and explanation. > > As you would have probably figured out my PL/SQL skills presently > leave a lot to be desired and so I am having some difficulty in > applying the principles you have explained to my current dilemma. > > Should I be assigning the select statement to the num_var variable? > > Thanks again for your help, > > tb3101 > > On 10 Dec, 18:43, ddf <[email protected]> wrote: > > > > > On Dec 10, 10:34 am, trub3101 <[email protected]> wrote: > > > > Hi all, > > > > Within my shell script I am trying to assign the result of a resultset > > > to a procedure thus > > > > table myuser > > > > ID email username > > > 01 [email protected] trub3101 > > > 02 [email protected] rtub3102 > > > > remove_myuser.ksh > > > > #!/bin/ksh > > > sqlplus /nolog<<EOF > > > connect myself/myself > > > > var num_var number > > > > begin > > > select id into :num_var from myuser where email = '[email protected]'; > > > end; > > > / > > > > exec remove_myuser_rec(num_var) ; --- Which is suppose represent exec > > > remove_myuser_rec(01) > > > > exit > > > EOF > > > > For some reason or another this does not work. I keep getting this > > > error: > > > > SQL> SQL> BEGIN remove_myuser_rec(num_var); END; > > > > * > > > ERROR at line 1: > > > ORA-06550: line 1, column 22: > > > PLS-00201: identifier 'NUM_VAR' must be declared > > > ORA-06550: line 1, column 7: > > > PL/SQL: Statement ignored > > > > Would any kind soul out there be able to explain why this is not > > > working? > > > > Many thanks in advance, > > > > tb3101 > > > Funny that you can set the variable with the proper syntax but can't > > use that same syntax to pass the variable to a program unit: > > > SQL> create or replace procedure showme(me in varchar2) > > 2 as > > 3 begin > > 4 dbms_output.put_line('This is me, me, me: '||me); > > 5 end; > > 6 / > > > Procedure created. > > > SQL> > > SQL> variable me varchar2(40) > > SQL> > > SQL> begin > > 2 :me := 'Yarkenplotz'; > > 3 end; > > 4 / > > > PL/SQL procedure successfully completed. > > > SQL> > > SQL> exec showme(:me) > > This is me, me, me: Yarkenplotz > > > PL/SQL procedure successfully completed. > > > SQL> > > > For your code it should be: >
This is how you should be writing your exec statement: > > exec remove_myuser_rec(:num_var) ; > > > David Fitzjarrell- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - Is the example not clear, given that it's your code I've modified? Possibly it was buried too far into the text. Let me reiterate the solution to you: exec remove_myuser_rec(:num_var) ; Notice the ':' prepended to your variable. You need this to allow SQL*Plus to reference the value within. David Fitzjarrell --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
