• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2906
  • Last Modified:

identifier 'V_CUR' must be declared

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
wwwbetyar
Asked:
wwwbetyar
  • 3
1 Solution
 
cjjcliffordCommented:
Is there a DECLARE section missing, where v_Cur is defined?
0
 
schwertnerCommented:
Ref Cursor should also be declared.
0
 
wwwbetyarAuthor Commented:
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
 
cjjcliffordCommented:
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
 
cjjcliffordCommented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now