Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Unanswered: dynamic sql doesn't 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
    ==============================================

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: dynamic sql doesn't work?

    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
    ==============================================

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Re: dynamic sql doesn't work?

    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!!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2003
    Posts
    4

    Post wrong Code

    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".

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    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 /

    Last edited by LKBrwn_DBA; 08-18-03 at 14:37.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Aug 2003
    Posts
    4
    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

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Post wrong Code

    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.

  8. #8
    Join Date
    Aug 2003
    Posts
    4

    Re: Post wrong Code

    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;
    ==============================================

  9. #9
    Join Date
    Oct 2003
    Posts
    1

    Re: dynamic sql doesn't work?

    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!!


  10. #10
    Join Date
    Oct 2003
    Location
    sydney
    Posts
    1

    RELATIONAL DATABASE DESIGN

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •