Hi David,
Thanks again for your input.
I was getting various errors when I did as you suggested pre-post
which why I thought you might have meant something else.
Currently, I am getting 'no_data_found' exceptions when I attempt to
run the script. See below (apologises for lengthy post!)
1.TABLE DESCRIPTION
SQL> desc myuser
Name
Null? Type
-----------------------------------------------------------------------------
-------- ----------------------------------------------------
MU_ID
NUMBER(4)
MU_EMAIL
VARCHAR2(30)
MU_USERNAME
VARCHAR2(20)
2.SELECT FROM TABLE
SQL> select * from myuser;
MU_ID MU_EMAIL MU_USERNAME
---------- ------------------------------ --------------------
1 [email protected] trub3101
2 [email protected] trub3102
3 [email protected] trub3103
4 [email protected] trub3104
SQL> i
2 where mu_id = 1
3 /
MU_ID MU_EMAIL MU_USERNAME
---------- ------------------------------ --------------------
1 [email protected] trub3101
3.PROCEDURE SOURCE CODE
SQL> select text from user_source where name = 'REMOVE_MYUSER_REC'
2 /
TEXT
----------------------------------------
procedure remove_myuser_rec(
p_Email in varchar2)
is
p_userid myuser.mu_id%TYPE;
v_email myuser.mu_email%TYPE;
p_username myuser.mu_username%TYPE;
begin
select mu_id, mu_email, mu_username into
p_userid, v_email, p_username
from myuser
where v_email = p_Email;
delete from myuser
where mu_id = p_userid;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('A User with an I
D of '||p_Email||' cannot be found in th
e database');
end remove_myuser_rec;
4.RUNNING delete_myuser SCRIPT
./delete_myuser.ksh [email protected]
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Dec 11 21:57:42 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> 2 3 4
PL/SQL procedure successfully completed.
SQL> SQL>
NUM_VAR
----------
1
SQL> SQL> A User with an ID of 1 cannot be found in the database
PL/SQL procedure successfully completed.
SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release
9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
I am obviously missing something very fundamental here (woods for the
trees and all that!)
Thanks again for your efforts regarding this.
tb3101
On 11 Dec, 18:15, ddf <[email protected]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
