Okay,
I have had a few hours sleep and cleared my head.
Basically, the logic in my procedure was all over the place and the
procedure included what where essentially redundant variables.
The procedure to receive the passing variable from the script should
simply have been:
CREATE OR REPLACE procedure remove_myuser_rec(
p_id in number)
is
begin
delete from myuser
where mu_id = p_id;
/*
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('A User with an ID of '||p_userid||' cannot be
found in the database');
*/
end remove_myuser_rec;
/
I knew it would be fundamental in the end.
Thanks again David for your input.
tb3101
On 11 Dec, 22:12, trub3101 <[email protected]> wrote:
> 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 -- 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
-~----------~----~----~----~------~----~------~--~---
