Thursday, May 31, 2007

PLS-00394 and PLS-00225

Though i have two years of experience as a software engineer, i have never ever written a oracle procedure, function etc.... These things are handled by my onsite client. So i didn't get the chance to write those. Now-a-days i have find time to read. I use this time to read those things, As usual i would like to share the learning with you.

My first oracle block:

Assume that emp is the table which has two columns. 1. employee_id and 2. employee_name

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select * from emp;
4 begin
5 open emp_cursor;
6 fetch emp_cursor into employee_id;
7 dbms_output.put_line(employee_id);
8 close emp_cursor;
9 end;
10 /

Good. I love to get the error, Then only i can learn too many things.

fetch emp_cursor into employee_id;
ERROR at line 6:ORA-06550: line 6, column 1:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored

Now we will analyse the code.

First thing is declaring a cursor ( cursor emp_cursor is select * from emp ) which fetch all the columns of the emp table.

Now i want to fetch the column into employee_id ( fetch emp_cursor into employee_id ). But here emp_cursor has two columns, the first column Employee_id of emp is assigned to employee_id but the Employee_name is not yet assigned to any value. This throws PLS-00394: wrong number of values in the INTO list of a FETCH statement

So PL/SQL Statement ignored.

This is the modified code for the above program,

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select employee_id from emp;
4 begin
5 open emp_cursor;
6 fetch emp_cursor into employee_id;
7 dbms_output.put_line(employee_id);
8 close emp_cursor;
9 end;
10 /

Here i have selected only employee_id as cursor.

Note :
I got this PLS-00225: subprogram or cursor 'EMP_CURSOR' reference is out of scope error when i tried to call the fetch emp_cursor.employee_id into employee_id; Cursor is nothing more than a pointer to a piece of memory

To resolve this error we have to declare a variable which is same as cursor's rowtype

SQL> declare
2 employee_id emp.EMPLOYEE_ID%Type;
3 cursor emp_cursor is select employee_id from emp;

4 emp_val emp_cursor%ROWTYPE;
5 begin
6 open emp_cursor;
7 fetch emp_cursor into emp_val;
8 dbms_output.put_line(emp_val.employee_id);
9 close emp_cursor;
10 end;
11 /


By doing this we dont mind about how many columns will be fetched by cursor. If multiple columns are fetched then we would be able to reference them via emp_val variable.

8 comments:

Anonymous said...

balamr.blogspot.com is very informative. The article is very professionally written. I enjoy reading balamr.blogspot.com every day.
guaranteed bad credit loans
payday loans

Anonymous said...

I just discovered the website who writes about
Several
home business reviews

If you want to know more here it is
home based business reviews
www.home-businessreviews.com

Anonymous said...

Agreement to pass the lascivious with two backs casinos? verify this late-model [url=http://www.realcazinoz.com]casino[/url] advisor and wing it depose online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
you can also into our untrained [url=http://freecasinogames2010.webs.com]casino[/url] eye-opener at http://freecasinogames2010.webs.com and uplift deep-rooted folding moolah !
another in [url=http://www.ttittancasino.com]casino spiele[/url] down is www.ttittancasino.com , completely than of german gamblers, earn a classify the ill-use goodness in manumitted online casino bonus.

Anonymous said...

Hello


Why should you pay ebay big money when you can use http://www.BuySellDirect.net for free to sell your products.


http://www.BuySellDirect.net is what Ebay users need to save paying charges when they sell or not.

Anonymous said...

We just couldnt leave your website before saying that we genuinely enjoyed the high quality information you offer for your visitors... Would be back frequently to check up on new stuff you post!

Anonymous said...

top [url=http://www.xgambling.org/]001[/url] brake the latest [url=http://www.casinolasvegass.com/]free casino games[/url] free no deposit perk at the best [url=http://www.baywatchcasino.com/]laid-back largesse casino
[/url].

Anonymous said...

[url=http://www.onlinecasinos.gd]casinos online[/url], also known as accepted casinos or Internet casinos, are online versions of commonplace ("buddy and mortar") casinos. Online casinos approve gamblers to filch up and wager on casino games guidance of the Internet.
Online casinos habitually beg odds and payback percentages that are comparable to land-based casinos. Some online casinos call on higher payback percentages as a peacefulness looking in espouse of m‚tier gismo games, and some obliging non-military payout say audits on their websites. Assuming that the online casino is using an correctly programmed indefinitely baby up generator, pr‚cis games like blackjack query an established barrier edge. The payout component voyage of uncovering of these games are established again non-standard extras to the rules of the game.
Heterogeneous online casinos testify to minus or stumble upon into the property of their software from companies like Microgaming, Realtime Gaming, Playtech, Worldwide Cunning Technology and CryptoLogic Inc.

Anonymous said...

buy ativan ativan uses and dosages - where can i buy ativan online