Re: Error with DBMS_SQL

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Wed, 20 Mar 2013 01:09:06 +0100
Message-ID: <favhk89tjeqh5741vm9h4aiokgmrjjb5rm_at_4ax.com>

On Tue, 19 Mar 2013 10:13:56 -0700 (PDT), carlinodba_at_gmail.com wrote:

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

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

Received on Wed Mar 20 2013 - 01:09:06 CET