Re: Simple Create Procedure Problem..

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Tue, 30 Nov 2004 12:22:45 GMT
Message-ID: <pCZqd.53705$K7.53626@news-server.bigpond.net.au>

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41ac53a3$0$17542$afc38c87_at_news.optusnet.com.au...
> Mark C. Stock wrote:

>> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
>> news:41ac00bc$0$17542$afc38c87_at_news.optusnet.com.au...
>> | Arijit Chatterjee wrote:
>> | > RESPECTED FACULTIES,
>> | > I AM TRYING TO WRITE A PROCEDURE CODE IS GIVEN BELOW
>> | > ===========================================
>> | > CREATE PROCEDURE PROC(ACCNO IN NUMBER)
>> | > AS
>> | > BEGIN
>> | > SELECT * FROM ACCOUNTMASTER WHERE ACCOUNTCODE=ACCNO;
>> | > END;
>> | > ============================================
>> | > aND I AM GETTING THE ERROR
>> | >
>> | > --------------------------------------------
>> | > Warning: Procedure created with compilation errors.
>> | > --------------------------------------------
>> | > I HAVE CONNECTED WITH SCOTT/TIGER AND SCOTT HAS GIVEN THE
>> | > PRIVILEGES TO CREATE PROCEDURE.
>> | >
>> | > NOW WHAT SHOULD BE THE REASON FOR THIS ERROR?
>> | >
>> | > REGARDS
>> | > ARIJIT CHATTERJEE
>> |
>> |
>> | How about learning how to type "show errors" when you receive that
>> message?
>> |
>> | If you did, maybe you'd see something like this:
>> |
>> | SQL> create procedure proc (accno in number)
>> |    2  as
>> |    3  begin
>> |    4  select * from emp where empno=accno;
>> |    5  end;
>> |    6  /
>> |
>> | Warning: Procedure created with compilation errors.
>> |
>> | SQL> show errors
>> | Errors for PROCEDURE PROC:
>> |
>> | LINE/COL ERROR
>> | -------- -----------------------------------------------------------------
>> | 4/1      PLS-00428: an INTO clause is expected in this SELECT statement
>> |
>> | Which kind of gives you the clue as to where the error lies. And one
>> | possible solution would be:
>> |
>> | SQL> create or replace procedure proc (accno in number)
>> |    2  as
>> |    3  begin
>> |    4  execute immediate 'select * from emp where empno=' || accno;
>> |    5  end;
>> |    6  /
>> |
>> | Procedure created.
>> |
>> |
>> | But this is extremely basic stuff, so you'd best start reading up on 
>> how
>> | to write PL/SQL.
>> |
>> | Regards
>> | HJR
>>
>> slow down, howard....
>

> I seldom write in haste. And in this specific case, I definitely didn't do
> so, because I went to the effort of checking my suggested code in both 10g
> and 9i... so, could you perhaps leave little gems like this out in future?

>
>> your fix steps backwards a bit by introducing execute immediate where not
>> necessary, and still not providing an INTO clause
>

> I don't know what's necessary (and neither do you), other than what the OP
> is utterly explicit about: why doesn't his code compile? He didn't mention
> needing an INTO clause, nor wanting to avoid the use of 'execute
> immediate'. Besides which, I mentioned specifically that what I wrote was
> *one possible* solution, and accordingly never pretended it was best
> practice, or that it excluded other possibile solutions.

>

> The question you should ask yourself is, what did the OP actually ask?
> (Answer: "WHAT SHOULD BE THE REASON FOR THIS ERROR?").

>

> Then you could ask, does the code sample I provided compile properly?
> (Answer, yes)

>

> And from that we can deduce whether my code sample could help him see why
> his code sample doesn't work.

>

> And that was all I slowly and carefully set out to do, thanks all the
> same.

>
>> perhaps you meant something more like this:
>>
>

> I meant precisely what I wrote, thank you.

>
>> create or replace procedure proc (accno in number)
>> as
>>   r_emp emp%rowtype;
>> begin
>>      select *
>>      into   r_emp
>>      from emp where empno = accno;
>>
>>      -- then do something with r_emp
>> end;
>
>

> And you've now just selected a value into a variable when the OP, I would
> guess from the actual content of his post, has precisely no understanding
> of variables, nor what to do with them. Bully for you, then.

>

> I don't know about you, but I spend most of my professional career helping
> people who know nothing know something. I do it step by step, and that
> means I might have to do or say things in a particular way at a particular
> time. Things which some guru (or know-it-all student) starts complaining
> about because it misses out X, or Y or Z. But I do it for a reason, and we
> get there in the end.

>

> Instead of running away with assumptions about what he might or might not
> want to do with the result from the table, examine what he was actually
> posting about. He had a piece of code which wasn't compiling, because he
> was trying to put a bare SQL statement into a piece of PL/SQL. That's all.

>

> I have no doubt at all that your code is slicker and more professional and
> technically more accomplished than mine... but there are ample grounds for
> believing that mine was closer to his original in intent and execution,
> and therefore a smaller step on the path to the real answer here which is
> that the guy needs to learn about PL/SQL.

>

> So next time, feel free to offer your alternative solutions, because they
> are of course worthwhile and technically neat, but please don't do so as a
> "correction" of mine, especially since I never offered mine as the "right"
> way to do it in the first place. And if you could curb the desire to play
> mind-reading games regarding what you think I meant to say, I'd be
> grateful.

>

Hi Howard,

Can you provide any practical examples of why one would want to use an "execute immediate" for a basic select statement within PL/SQL ? Although it's certainly desirable to have code that compiles correctly, shouldn't it also actually accomplish something as well ?

Just curious ...

Richard Received on Tue Nov 30 2004 - 06:22:45 CST