REF CURSOR and Dynamic Sql

[rdharmar]

I have declare a REF cursor in the Package Specification:

TYPE type_cur IS REF CURSOR RETURN track_rec_type;

PROCEDURE prg_ConceptTrackMatrix_rpt(cur_TrackMatrix IN OUT PRG_EDP_REPORTS.type_cur);

In the Package body:

v_str_sql:= 'select card_cd as card_num from table_card';
v_openCur:= 'BEGIN OPEN cur_TrackMatrix '||'FOR '||v_str_sql||'; END;';

EXECUTE IMMEDIATE v_openCur;


When I execute this procedure I get a an error
"identifier 'CUR_TRACKMATRIX' must be declared"

I would greatly appreciate if someone could point me to what I am doing wrong.

Thanks

 

[sem]

When you execute some code dynamically, it has no access to your locally-defined objects. Thus to be accessible from dynamic code your cusor should be defined as "global" (declare it within a package specification). BTW, why do you need to open that cursor within dynamic code? Why don't you use simple

OPEN cur_TrackMatrix FOR v_str_sql;


Regards, Dima

http://seminihin.narod.ru

 

[rdharmar]

I have defined the cursor in the package specification and I think that should make it global.

I have now declared the cursor, as a week cursor in the specification ( TYPE type_cur IS REF CURSOR). I was able to execute this as a normal procedure but get the error only when I make it dynamic.

I still get the error "identifier 'CUR_TRACKMATRIX' must be declared".

In the problem statement above, I have left out the "where clause" (dynamic portion) for brevity.

 

[sem]

Obviously, because dynaic sql is executed in its own environment, thus you have to use fully specified names:

<package>.CUR_TRACKMATRIX

You should recognize that once you have dynamic sql, the code being executed is no more within the same namespace as surrounding code.

Regards, Dima

http://seminihin.narod.ru