Solved

identifier 'V_CUR' must be declared

Posted on 2004-09-07
5
2,759 Views
Last Modified: 2008-01-09
Hello guys

What's wrong ?
v_Cur is a ref cursor

begin
  v_Cur := GetALLRecordByIDG(FTable, FIDG);
  select max(ID) into v_maxid from v_Cur;    //------------------ Error : *
  return(v_maxid);
end;

*"Error: PLS-00201: identifier 'V_CUR' must be declared
Line: 93
Text: select max(ID) into v_maxid from v_Cur;"
0
Comment
Question by:wwwbetyar
  • 3
5 Comments
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11995024
Is there a DECLARE section missing, where v_Cur is defined?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11995071
Ref Cursor should also be declared.
0
 

Author Comment

by:wwwbetyar
ID: 11996062
v_Cur is defined in the package header !

Header :
type
       refcur is ref cursor;  
...



Body :

function GetALLRecordByIDG(FTable in varchar2, --melyik tábla..
                           FIDG in number)     --..melyik IDG-je alapján
  return refcur is      
  v_Cur  refcur;    
  v_sql  long;
-- MODIFICATION HISTORY
-- Person          Date         Comments
-- --------------  -----------  -------------------------------------------
-- Visszatér az összes rekorddal amelynek IDG-je alapjan azonos (csak IDG-t tudjuk)
-- --------------  -----------  -------------------------------------------
-- Zalán Péter     2004.09.06.   Létrehozás
-- --------------  -----------  -------------------------------------------    
begin
  v_sql := 'select * from '||FTable||' where IDG='||to_char(FIDG);
  open v_Cur for v_sql;
  return(v_Cur);
end;  

and now ????
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11996118
v_Cur cannot be accessed like that in the calling procedure.

What you have to do is something like:

-- test procedure part of the package defining "refcur"
PROCEDURE test_proc
IS
    v_Cur refcur;
BEGIN
    v_Cur := GetALLRecordByIDG(FTable, FIDG);
    -- etc, etc...
END;
/


0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 50 total points
ID: 11996213
my previous post explains the 'rror: PLS-00201: identifier 'V_CUR' must be declared' error... however, you cannot use a cursor as a table/view in a select statement (as is tried in the following):

  1  declare
  2  type refcur is ref cursor;
  3  v_cur refcur;
  4  i number;
  5  begin
  6  open v_cur for 'SELECT 1 a, 2 b, 3 c, 4 d FROM dual';
  7  select max(a) into i from v_cur;
  8  dbms_output.put_line( 'max(a) : ' || i );
  9* end;
SQL> set serveroutput on
SQL> /
select max(a) into i from v_cur;
                          *
ERROR at line 7:
ORA-06550: line 7, column 27:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored


What you should try and do is join the two procedures into one call...

DECLARE
    v_maxid NUMBER;
    v_id NUMBER := 100; -- whatever, probably parameter!
BEGIN
    SELECT max(id) INTO v_maxid
    FROM table_name WHERE id = :id
    USING v_id;
END;
/

However, it looks like you want to use one function to build generic select statements based on table_name and ID parameters.
so to do this in a single step:

CREATE FUNCTION get_maxid( in_tname IN  VARCHAR2, in_id IN VARCHAR2 ) RETURNS NUMBER
IS
    v_max NUMBER;
BEGIN
    -- Use placeholder to help Oracle cache SQL better!
    EXECUTE IMMEDIATE 'SELECT max( id ) FROM ' || in_tname || ' WHERE IDG = :id'
    INTO v_max
    USING in_id;
    RETURN v_max;
END;
/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question