Error with DBMS_SQL

[carli...@gmail.com]

Hi, I have a code with this lines:

[code]
create or replace procedure update_potencia

IS CURSOR from_table

IS SELECT table_name
FROM user_tables
WHERE table_name LIKE '%AVE';

v_from_table from_table%ROWTYPE;

source_cursor INTEGER;

ignore INTEGER;

BEGIN
OPEN from_table;
LOOP
FETCH from_table INTO v_from_table;
EXIT WHEN from_table%NOTFOUND;
source_cursor := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE(source_cursor,
'INSERT INTO noa_ave_max_pot (Pointnumber,
hora_max_rtc, valor_max_rtc,
hora_noa, valor_noa,
hora_max_noa, valor_max_noa )
(SELECT a.pointnumber pointnumber,
a.utctimemax hora_max_rtc, valor_max_rtc a.value,
b.utctime hora_ave, b.VALUE valor_ave,
c.utctime hora_max_ave, c.VALUE value_max_ave
FROM rtc_estaciones a, ' || v_from_table.table_name ||' b,
' || v_from_table.table_name ||' c
WHERE A.value IN (SELECT MAX (VALUE)
FROM rtc_estaciones )
AND C.VALUE IN (SELECT MAX (VALUE)
FROM ' || v_from_table.table_name ||'
WHERE utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2)
AND A.utctimemax BETWEEN SYSDATE - 3 AND SYSDATE - 2
AND B.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2
AND C.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2
AND a.utctimemax = b.utctime)',DBMS_SQL.NATIVE );

ignore := DBMS_SQL.EXECUTE (source_cursor);


DBMS_SQL.CLOSE_CURSOR (source_cursor);

END LOOP;

CLOSE from_table;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Last Error: ' || DBMS_SQL.LAST_ERROR_POSITION ());
DBMS_SQL.close_cursor (source_cursor);
RAISE;

END;
[code]

but when run I see this error:

SQL>/
DECLARE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 20

I have the necessary permissions for execution on them but I can not see the contents of these packages, because they are wrapped.

"SYS.DBMS_SYS_SQL"
"SYS.DBMS_SQL"

They could tell me any indication that change to avoid the error?

Thank you very much.!

Objective: from a range of tables, for maximum and time / date, with maximum value and time / date of a 2nd table, along with another couple of data value, date / time of it, and then insert the result in a 3rd table.

[code]
Oracle version 10.2.0.3.0

SQL> desc noa_ave --> || v_from_table.table_name || --> table range

SQL> describe noa_ave
Name Null? Type
-------- UTCTIME NOT NULL DATE
POINTNUMBER NOT NULL NUMBER(38)
VALUE FLOAT(126)
TLQ NUMBER(38)

SQL> describe rtc_estaciones
Name Null? Type
-------- UTCTIME NOT NULL DATE
POINTNUMBER NOT NULL NUMBER(38)
VALUE FLOAT(126)
TLQ NUMBER(38)
UTCTIMEMAX DATE

SQL> desc noa_ave_max_pot
Name Null? Type
-------- POINTNUMBER NOT NULL NUMBER(38)
HORA_MAX_RTC NOT NULL DATE
VALOR_MAX_RTC FLOAT(126)
HORA_NOA NOT NULL DATE
VALOR_NOA FLOAT(126)
HORA_MAX_NOA NOT NULL DATE
VALOR_MAX_NOA FLOAT(126)

SQL>
[code]

[Sybrand Bakker]

Problem is not in DBMS_SQL but in the statement you generate, it
contains errors.
I would recommend you build up a VARCHAR2 variable, say sqlstr, with
the statement, and use that as parameter to dbms_sql.
You can easily spool this string to a file, and execute the statement
standalone in sqlplus and you will see what is going on.
You don't need access to dbms_sql, the problem is bot there.

--
Sybrand Bakker
Senior Oracle DBA

[Mladen Gogala]

On Tue, 19 Mar 2013 10:13:56 -0700, carlinodba wrote:

> ERROR at line 1:
> ORA-00923: FROM keyword not found where expected ORA-06512: at
> "SYS.DBMS_SYS_SQL", line 909 ORA-06512: at "SYS.DBMS_SQL", line 39
> ORA-06512: at line 20

DBMS_SQL works. It's the SQL passed as an argument that doesn't work.
Execute SQL separately in SQL*Developer. Don't use sqlplus, it's sooo 20th
century.



--
http://mgogala.byethost5.com

[carli...@gmail.com]

Thanks, it works now!

I added the lines;

[code]
source_cursor varchar2 (32767);
.
.
dbms_output.put_line('INSERT STATEMENT: '|| source_cursor);
[code]

and I see that the insert is made.

I'll try with a range of tables, to see that the collection of data is inserted correctly, with data from multiple tables.

Now I would like to add a few lines to check if the record exists then update then insert otherwise, according to the case.

You'll have an example that you do this?

Thank you very much!

[joel garry]

On Mar 20, 7:20 am, carlino...@gmail.com wrote:

> Now I would like to add a few lines to check if the record exists then update then insert otherwise, according to the case.

Sounds like a job for merge. http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

jg
--
@home.com is bogus.
http://www.theregister.co.uk/2013/03/19/gartner_virtualisation_market_overview/