Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00947: not enough values (PL/SQL Release 11.2.0.3.0)
ORA-00947: not enough values [message #563879] Fri, 17 August 2012 12:00 Go to next message
hbhanujan
Messages: 28
Registered: August 2012
Location: Dallas, TX
Junior Member
 
/* Formatted on 08/17/2012 11:56:42 AM (QP5 v5.163.1008.3004) */
DECLARE
   src_csr1      INTEGER;
   src_x         INTEGER;
   dst_csr1      INTEGER;
   v_DB_link     VARCHAR2 (20);
   v_Db_name     VARCHAR2 (20);
   v_sql1        VARCHAR2 (2000);
   v_sql_src     VARCHAR2 (2000);
   v_date        DATE;
   ig1           INTEGER;
   v_Sum_bytes   NUMBER;
   v_Owner       VARCHAR2 (100);
BEGIN
   SELECT db_link INTO v_DB_link FROM dba_db_links;

   v_sql1 := 'select name  from v$database @' || v_DB_link;

   EXECUTE IMMEDIATE v_sql1 INTO v_Db_name;

   DBMS_OUTPUT.put_line (' DB link : ' || v_DB_link);
   DBMS_OUTPUT.put_line (' DB Name  : ' || v_Db_name);

   src_csr1 := DBMS_SQL.open_cursor;

   v_sql_src :=
      ' select sysdate,(select name  from v$database@' || v_DB_link
      || ') db_name ,owner, sum(bytes)/(1048576*1024) sum_bytes 
from db_XXXX  group by owner  having sum(bytes)/(1048576*1024) > 1.0  
order by owner  ';


   DBMS_OUTPUT.put_Line (v_sql_src);
   DBMS_SQL.parse (src_csr1, v_sql_src, DBMS_SQL.native);
   DBMS_SQL.DEFINE_COLUMN (src_csr1, 1, v_date);
   DBMS_SQL.DEFINE_COLUMN (src_csr1,
                           2,
                           v_Db_name,
                           20);
   DBMS_SQL.DEFINE_COLUMN (src_csr1,
                           3,
                           v_Owner,
                           32);
   DBMS_SQL.DEFINE_COLUMN (src_csr1, 4, v_Sum_bytes);

   ig1 := DBMS_SQL.EXECUTE (src_csr1);

   dst_csr1 := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE (
      dst_csr1,
      'insert into ffas_schema_tbl VALUES (:v_date_bind, :v_Owner_bind, :v_Sum_bytes_bind)',
      DBMS_SQL.NATIVE);

   LOOP
      IF DBMS_SQL.FETCH_ROWS (src_csr1) > 0
      THEN
         --  get values for the row
         DBMS_SQL.COLUMN_VALUE (src_csr1, 1, v_date);
         DBMS_SQL.COLUMN_VALUE (src_csr1, 2, v_Db_name);
         DBMS_SQL.COLUMN_VALUE (src_csr1, 3, v_Owner);
         DBMS_SQL.COLUMN_VALUE (src_csr1, 4, v_Sum_bytes);
         -- bind row values to destination cursor
         DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_date_bind', v_date);
         DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Db_name_bind', v_Db_name);
         DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Owner_bind', v_Owner);
         DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Sum_bytes_bind', v_Sum_bytes);
         ig1 := DBMS_SQL.EXECUTE (dst_csr1);
      END IF;
   END LOOP;

   DBMS_SQL.close_cursor (src_csr1);
   DBMS_SQL.close_cursor (dst_csr1);
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
      DBMS_OUTPUT.put_line (SQLERRM || SQLCODE);
END;
/

DB link : REMOTE_LINK
DB Name : FSA01T
select sysdate,(select name from v$database@REMOTE_LINK) db_name ,owner,
sum(bytes)/(1048576*1024) sum_bytes from fsa01t_dba_segments group by owner
having sum(bytes)/(1048576*1024) > 1.0 order by owner

Getting the following error - I have checked and made sure that the query returns 4 columns.
DECLARE
*
ERROR at line 1:
ORA-00947: not enough values
ORA-06512: at line 60

*BlackSwan added {code} tags. Do so yourself in the future.

[Updated on: Fri, 17 August 2012 12:22] by Moderator

Report message to a moderator

Re: ORA-00947: not enough values [message #563881 is a reply to message #563879] Fri, 17 August 2012 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session like:
SQL> DECLARE
  2     src_csr1      INTEGER;
  3     src_x         INTEGER;
  4     dst_csr1      INTEGER;
  5     v_DB_link     VARCHAR2 (20);
  6     v_Db_name     VARCHAR2 (20);
  7     v_sql1        VARCHAR2 (2000);
  8     v_sql_src     VARCHAR2 (2000);
  9     v_date        DATE;
 10     ig1           INTEGER;
 11     v_Sum_bytes   NUMBER;
 12     v_Owner       VARCHAR2 (100);
 13  BEGIN
 14     SELECT db_link INTO v_DB_link FROM dba_db_links;
 15  
 16     v_sql1 := 'select name  from v$database @' || v_DB_link;
 17  
 18     EXECUTE IMMEDIATE v_sql1 INTO v_Db_name;
 19  
 20     DBMS_OUTPUT.put_line (' DB link : ' || v_DB_link);
 21     DBMS_OUTPUT.put_line (' DB Name  : ' || v_Db_name);
 22  
 23     src_csr1 := DBMS_SQL.open_cursor;
 24  
 25     v_sql_src :=
 26        ' select sysdate,(select name  from v$database@' || v_DB_link
 27        || ') db_name ,owner, sum(bytes)/(1048576*1024) sum_bytes 
 28  from db_XXXX  group by owner  having sum(bytes)/(1048576*1024) > 1.0  
 29  order by owner  ';
 30  
 31  
 32     DBMS_OUTPUT.put_Line (v_sql_src);
 33     DBMS_SQL.parse (src_csr1, v_sql_src, DBMS_SQL.native);
 34     DBMS_SQL.DEFINE_COLUMN (src_csr1, 1, v_date);
 35     DBMS_SQL.DEFINE_COLUMN (src_csr1,
 36                             2,
 37                             v_Db_name,
 38                             20);
 39     DBMS_SQL.DEFINE_COLUMN (src_csr1,
 40                             3,
 41                             v_Owner,
 42                             32);
 43     DBMS_SQL.DEFINE_COLUMN (src_csr1, 4, v_Sum_bytes);
 44  
 45     ig1 := DBMS_SQL.EXECUTE (src_csr1);
 46  
 47     dst_csr1 := DBMS_SQL.OPEN_CURSOR;
 48  
 49     DBMS_SQL.PARSE (
 50        dst_csr1,
 51        'insert into ffas_schema_tbl VALUES (:v_date_bind, :v_Owner_bind, :v_Sum_bytes_bind)',
 52        DBMS_SQL.NATIVE);
 53  
 54     LOOP
 55        IF DBMS_SQL.FETCH_ROWS (src_csr1) > 0
 56        THEN
 57           --  get values for the row
 58           DBMS_SQL.COLUMN_VALUE (src_csr1, 1, v_date);
 59           DBMS_SQL.COLUMN_VALUE (src_csr1, 2, v_Db_name);
 60           DBMS_SQL.COLUMN_VALUE (src_csr1, 3, v_Owner);
 61           DBMS_SQL.COLUMN_VALUE (src_csr1, 4, v_Sum_bytes);
 62           -- bind row values to destination cursor
 63           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_date_bind', v_date);
 64           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Db_name_bind', v_Db_name);
 65           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Owner_bind', v_Owner);
 66           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Sum_bytes_bind', v_Sum_bytes);
 67           ig1 := DBMS_SQL.EXECUTE (dst_csr1);
 68        END IF;
 69     END LOOP;
 70  
 71     DBMS_SQL.close_cursor (src_csr1);
 72     DBMS_SQL.close_cursor (dst_csr1);
 73  EXCEPTION
 74     WHEN OTHERS
 75     THEN
 76        RAISE;
 77        DBMS_OUTPUT.put_line (SQLERRM || SQLCODE);
 78  END;
 79  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 76

As you see I have not the same error than you.
In addition, your WHEN OTHERS clause hides where the error comes from.
So first, remove the WHEN OTHERS clause (read the link):
SQL> DECLARE
  2     src_csr1      INTEGER;
  3     src_x         INTEGER;
  4     dst_csr1      INTEGER;
  5     v_DB_link     VARCHAR2 (20);
  6     v_Db_name     VARCHAR2 (20);
  7     v_sql1        VARCHAR2 (2000);
  8     v_sql_src     VARCHAR2 (2000);
  9     v_date        DATE;
 10     ig1           INTEGER;
 11     v_Sum_bytes   NUMBER;
 12     v_Owner       VARCHAR2 (100);
 13  BEGIN
 14     SELECT db_link INTO v_DB_link FROM dba_db_links;
 15  
 16     v_sql1 := 'select name  from v$database @' || v_DB_link;
 17  
 18     EXECUTE IMMEDIATE v_sql1 INTO v_Db_name;
 19  
 20     DBMS_OUTPUT.put_line (' DB link : ' || v_DB_link);
 21     DBMS_OUTPUT.put_line (' DB Name  : ' || v_Db_name);
 22  
 23     src_csr1 := DBMS_SQL.open_cursor;
 24  
 25     v_sql_src :=
 26        ' select sysdate,(select name  from v$database@' || v_DB_link
 27        || ') db_name ,owner, sum(bytes)/(1048576*1024) sum_bytes 
 28  from db_XXXX  group by owner  having sum(bytes)/(1048576*1024) > 1.0  
 29  order by owner  ';
 30  
 31  
 32     DBMS_OUTPUT.put_Line (v_sql_src);
 33     DBMS_SQL.parse (src_csr1, v_sql_src, DBMS_SQL.native);
 34     DBMS_SQL.DEFINE_COLUMN (src_csr1, 1, v_date);
 35     DBMS_SQL.DEFINE_COLUMN (src_csr1,
 36                             2,
 37                             v_Db_name,
 38                             20);
 39     DBMS_SQL.DEFINE_COLUMN (src_csr1,
 40                             3,
 41                             v_Owner,
 42                             32);
 43     DBMS_SQL.DEFINE_COLUMN (src_csr1, 4, v_Sum_bytes);
 44  
 45     ig1 := DBMS_SQL.EXECUTE (src_csr1);
 46  
 47     dst_csr1 := DBMS_SQL.OPEN_CURSOR;
 48  
 49     DBMS_SQL.PARSE (
 50        dst_csr1,
 51        'insert into ffas_schema_tbl VALUES (:v_date_bind, :v_Owner_bind, :v_Sum_bytes_bind)',
 52        DBMS_SQL.NATIVE);
 53  
 54     LOOP
 55        IF DBMS_SQL.FETCH_ROWS (src_csr1) > 0
 56        THEN
 57           --  get values for the row
 58           DBMS_SQL.COLUMN_VALUE (src_csr1, 1, v_date);
 59           DBMS_SQL.COLUMN_VALUE (src_csr1, 2, v_Db_name);
 60           DBMS_SQL.COLUMN_VALUE (src_csr1, 3, v_Owner);
 61           DBMS_SQL.COLUMN_VALUE (src_csr1, 4, v_Sum_bytes);
 62           -- bind row values to destination cursor
 63           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_date_bind', v_date);
 64           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Db_name_bind', v_Db_name);
 65           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Owner_bind', v_Owner);
 66           DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Sum_bytes_bind', v_Sum_bytes);
 67           ig1 := DBMS_SQL.EXECUTE (dst_csr1);
 68        END IF;
 69     END LOOP;
 70  
 71     DBMS_SQL.close_cursor (src_csr1);
 72     DBMS_SQL.close_cursor (dst_csr1);
 73  END;
 74  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 14

Indeed, in my case, there are several database links in my database so the error.

Post your session so we can see what you have.

Regards
Michel

Report message to a moderator

Re: ORA-00947: not enough values [message #563883 is a reply to message #563881] Fri, 17 August 2012 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 20086
Registered: January 2009
Senior Member
One "solution" that I used in the past is to keep the SQL constant as below

SELECT COL1 FROM TABLE1@REMOTE;

I would create private DB LINK for "REMOTE" to point to which ever remote DB I desired.
You really don't require EXECUTE IMMEDIATE or DBMS_SQL to accomplish what needs to be done.

Report message to a moderator

Re: ORA-00947: not enough values [message #563891 is a reply to message #563883] Fri, 17 August 2012 12:51 Go to previous messageGo to next message
hbhanujan
Messages: 28
Registered: August 2012
Location: Dallas, TX
Junior Member
Michel/Blackswan

Thanks for your input. I will retry with private dblinks to see if I can get this done.

Report message to a moderator

Re: ORA-00947: not enough values [message #563896 is a reply to message #563891] Fri, 17 August 2012 13:04 Go to previous messageGo to next message
hbhanujan
Messages: 28
Registered: August 2012
Location: Dallas, TX
Junior Member
Is there an easier way to accomplish what I am trying to do?

Here is what the issue is - we are supposed to go to a different database and see how much space is taken up by all the users in that database and insert into a table.

There are 11 users in the remote table.

What options do I have ?

Thanks

Report message to a moderator

Re: ORA-00947: not enough values [message #563898 is a reply to message #563896] Fri, 17 August 2012 13:19 Go to previous messageGo to next message
hbhanujan
Messages: 28
Registered: August 2012
Location: Dallas, TX
Junior Member
 
SQL> DECLARE
  2     src_csr1      INTEGER;
  3     src_x         INTEGER;
  4     dst_csr1      INTEGER;
  5     v_DB_link     VARCHAR2 (20);
  6     v_Db_name     VARCHAR2 (20);
  7     v_sql1        VARCHAR2 (2000);
  8     v_sql_src     VARCHAR2 (2000);
  9     v_date        DATE;
 10     ig1           INTEGER;
 11     v_Sum_bytes   NUMBER;
 12     v_Owner       VARCHAR2 (100);
 13  BEGIN
 14     SELECT db_link INTO v_DB_link FROM dba_db_links;
 15
 16     v_sql1 := 'select name  from v$database @' || v_DB_link;
 17
 18     EXECUTE IMMEDIATE v_sql1 INTO v_Db_name;
 19         src_csr1 := DBMS_SQL.open_cursor;
 20         v_sql_src :=
 21              ' select sysdate,(select name  from v$database@'||v_DB_link
 22               || ') db_name ,owner, round(sum(bytes)/(1048576*1024),2) sum_b
ytes from fsa01t_dba_segments  group by owner  having sum(bytes)/(1048576*1024)
> 1.0  order by owner  ';
 23                   DBMS_OUTPUT.put_Line (v_sql_src);
 24                   DBMS_SQL.parse (src_csr1, v_sql_src, DBMS_SQL.native);
 25                   DBMS_SQL.DEFINE_COLUMN (src_csr1, 1, v_date);
 26                   DBMS_SQL.DEFINE_COLUMN (src_csr1, 2, v_Db_name, 20);
 27                   DBMS_SQL.DEFINE_COLUMN (src_csr1, 3,v_Owner,32);
 28                   DBMS_SQL.DEFINE_COLUMN (src_csr1, 4, v_Sum_bytes);
 29
 30                       ig1 := DBMS_SQL.EXECUTE (src_csr1);
 31                       dst_csr1 := DBMS_SQL.OPEN_CURSOR;
 32
 33              DBMS_SQL.PARSE ( dst_csr1, 'insert into ffas_schema_tbl VALUES
(:v_date_bind, :v_Db_name_bind, :v_Owner_bind, :v_Sum_bytes_bind)', DBMS_SQL.NAT
IVE);
 34
 35     LOOP
 36        IF DBMS_SQL.FETCH_ROWS (src_csr1) > 0
 37        THEN
 38           --  get values for the row
 39               DBMS_SQL.COLUMN_VALUE (src_csr1, 1, v_date);
 40               DBMS_SQL.COLUMN_VALUE (src_csr1, 2, v_Db_name);
 41               DBMS_SQL.COLUMN_VALUE (src_csr1, 3, v_Owner);
 42               DBMS_SQL.COLUMN_VALUE (src_csr1, 4, v_Sum_bytes);
 43           -- bind row values to destination cursor
 44              DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_date_bind', v_date);
 45              DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Db_name_bind', v_Db_name)
;
 46              DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Owner_bind', v_Owner);
 47              DBMS_SQL.BIND_VARIABLE (dst_csr1, ':v_Sum_bytes_bind', v_Sum_by
tes);
 48           ig1 := DBMS_SQL.EXECUTE (dst_csr1);
 49        END IF;
 50     END LOOP;
 51
 52     DBMS_SQL.close_cursor (src_csr1);
 53     DBMS_SQL.close_cursor (dst_csr1);
 54  END;
 55  /


select sysdate,(select name from v$database@REMOTE_LINK) db_name ,owner,
round(sum(bytes)/(1048576*1024),2) sum_bytes from fsa01t_dba_segments group by
owner having sum(bytes)/(1048576*1024) > 1.0 order by owner
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at line 36


SQL>

Report message to a moderator

Re: ORA-00947: not enough values [message #563899 is a reply to message #563898] Fri, 17 August 2012 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have no condition to get out of the loop, so you get out of it when you have an exception.
Maybe you should do something like:
IF DBMS_SQL.FETCH_ROWS (src_csr1) > 0 THEN
...
ELSE EXIT;
END IF;


Regards
Michel

Report message to a moderator

Re: ORA-00947: not enough values [message #563902 is a reply to message #563899] Fri, 17 August 2012 13:47 Go to previous messageGo to next message
hbhanujan
Messages: 28
Registered: August 2012
Location: Dallas, TX
Junior Member
Michel

Thank you very much. I got the block to execute and it did insert the rows and am working on exiting the block. It's taking a while and that's ok for now. Thank you for all your help.

Hari

Report message to a moderator

Re: ORA-00947: not enough values [message #563905 is a reply to message #563902] Fri, 17 August 2012 14:35 Go to previous message
Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now consider optimizing your PL/SQL block in the following way (which is what BlackSwan underlied):
DECLARE
  v_Db_name     VARCHAR2 (20);
BEGIN
  FOR dbl IN (SELECT db_link INTO v_DB_link FROM dba_db_links) LOOP
    EXECUTE IMMEDIATE 'select name  from v$database @' || dbl.db_link INTO v_Db_name;
    INSERT INTO ffas_schema_tbl 
      SELECT sysdate, v_Db_name, owner, round(sum(bytes)/(1048576*1024),2)
      FROM fsa01t_dba_segments  
      GROUP BY owner  
      HAVING sum(bytes)/(1048576*1024) > 1.0;
  END LOOP;
END;
/

It does the same thing than yours but will be, I bet, far faster.
(Note I didn't test the syntax as I have not your tables.)

Regards
Michel

[Updated on: Sat, 18 August 2012 01:42]

Report message to a moderator

Previous Topic: Procedure Performance
Next Topic: COLUMN TO ROW & ROW TO COLUMN
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 19 06:25:53 CDT 2013

Total time taken to generate the page: 0.12959 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.