Home » Other » Training & Certification » Error ORA-00947(not enough values) - FORALL
icon4.gif  Error ORA-00947(not enough values) - FORALL [message #307297] Tue, 18 March 2008 06:19 Go to next message
mafc73
Messages: 38
Registered: November 2006
Member
Hi,

I have this code and I get the error ora-947 in values line. I have searched in metalink and the only advice is to put

VALUES var_sent1(i);

instead of

VALUES (var_sent1(i));

I have changed but it doesn't works.

Thanks


CREATE OR REPLACE procedure proc_example( a_date IN Varchar2 )
IS

cursor cursor_sent1 IS select c.id_num, r.url_id, r.url_id2, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from tab1 a, tab2 d, tab3 c, tab4 r
where a.event_date_id = d.date_id
group by c.id_num, url_id, url_id2, trunc(date_dt,'IW')+6;

TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;

var_sent1 tipo_sent1;

BEGIN

OPEN cursor_sent1;
FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
CLOSE cursor_sent1;

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_dest (imm_id_num, url_id, url_id2, date_dt, new, cnt, flows)
VALUES var_sent1(i);

END;
/
Re: Error ORA-00947(not enough values) - FORALL [message #307299 is a reply to message #307297] Tue, 18 March 2008 06:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i20483

Check the above link and search for forall keyword.

Regards

Raj
Re: Error ORA-00947(not enough values) - FORALL [message #307302 is a reply to message #307299] Tue, 18 March 2008 07:08 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
I have been seeing this link and I have seen this example

Inserting PL/SQL Records into the Database

A PL/SQL-only extension of the INSERT statement lets you insert records into database rows, using a single variable of type RECORD or %ROWTYPE in the VALUES clause instead of a list of fields. That makes your code more readable and maintainable.

If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible datatypes. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_name%ROWTYPE.

Example 5-50 declares a record variable using a %ROWTYPE qualifier. You can insert this variable without specifying a column list. The %ROWTYPE declaration ensures that the record attributes have exactly the same names and types as the table columns.

Example 5-50 Inserting a PL/SQL Record Using %ROWTYPE

DECLARE
dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
-- Using the %ROWTYPE means we can leave out the column list
-- (department_id, department_name, and location_id) from the INSERT statement
INSERT INTO departments VALUES dept_info;
END;
/



I have probed with

INSERT into t_dest VALUES var_sent1;

but I get an error ORA-00904: "VAR_SENT1": not valid identifier


That this badly?

Thanks

[mod-edit] removed font size so humans could read it.

[Updated on: Tue, 18 March 2008 07:41] by Moderator

Report message to a moderator

Re: Error ORA-00947(not enough values) - FORALL [message #307303 is a reply to message #307297] Tue, 18 March 2008 07:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
No need for PL/SQL here, it can be done with:

 INSERT INTO tabname SELECT select_list FROM tab(s) WHERE condition(s)

In addition, your sample code appears to generate a Cartesian product i.e. it would appear that you are missing joins
Re: Error ORA-00947(not enough values) - FORALL [message #307308 is a reply to message #307303] Tue, 18 March 2008 07:34 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
I want to do it with PL and bulk load because I want to learn how to use. This is an example for learn how it works BULK COLLECT and FORALL with a cursor. It isn't a real procedure.

Thanks
Re: Error ORA-00947(not enough values) - FORALL [message #307332 is a reply to message #307308] Tue, 18 March 2008 08:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You are combining two concepts here. i.e. records AND collections
your 'Example 5-50 Inserting a PL/SQL Record Using %ROWTYPE'
Is inserting a single record whereas, you are trying to insert a record from a collection.
 INSERT into t_dest VALUES var_sent1

Would be fine if var_sent1 was not a collection and therefore the advice
Quote:
VALUES var_sent1(i);
instead of
VALUES (var_sent1(i));

Should work. You say that it doesn't, trouble is, that is all that you say It doesn't work is basically useless information, you need to tell us what actually happened.
It worked fine when I did it (changing table names and column names as required. So, you're going to have to be more descriptive than "It doesn't work" My suggestion would be that you post the session in which your call "doesn't work"
Re: Error ORA-00947(not enough values) - FORALL [message #307362 is a reply to message #307332] Tue, 18 March 2008 10:41 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
If I use.

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_dest
VALUES var_sent1(i);

I get the error

ORA-00947(not enough values)

The rest of code is equal to the first message.


I want to use the adventages of BULK LOAD and FORALL for massive loads. My problem is that I have an insert ... select sentence and I want to see if I can improve the efficiency with BULK LOAD /FORALL. This insert ...select sentence load millions of rows. The select is tuned reason why I try with bulk loads.

Thanks
Re: Error ORA-00947(not enough values) - FORALL [message #307367 is a reply to message #307362] Tue, 18 March 2008 11:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1 more chance then I leave this post to someone else.
Please post your session i.e. go into sqlplus, run your code (that generates the error) copy all of the text in the session and post it here between code tags. I cannot/ will not help you until you do this.
Cheers
Jim
Re: Error ORA-00947(not enough values) - FORALL [message #307371 is a reply to message #307297] Tue, 18 March 2008 11:40 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
The error is when I compile the procedure. I compile with toad so I can't put the sqlplus code. The error is Error ORA-00947(not enough values) in values line.
I don't know what more can you need. Sad

This is the original procedure with the insert .. select sentence and bulk load behind.

Thanks


CREATE OR REPLACE procedure carga( a_date IN Varchar2 )
IS
v_sdate_id number;
v_edate_id number;
v_sdate_dt date;
v_edate_dt date;
v_advid number := 1233;
v_year number;
v_cnt number;

cursor cursor_sent1 IS select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new_money), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;

TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;

var_sent1 tipo_sent1;

BEGIN
v_edate_dt := to_date(a_date,'MM/DD/YYYY');
select year_cal_qy into v_year from t_date where date_dt = v_edate_dt;
select min(date_dt) into v_sdate_dt from t_date where year_cal_qy = v_year;
select date_id into v_sdate_id from t_date where date_dt = v_sdate_dt;
select date_id into v_edate_id from t_date where date_dt = v_edate_dt;


-- This is the original insert .. select
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;
commit;


-- bulk load
OPEN cursor_sent1;
FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
CLOSE cursor_sent1;

--- I have probed of the two forms and I get the same error when compile (Error ORA-00947(not enough values)

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
VALUES var_sent1(i);

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1
VALUES var_sent1(i);


END;
/




Re: Error ORA-00947(not enough values) - FORALL [message #307382 is a reply to message #307371] Tue, 18 March 2008 12:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Don't compile it in Toad. Compile it in SQL*Plus, so that you can copy and paste the results including line numbers, and the error message showing what position on what line caused the error and use code tags. You also need to provide create table and insert statements for any objects referred to in the code. Please read the forum guide for what we expect.
Re: Error ORA-00947(not enough values) - FORALL [message #307610 is a reply to message #307297] Wed, 19 March 2008 05:03 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
I have compiled in sql*plus the procedure two times. The first time I have used this sentence :


FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
VALUES var_sent1(i);


and the error is this


LINE/COL ERROR
-------- -----------------------------------------------------------------
71/5 PL/SQL: SQL Statement ignored
72/5 PL/SQL: ORA-00947: no hay suficientes valores


I have marked the line 72/5 in red color.


The second time I have changed the previous sentence for this


FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1
VALUES var_sent1(i);


and I get this error. In red is the column 17.


LINE/COL ERROR
-------- -----------------------------------------------------------------
74/5 PL/SQL: SQL Statement ignored
74/17 PL/SQL: ORA-00947: no hay suficientes valores


The insert .. select structure and the bulk collect/for all are the same.

Thanks


CREATE OR REPLACE procedure carga( a_date IN Varchar2 )
IS
v_sdate_id number;
v_edate_id number;
v_sdate_dt date;
v_edate_dt date;
v_advid number := 1233;
v_year number;
v_cnt number;

cursor cursor_sent1 IS select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;

TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;

var_sent1 tipo_sent1;

BEGIN
v_edate_dt := to_date(a_date,'MM/DD/YYYY');
select year_cal_qy into v_year from t_date where date_dt = v_edate_dt;
select min(date_dt) into v_sdate_dt from t_date where year_cal_qy = v_year;
select date_id into v_sdate_id from t_date where date_dt = v_sdate_dt;
select date_id into v_edate_id from t_date where date_dt = v_edate_dt;


-- This is the original insert .. select
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;
commit;


-- bulk load
OPEN cursor_sent1;
FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
CLOSE cursor_sent1;

--- I have probed of the two forms and I get the same error when compile (Error ORA-00947(not enough values)

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
VALUES var_sent1(i);

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1
VALUES var_sent1(i);


END;
/ 



Re: Error ORA-00947(not enough values) - FORALL [message #307620 is a reply to message #307610] Wed, 19 March 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so that you can copy and paste the results including line numbers, and the error message showing what position on what line caused the error and use code tags.

Which words don't you understand in this sentence?
Does your post fit what has been requested?

Regards
Michel
Re: Error ORA-00947(not enough values) - FORALL [message #307755 is a reply to message #307610] Wed, 19 March 2008 16:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to stop posting partial code and post a copy and paste of a complete run including all line numbers, executed from SQL*Plus, like the one I have demonstrated below and include create table and insert statements, otherwise all we can say is that similar things work for us, since you have not provided proof that you are doing what you post or provided a reproducible test case. You also need to provide your Oracle version. Just put your code in a .sql file and start that .sql file.

SCOTT@orcl_11g> CREATE TABLE t_data1 AS SELECT * FROM DEPT WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1
 12  	 VALUES var_sent1(i);
 13  END;
 14  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC carga

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> 



Re: Error ORA-00947(not enough values) - FORALL [message #307756 is a reply to message #307610] Wed, 19 March 2008 16:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Try this. Copy and paste the following into a .sql file and start it from SQL*Plus (not Toad!) and see what you get. If it runs without error, then we will know that the problem is not due to a difference in Oracle versions. Do not run it from Toad as that may be the problem or part of it. We need to narrow it down to what part is not working, so we need to see if a simple test like the following works for you or produces an error. Please post the results here.

CREATE TABLE t_data1 AS SELECT * FROM user_objects WHERE 1 = 2
/
CREATE OR REPLACE procedure carga 
IS
  cursor cursor_sent1 IS select * from user_objects WHERE ROWNUM <= 5;
  TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  var_sent1 tipo_sent1;
BEGIN
  OPEN cursor_sent1;
  FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  CLOSE cursor_sent1;
  FORALL i IN var_sent1.FIRST .. var_sent1.LAST
    INSERT into t_data1 
    VALUES var_sent1(i);
END;
/ 
SHOW ERRORS
EXEC carga
SELECT * FROM t_data1
/

Re: Error ORA-00947(not enough values) - FORALL [message #307757 is a reply to message #307610] Wed, 19 March 2008 16:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Apparently, if you specify column names in the insert, you have to specify them in the values clause. Please note the differences below.

-- reproduction:
SCOTT@orcl_11g> CREATE TABLE t_data1 AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1 (deptno, dname, loc)
 12  	 VALUES var_sent1(i);
 13  END;
 14  /

Warning: Procedure created with compilation errors.

SCOTT@orcl_11g> SHOW ERRORS
Errors for PROCEDURE CARGA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5     PL/SQL: SQL Statement ignored
12/5     PL/SQL: ORA-00947: not enough values
SCOTT@orcl_11g> EXEC carga
BEGIN carga; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.CARGA is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

no rows selected


-- correction:
SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1 (deptno, dname, loc)
 12  	 VALUES (var_sent1(i).deptno, var_sent1(i).dname, var_sent1(i).loc);
 13  END;
 14  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC carga

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> 

Re: Error ORA-00947(not enough values) - FORALL [message #308782 is a reply to message #307297] Tue, 25 March 2008 07:39 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
Hi,

I have probed with your solution but I get an pls error. I have changed the insert statement for this


FORALL i IN var_sent1.FIRST .. var_sent1.LAST
        INSERT INTO (select id_num, placement, creative, date_dt, new, trades, flows from t_data1) 
        VALUES var_sent1(i);



and It works ok.


Thank you.
Re: Error ORA-00947(not enough values) - FORALL [message #308847 is a reply to message #308782] Tue, 25 March 2008 12:39 Go to previous message
mafc73
Messages: 38
Registered: November 2006
Member
Hi,

Why can that be that the insert/select be faster than bulk/forall?.

I'm inserting 1.736.704 rows and the results are :

GET_TIME elapsed BULK/FORALL 100 = 13,53 secs.
GET_CPU_TIME elapsed BULK/FORALL 100 = 4,93 secs.

GET_TIME elapsed INSERT/SELECT = 12,65 secs.
GET_CPU_TIME elapsed INSERT/SELECT = 1,01 secs.

I have execute the procedure several times and the result is always similar.

Thanks

schema : hr

Tables
employees : 6848 rows
departments : 6912 rows

initial t_temporal: 0 rows
final t_temporal: 1.736.704 rows

I have execute 2 times for the probe. In the first I commented the lines for insert/select. The second I commented the lines forall.


Thanks

create or replace procedure p_empleados 
IS
  b1         PLS_INTEGER;
  b2         PLS_INTEGER;
  e1         PLS_INTEGER;
  e2         PLS_INTEGER; 
                           
  cursor cursor_sent1 IS select employee_id, first_name, department_name from employees e, departments d where e.department_id=d.department_id;                            
                    
  TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  
  var_sent1 tipo_sent1;                       
  
BEGIN

    b1 := DBMS_UTILITY.GET_TIME();
    b2 := DBMS_UTILITY.GET_CPU_TIME();

    OPEN  cursor_sent1;
    
    LOOP
    
      FETCH cursor_sent1 BULK COLLECT INTO var_sent1 LIMIT 100;
  
      FORALL i IN var_sent1.FIRST .. var_sent1.LAST
          SAVE EXCEPTIONS
          INSERT INTO (select employee_id, first_name, department_name from t_temporal where 0=1) 
          VALUES var_sent1(i);
          
          commit;
      
      EXIT WHEN cursor_sent1%NOTFOUND;
    
    END LOOP;
    
    CLOSE cursor_sent1;

    e1 := DBMS_UTILITY.GET_TIME() - b1;
    e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;

    DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed BULK/FORALL 100 = ' || ROUND(e1/100, 3) || ' secs.' );
    DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed BULK/FORALL 100 = ' || ROUND(e2/100, 3) || ' secs.' );


    b1 := DBMS_UTILITY.GET_TIME();
    b2 := DBMS_UTILITY.GET_CPU_TIME();

    INSERT into t_temporal(employee_id, first_name, department_name)
    select employee_id, first_name, department_name from employees e, departments d where e.department_id=d.department_id;
    commit;

    e1 := DBMS_UTILITY.GET_TIME() - b1;
    e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;


    DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed INSERT/SELECT = ' || ROUND(e1/100, 3) || ' secs.' );
    DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed INSERT/SELECT = ' || ROUND(e2/100, 3) || ' secs.' );

EXCEPTION
  WHEN OTHERS THEN 
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Found an error at ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Error Msg: ' ||       
            SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) );
    END LOOP;                 
END; 

[Updated on: Tue, 25 March 2008 12:39]

Report message to a moderator

Previous Topic: hot backup
Next Topic: Trigger
Goto Forum:
  


Current Time: Thu Dec 08 04:18:33 CST 2016

Total time taken to generate the page: 0.14238 seconds