dynamic sql doesnt work?

I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

Originally posted by wakuku
I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================|||Originally posted by dmmac
I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".|||Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /|||Originally posted by LKBrwn_DBA
Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

I've tried, but failed again...saying
"Bind variable vscr not declared"

The one i'm using is Orcale8 Enterprise Edition Release 8.0.6.3.0|||Originally posted by wakuku
Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.|||Originally posted by andrewst
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.

I've tried this, but failed again.

It seems that "OPEN sqlstatement FOR cursor" can't be run in the SQL I'm using.

I've used other method, but it works. Here's my coding...
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source_Table.table_name%TYPE;
vSQLstr varchar(9999);
vTotal_Rank Integer;

TYPE cur_typ is REF CURSOR;

Cursor tableCursor IS
SELECT table_name FROM Source_Table;

rankCursor integer;
exeCursor integer;
updateCursor integer;

BEGIN
OPEN tableCursor;

LOOP

FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

rankCursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(rankCursor, 'SELECT rank, new_rank, count(rank) FROM rank_Mapping, '
|| vTable_Name || ' WHERE ltrim(rtrim(rank)) = ltrim(rtrim(old_rank))
GROUP BY rank, new_rank', DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(rankCursor, 1, vMap_Old_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 2, vCor_New_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 3, vTotal_Rank);

exeCursor := DBMS_SQL.EXECUTE(rankCursor);

updateCursor := DBMS_SQL.OPEN_CURSOR;

LOOP

IF DBMS_SQL.FETCH_ROWS(rankCursor) > 0 THEN

DBMS_SQL.COLUMN_VALUE(rankCursor, 1, vMap_Old_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 2, vCor_New_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 3, vTotal_Rank);

DBMS_OUTPUT.PUT_LINE(vTable_Name || ' ' || vMap_Old_Rank ||'-'||
vCor_New_Rank || ': ' || vTotal_Rank);

DBMS_SQL.PARSE(updateCursor, 'UPDATE ' || vTable_Name ||
' SET rank = :vCor_New_Rank WHERE rank = :vMap_Old_Rank',
DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(updateCursor, 'vMap_Old_Rank', vMap_Old_Rank);
DBMS_SQL.BIND_VARIABLE(updateCursor, 'vCor_New_Rank', vCor_New_Rank);

/* exeCursor := DBMS_SQL.EXECUTE(updateCursor); */

ELSE
EXIT;
END IF;

END LOOP;
IF DBMS_SQL.IS_OPEN(rankCursor) THEN
DBMS_SQL.CLOSE_CURSOR(rankCursor);
END IF;

IF DBMS_SQL.IS_OPEN(updateCursor) THEN
DBMS_SQL.CLOSE_CURSOR(updateCursor);
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

..............

END;
==============================================|||but it doesnt work if the ref cursor is declared with a return value.
for example, following would work
--------------
CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;
end;

END;
/
----------

but not following
-----------
-- Start of DDL Script for Package RECON.RCPACKAGE
-- Generated 2-Oct-2003 22:33:06 from RECON@.CGEN

CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR return Recx ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;

end;

END;
/
-----------

Originally posted by LKBrwn_DBA
YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||hi actually i really wanna help in this TASK could u please help me out as soon as possible.....i kindly thanxs for that...
----------------

There are a number of subtasks to this weekly task. PL/SQL is not necessary. It will be
sufficient to use SQL. However you will need a firm grasp of the concepts of Week 7
lectures to be able to do the subtasks.
1) Create the following table
StudentTableNotNormalised
StudId Name CourseCode CourseDesc Lecturer Grade Office
S1234 Jack C224 Database Codd D 381
S1234 Jack C225 Algorithms Djikstra P 380
S2345 Jill C224 Database Codd HD 381
S2345 Jill C226 Architecture Ritchie HD 390
S4567 Jack C226 Architecture Ritchie D 390
S4567 Jack C224 Database Codd F 381
S9872 Howard Cpol Politics Marx F 380
2) Design a schema for this table that is in second normal form but not third normal
form.
Demonstrate this schema (you can if you like use views from your original table
to demonstrate the schema and populations: Hint use Distinct, , alternatively
create the tables and populations with a script).
3) Design a schema for this that is in third normal form.
Demonstrate this schema (again you can use views, alternatively create the
tables and populations with a script)
4) Functional dependencies.
Create two SQL queries on the table StudentTableNotNormalised
to ascertain whether Lecturer functionally determines Office and conversely
whether Office functionally determines Lecturerer.
(Hint Use Distinct, Group by and count)