Re: Simple Create Procedure Problem..

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 30 Nov 2004 06:57:54 -0500
Message-ID: <8YGdnSsi6KTX_THcRVn-1A@comcast.com>

"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.

|

| Regards
| HJR

|
|

| >
| > Arijit, study up on Oracle's PL/SQL Users guide -- do you have one or
know
| > where to get it?
| >
| > ++ mcs
| >
| >

wow! that was unexpected!

'slow down' was meant as nothing more than a friendly, 'hey, don't forget', or whatever you would normally interpret as a non-confrontational additional comment

i am truly sorry that it was taken as an affront -- that was certainly not my intent. based on the content of all your other posts and publications, i simply assumed you had overlooked something that you were well aware of.

however, since you apparently are of a different opinion on the technical issues, let me elaborate a little

i stand by the statement that execute immediate is unnecessary, as there is no dynamic sql involved here. if i'm not mistaken, execute immediate also causes additional parses (so i recall from my recent readings, i've not done tests to empirically prove this). i would not want any of my students, readers, or developers to get in the habit of using execute immediate for non-dynamic sql.

providing the OP with an example that lacks a basic and typical element, like the into clause, can be, i believe, misleading -- especially to a newbie. yes, the code does compile, but it does not accomplish anything -- and it is not a stretch to assume that if a select statement is being issued, then the point is to have access to the results.

borrowing from the 'teach a man to fish' illustration -- if someone asks me how to put the worm on the hook and he's standing there with just a pole and a hook, i would think it appropriate to remind him not to forget to connect the hook to the pole with a bit of fishing line.

sorry again for my poor choice of words -- your posts are always valued, and i certainly welcome any corrections or challenges you care to provide for my posts.

++ mcs Received on Tue Nov 30 2004 - 05:57:54 CST