Home » SQL & PL/SQL » SQL & PL/SQL » Exception Reserved word
Exception Reserved word [message #37840] Fri, 01 March 2002 19:04 Go to next message
Sanjay Raja
Messages: 1
Registered: March 2002
Junior Member
Hi,
I have a table named EXCEPTION.
While querying the table from SQL Plus I have no issues.
When I write a PL/SQL block to do the same it gives me compilation errors.

Is there anyway we can get around this problem ?
Oracle version we are using is 7.3
Here is the query----

CURSOR cur_employee_details IS
(SELECT
totspan.employeeid employeeid,
totspan.applydate applydate,
excp.in_or_out in_or_out,
excp.TYPE abs_code,
excp.amount amount,
extshced.name name
FROM
KRONOS.TOTALEDSPAN totspan,
KRONOS.EMPLOYEE emp,
KRONOS.EXCEPTION excp,
kronos.WTKEMPLOYEE wtkemp,
kronos.EXTERNALSCHEDULE extshced
WHERE
totspan.employeeid = emp.employeeid AND
SUBSTR(emp.employeenumber, 6) = 'E7W'
AND excp.totaledspanid = totspan.totaledspanid AND
((excp.in_or_out = 'I' AND
excp.TYPE = 'LV') OR (excp.in_or_out = 'O' AND
excp.TYPE = 'EV')) AND
emp.employeeid = wtkemp.employeeid AND
wtkemp.group_schedid = extshced.extschedid);
Re: Exception Reserved word [message #37852 is a reply to message #37840] Mon, 04 March 2002 01:22 Go to previous message
pratibha_md@yahoo.com
Messages: 1
Registered: March 2002
Junior Member
Hi ,

I think u have to go for dbms_sql(dynamic cursor method). I have given an example code for ur reference. I think it may help u.

good luck
Example
-- ----------------
/* This program demonstrates the usage of execute & fetch */
declare
m_empno varchar2(5);
m_ename varchar2(30);
m_deptno varchar2(5);
sql_stmt varchar2(1000);
i number:=0;
cur number:=0;
begin
/* exception table is copied from scott.emp */
sql_stmt := 'select empno,ename,deptno from exception';

cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,sql_stmt ,dbms_sql.v7);
dbms_sql.define_column(cur,1,m_empno,5);
dbms_sql.define_column(cur,2,m_ename,30);
dbms_sql.define_column(cur,3,m_deptno,5);
i := dbms_sql.execute(cur);
loop
if (dbms_sql.fetch_rows(cur) > 0) then
dbms_sql.column_value(cur,1,m_empno);
dbms_sql.column_value(cur,2,m_ename);
dbms_sql.column_value(cur,3,m_deptno);
dbms_output.put_line('Empno : ' || m_empno);
dbms_output.put_line('Name : ' || m_ename);
dbms_output.put_line('Dept# : ' || m_deptno);
else
dbms_output.put_line('*** End of File ***');
exit;
end if;
end loop;
dbms_sql.close_cursor(cur);
end;
-- ----------------
Previous Topic: query
Next Topic: Dates in the same calendar week.
Goto Forum:
  


Current Time: Thu Mar 28 17:10:20 CDT 2024