Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: error using dbms.sql

    Hello:
    I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
    Procedure created.

    No errors.
    SQL> exec readtable('state_relation')
    BEGIN readtable('state_relation'); END;

    *
    ERROR at line 1:
    ORA-00905: missing keyword
    ORA-06512: at "CHADBA.READTABLE", line 78
    ORA-06512: at line

    The code is the following:

    set serveroutput on size 1000000

    CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS

    v_tablename varchar2(30);
    v_tablekey varchar2(30);
    fccid number;
    name varchar2(30);
    state varchar2(2);
    strsql varchar2(1000);
    numrows number;
    averageleng number;
    bytes number;
    countreg number;
    countbytes number;
    cursor1 INTEGER;
    cursor2 INTEGER;
    ignore INTEGER;
    BEGIN

    -- OPEN CURSOR
    cursor1 := dbms_sql.open_cursor;
    -- PREPARE PARSE
    DBMS_SQL.PARSE(cursor1,
    'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
    DBMS_SQL.native);
    -- use bind variables? No
    -- use query ? yes
    -- Define colmna
    DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
    DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
    -- execute
    ignore := DBMS_SQL.EXECUTE(cursor1);

    -- Prepare cursor for read and insert in other table
    cursor2 := DBMS_SQL.OPEN_CURSOR;
    strsql := ' SELECT DISTINCT '
    || ' l.bill_fccid '
    || ' l.name'
    || ' count(l.svcing_st) '
    || ' from '
    || 'v_tablekey l'
    || 'v_tablename m'
    || ' where '
    || 'l.bill_fccid'
    || ' = '
    || 'm.bill_fccid'
    || 'group by '
    || ' l.bill_fccid'
    || 'l.name';

    -- Parse cursor2
    DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);

    -- fetch_rows
    LOOP
    IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
    -- get column values of the row
    DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
    DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);

    ignore := DBMS_SQL.EXECUTE(cursor2);
    ELSE
    -- No more rows to read
    EXIT;
    END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(cursor1);
    DBMS_SQL.CLOSE_CURSOR(cursor2);
    EXCEPTION
    WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(cursor1) THEN
    DBMS_SQL.CLOSE_CURSOR(cursor1);
    END IF;
    IF DBMS_SQL.IS_OPEN(cursor2) THEN
    DBMS_SQL.CLOSE_CURSOR(cursor2);
    END IF;
    RAISE;
    END readtable;
    /
    show errors;
    Can some body help me? Thanks in advance

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: error using dbms.sql

    Remove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.

    Here is the correct stmt :
    DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);

    Originally posted by nelari
    Hello:
    I have a table with two of the fiedls that are tables_names. I need to use then to select some data and insert in another table. The error is the following:
    Procedure created.

    No errors.
    SQL> exec readtable('state_relation')
    BEGIN readtable('state_relation'); END;

    *
    ERROR at line 1:
    ORA-00905: missing keyword
    ORA-06512: at "CHADBA.READTABLE", line 78
    ORA-06512: at line

    The code is the following:

    set serveroutput on size 1000000

    CREATE OR REPLACE PROCEDURE readtable ( source IN VARCHAR2) IS

    v_tablename varchar2(30);
    v_tablekey varchar2(30);
    fccid number;
    name varchar2(30);
    state varchar2(2);
    strsql varchar2(1000);
    numrows number;
    averageleng number;
    bytes number;
    countreg number;
    countbytes number;
    cursor1 INTEGER;
    cursor2 INTEGER;
    ignore INTEGER;
    BEGIN

    -- OPEN CURSOR
    cursor1 := dbms_sql.open_cursor;
    -- PREPARE PARSE
    DBMS_SQL.PARSE(cursor1,
    'SELECT table_name, table_key into v_tablename,v_tablekey FROM ' || source,
    DBMS_SQL.native);
    -- use bind variables? No
    -- use query ? yes
    -- Define colmna
    DBMS_SQL.DEFINE_COLUMN(cursor1, 1, v_tablename,30);
    DBMS_SQL.DEFINE_COLUMN(cursor1, 2, v_tablekey,30);
    -- execute
    ignore := DBMS_SQL.EXECUTE(cursor1);

    -- Prepare cursor for read and insert in other table
    cursor2 := DBMS_SQL.OPEN_CURSOR;
    strsql := ' SELECT DISTINCT '
    || ' l.bill_fccid '
    || ' l.name'
    || ' count(l.svcing_st) '
    || ' from '
    || 'v_tablekey l'
    || 'v_tablename m'
    || ' where '
    || 'l.bill_fccid'
    || ' = '
    || 'm.bill_fccid'
    || 'group by '
    || ' l.bill_fccid'
    || 'l.name';

    -- Parse cursor2
    DBMS_SQL.PARSE(cursor2,strsql,DBMS_SQL.native);

    -- fetch_rows
    LOOP
    IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
    -- get column values of the row
    DBMS_SQL.COLUMN_VALUE(cursor1, 1, v_tablename);
    DBMS_SQL.COLUMN_VALUE(cursor1, 2, v_tablekey);

    ignore := DBMS_SQL.EXECUTE(cursor2);
    ELSE
    -- No more rows to read
    EXIT;
    END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(cursor1);
    DBMS_SQL.CLOSE_CURSOR(cursor2);
    EXCEPTION
    WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(cursor1) THEN
    DBMS_SQL.CLOSE_CURSOR(cursor1);
    END IF;
    IF DBMS_SQL.IS_OPEN(cursor2) THEN
    DBMS_SQL.CLOSE_CURSOR(cursor2);
    END IF;
    RAISE;
    END readtable;
    /
    show errors;
    Can some body help me? Thanks in advance

  3. #3
    Join Date
    May 2003
    Posts
    2

    Re: error using dbms.sql

    Originally posted by dbmadcap
    Remove "into v_tablename,v_tablekey" where you are parsing the sql. You are already defining the columns, hence dont need the into clause.

    Here is the correct stmt :
    DBMS_SQL.PARSE(cursor1, 'SELECT table_name, table_key FROM ' || source, DBMS_SQL.native);

    Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.

  4. #4
    Join Date
    May 2003
    Posts
    87

    Re: error using dbms.sql

    You have v_tablekey and v_tablename inside quotes. Change it as below :

    strsql := ' SELECT DISTINCT '
    || ' l.bill_fccid '
    || ' l.name'
    || ' count(l.svcing_st) '
    || ' from '|| v_tablekey || ' l '
    || ' , '|| v_tablename || ' m '

    || ' where '
    || 'l.bill_fccid'
    || ' = '
    || 'm.bill_fccid'
    || 'group by '
    || ' l.bill_fccid'
    || 'l.name';

    Also as a humble suggestion, try to form the string in fewer lines so that it will make easy for you to read & debug

    Originally posted by nelari
    Thank you for the answer but this is not the problem, I already change us you indicate and continues the same error. The error is with the two variables that i'm using for the second select.

Posting Permissions

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