Re: PL/SQL: declaration of cursor is incomplete or malformed

On 14 Mar 2003 10:51:57 -0800, urbannoizz_at_yahoo.com (Julia S) wrote:

>Hi,
>
>I am new to PL/SQL and my first simple program returns the following
>errors:
>
>3/10 PLS-00341: declaration of cursor 'C1' is incomplete or
>malformed
>4/6 PL/SQL: SQL Statement ignored
>4/41 PLS-00201: identifier 'TABLE_NAME' must be declared
>8/7 PL/SQL: Item ignored
>19/7 PL/SQL: SQL Statement ignored
>19/21 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>21/7 PL/SQL: Statement ignored
>21/27 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>
>I am using Oracle 8.1.7 and PL/SQL function listing is:
>
>
>CREATE OR REPLACE FUNCTION get_pkey_list(table_name IN VARCHAR, query
>IN VARCHAR2) RETURN VARCHAR2 IS
>
> CURSOR c1 IS
> SELECT /*+FIRST_ROWS */ pkey FROM table_name
> WHERE contains(search_text, query, 10) > 0
> order BY score(10) desc, pkey desc;
>
> r1 c1%ROWTYPE;
> pkey_rec VARCHAR2(8);
> res_str VARCHAR2(230);
> i NUMBER := 1;
>
>BEGIN
> OPEN c1;
> i := 1;
> res_str := '';
> WHILE i <= 25
> LOOP
> FETCH c1 INTO r1;
>
> pkey_rec := TO_CHAR(r1.pkey);
> res_str := CONCAT(res_str, pkey_rec);
>
> IF i < 25 THEN
> res_str := CONCAT(res_str, ',');
> END IF;
>
> i := i + 1;
> END LOOP;
> CLOSE c1;
>
> RETURN res_str;
>
>EXCEPTION
> WHEN OTHERS THEN
> RETURN -1;
>
>END get_pkey_list;
>
>---------------
>Thanks a lot.
>
>J

The cursor above is static sql. In static sql you the object names can't be variable. If you want variable object names you need to (depending on version) use Native Dynamic SQL (open <cursor variable> for <statement string var> using <list of bind variables>, in 8i and higher) or dbms_sql (8.0 and lower)
Both variants are discussed in your pl/sql manual.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Mar 14 2003 - 13:07:42 CST