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