Home » SQL & PL/SQL » SQL & PL/SQL » Problem with annonymous block (Oracle 9i)
Problem with annonymous block [message #421256] Mon, 07 September 2009 00:42 Go to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Problem: I need to insert all the values of emp table into test table but not using select statement,so i have written the below annonymous block using nested tables.But it is giving some error


declare
   TYPE NumTab IS TABLE OF emp%ROWTYPE;
   a1 NumTab;
begin
    forall i in a1.first..a1.last
    insert into test values ( a1(i));
end;        

    insert into test values ( a1(i));
                *
ERROR at line 6:
ORA-06550: line 6, column 17:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 6, column 5:
PL/SQL: SQL Statement ignored



Can anyone help me in this ?
Also Please tell me is there any way to view the record using any PL/SQL Collection without explicitly giving the column name?



[Updated on: Mon, 07 September 2009 00:47]

Report message to a moderator

Re: Problem with annonymous block [message #421257 is a reply to message #421256] Mon, 07 September 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table test as select * from emp where 1=0;

Table created.

SQL> declare
  2     TYPE NumTab IS TABLE OF emp%ROWTYPE;
  3     a1 NumTab;
  4  begin
  5     select * bulk collect into a1 from emp;
  6      forall i in a1.first..a1.last
  7      insert into test values a1(i);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from test;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20

12 rows selected.

Regards
Michel

[Updated on: Mon, 07 September 2009 01:02]

Report message to a moderator

Re: Problem with annonymous block [message #421263 is a reply to message #421256] Mon, 07 September 2009 01:17 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
Thanks for the help. This works. Also regarding my 2nd questions

i have modified the above code to something like this
declare
       TYPE NumTab IS TABLE OF emp%ROWTYPE;
       a1 NumTab;
    begin
       select * bulk collect into a1 from emp;
	   forall i in a1.first..a1.last
        dbms_output.put_line(a1(i));
    end;



What i wanted is to know is it possible to get the record using any PL/SQL collections without giving the column name ??
Re: Problem with annonymous block [message #421264 is a reply to message #421263] Mon, 07 September 2009 01:23 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no way in pure PL/SQL.

Regards
Michel
Previous Topic: help in string replace
Next Topic: Issue in Bulk Collect
Goto Forum:
  


Current Time: Sat Dec 03 14:10:35 CST 2016

Total time taken to generate the page: 0.21256 seconds