PL/SQL insert problem

I guess you are missing smething in your code
Here is a pl/sql which show its working
ora816 SamSQL :> @t2
ora816 SamSQL :> declare
  2  sqlstr varchar2(2000);
  3  begin
  4  sqlstr := 'insert into t1
  5      select empno,ename,(Select dname from deptdup where deptdup.deptno=empdup.deptno) newcol from empdup';
  6  execute immediate sqlstr;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.80
ora816 SamSQL :> select * from t1;

     EMPNO ENAME      NEWCOL
---------- ---------- --------------
      7369 SMITH      ACCOUNTING
      7499 ALLEN      ACCOUNTING
      7521 WARD       MANAGER
      7566 JONES      ACCOUNTING
      7654 MARTIN     ACCOUNTING
      7698 BLAKE      COMPUTER
      7782 CLARK      ACCOUNTING
      7788 SCOTT      ASSISTANT
      7839 KING       ACCOUNTING
      7844 TURNER     CLERK
      7876 ADAMS      ACCOUNTING
      7900 JAMES      CLERK
      7902 FORD       ACCOUNTING
      7934 MILLER     ACCOUNTING

14 rows selected.

I am using 8.0.5 for Win NT
The brackets did not help, I still got the same error message.

Brad

I am using 8.0.5 for Win NT
The brackets did not help, I still got the same error message.

Brad

maybe the failure is more above in your pl/sql code

Brad,
PL/SQL and SQL are different.  PL/SQL is a procedural language.  Things that work in SQL don't always work in PL/SQL.  The best way I found to handle this type of problem is to create a PL/SQL Procedure and use cursors.

PROCEDURE xxx AS
CURSOR c1 IS
  select t1.r1,
      t1.r2,
      t1.r3,
      (Select r4 from t2 where t2.r1 = t1.r1
                         and   t2.r2 = t1.r2)
  from t1;
this c1%ROWTYPE
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO this;
      EXIT WHEN c1%NOTFOUND;  -- end of records, exit loop
     INSERT INTO t3
          VALUES (r1 = this.r1,
                  r2 = this.r2,...);
        COMMIT;
  END LOOP;
  CLOSE c1;
END;
END xxx;

Or something like this.  You can add exception handling and other checks along the way.  You can also put it in a package.  Hope it helps.
Rob

I guess that this is not a feture of oracle 8.0.5 .if you
run the below sql from sql*plus

Insert into t3
select t1.r1,
      t1.r2,
      t1.r3,
      (Select r4 from t2 where t2.r1 = t1.r1
                         and   t2.r2 = t1.r2)
from   t1;

You will get a "ORA-00936: missing expression" error . and if make a plsql and run it as a plsql program you will get a error something like this
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
cast <a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 4, column 62:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
) intersect minus union

Hope this helps
Sam

I think Oracle8.0.5 does not support that syntax, Oracle8i (8.1.5 or higher) does.

A SELECT statement cannot be used directly in a PL/SQL procedure because there is nowhere for the results to go.  It is a procedural language that requires a memory structure to store the results (i.e., SELECT INTO or CURSOR).

its not a select its an insert and this should also work in pl/sql

meikl ;-)

meikl, you're right.  I forgot about the insert.  I was thinking a straight SELECT statement.  Thanks for the correction.

My preference is to always open an explicit cursor in these situations so I can adequately control the execution of the procedure.  I try to avoid the implicit cursor in formal PL/SQL code.

Hi bwetmore,

As i already commented previously  , this is not a feature of 8.0.5. you can not implement this in PL/sql with this syntax. Although you can implement  by changing the insert statment as ser6398 suggested you before.


Sam

Try the following:
declare
begin

Insert into t3
select t1.r1,
      t1.r2,
      t1.r3,
      t2.r4
      from t1, t2
      where t2.r1 = t1.r1
        and t2.r2 = t1.r2;
commit;
end;
/

Hey mozartny,

I made that suggestion 3 days ago.  I suggest you read the EE guidelines on comments vs. answers: http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp