Must declare p_state

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Must declare p_state

Postby braindead » Fri Feb 24, 2012 1:51 pm

Tim,

I cloned this from your example

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

The first prece complies with out errors, but the second section fails with this error.

You did not declare this in your example:



Code: Select all
SQL> @q5_refcursor

Procedure created.

   get_stores (p_state = 'CA', p_recordset => l_cursor);
               *
ERROR at line 7:
ORA-06550: line 7, column 16:
PLS-00201: identifier 'P_STATE' must be declared
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 10, column 11:
PLS-00201: identifier 'V_CURSOR' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored


SQL> spool off



*************************************
Code: Select all
CREATE OR REPLACE PROCEDURE get_stores (p_state IN stores.state%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_recordset
FOR
SELECT
   stor_name,
   stor_address,
   city
FROM
   stores
WHERE
   state = p_state
ORDER BY city;
END;
/

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
l_cursor SYS_REFCURSOR;
l_stor_name      stores.stor_name%TYPE;
l_stor_address   stores.stor_address%TYPE;
l_city           stores.city%TYPE ;
BEGIN
   get_stores (p_state = 'CA', p_recordset => l_cursor);
LOOP FETCH
   l_cursor INTO l_stor_name, l_stor_address, l_city;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stor_name || ' | ' || l_stor_address || ' | ' || l_city);
END LOOP;
CLOSE l_cursor;
END;
/
braindead
Member
 
Posts: 3
Joined: Thu Feb 23, 2012 2:44 pm

Re: Must declare p_state

Postby Tim... » Fri Feb 24, 2012 2:18 pm

Hi.

Look at this line,

Code: Select all
get_stores (p_state = 'CA', p_recordset => l_cursor);


You've used "=" instead of "=>", which is the problem. The line should be,

Code: Select all
get_stores (p_state => 'CA', p_recordset => l_cursor);


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Must declare p_state

Postby braindead » Fri Feb 24, 2012 3:15 pm

Hi Tim,

Thanks!

Now I get this error.

On your page I don't see v_cursor reference anywhere!

http://www.oracle-base.com/articles/mis ... rdsets.php

BTW, do you teach public courses in PL/SQL.

I love England and I need an excuse to go there!

Code: Select all
SQL> @q5_refcursor

Procedure created.

EXIT WHEN v_cursor%NOTFOUND;
          *
ERROR at line 10:
ORA-06550: line 10, column 11:
PLS-00201: identifier 'V_CURSOR' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored


SQL> spool off
braindead
Member
 
Posts: 3
Joined: Thu Feb 23, 2012 2:44 pm

Re: Must declare p_state

Postby Tim... » Fri Feb 24, 2012 3:28 pm

Hi.

Whoops. That's a typo. It should have been l_cursor, as defined in the DECLARE block. I've amended the article now. Sorry.

As for classes. I've taught some for Oracle University. Don't do any private classes.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest

cron