hi ,
i have run the program below get invalid option error please help to correct this error..
1 DECLARE
2 SQL_SMT VARCHAR2(350);
3 plsql_block varchar2(500);
4 V_DEPTNO NUMBER := 10;
5 V_TOTAL NUMBER;
6 V_ENAME VARCHAR2(30) :='SMITH';
7 V_COMM NUMBER;
8 BEGIN
9 SQL_SMT := 'CREATE TABLE MY_EMP'||
10 'AS SELECT * FROM EMP'
11 ||'WHERE DEPTNO=:1;';
12 EXECUTE IMMEDIATE SQL_SMT USING V_DEPTNO;
13 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM MY_EMP;' INTO V_TOTAL;
14 DBMS_OUTPUT.PUT_LINE('STUDENT ADDED:'||V_TOTAL);
15 PLSQL_BLOCK :='DECLARE'||
16 'V_DATE DATE;'||
17 'BEGIN'||
18 'SELECT SYSDATE INTO V_DATE FROM DUAL;'||
19 'DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE,''DD-MON-YYYY'');'||
20 'END;';
21 EXECUTE IMMEDIATE PLSQL_BLOCK;
22 SQL_SMT :='UPDATE MYEMP SET COMM = 2457'||
23 'WHERE ENAME =:1;'||
24 'RETURNING COMM INTO :2';
25 EXECUTE IMMEDIATE SQL_SMT USING
26 V_ENAME RETURNING INTO V_COMM;
27 DBMS_OUTPUT.PUT_LINE('NEW COMM:'||V_COMM);
28* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 12
if i run this
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
DBMS_OUTPUT.PUT_LINE(l_nam);
end;
/
RESEARCH
PL/SQL procedure successfully completed.
it is running successful but i modify this program to below it show error
declare
sql1 varchar2(100);
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
sql1 :='select dname, loc from dept where deptno = :1'
execute immediate sql1
into l_nam, l_loc
using l_dept ;
DBMS_OUTPUT.PUT_LINE(l_nam);
end;
/
ERROR at line 8:
ORA-06550: line 8, column 2:
PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the
following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
please help me .....
thanks in advance .....