Re: Simple Create Procedure Problem..

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.

Regards
HJR
>
> Arijit, study up on Oracle's PL/SQL Users guide -- do you have one or know
> where to get it?
>
> ++ mcs
>
>
Received on Tue Nov 30 2004 - 05:04:02 CST