Dynamic declaration of %rowtype [message #275630] |
Mon, 22 October 2007 02:31  |
shilpak
Messages: 1 Registered: October 2007
|
Junior Member |
|
|
I am facing problem in Declaring %Rowtype in procedure.
I am creating the table at the start if doesn't exists.
i want to declare rec_daily_oper Alarms%rowtype, but this is giving compilation error when table doesn't exists; if table exists then procedure is executing absolutely fine.
Plz help to resolve this problem.
CREATE OR REPLACE PROCEDURE SP_RPT_(pi_date IN DATE)
IS
Begin
declare
x number;
Begin
select table_name into x from all_tables
where owner = 'SCOTT' and table_name = 'ALARMS';
EXCEPTION
WHEN NO_DATA_FOUND THEN
execute immediate 'CREATE TABLE ALARMS
(
TIME DATE,
REPEATED_HOUR NUMBER(1),
SECS NUMBER(10),
RECORD NUMBER(6)
)';
END;
DECLARE
var_l_starttime DATE;
var_l_endtime DATE;
rec_daily_oper ALARMS%ROWTYPE;
rec_daily_stationname stationmaster%ROWTYPE;
TYPE T_CURSOR IS REF CURSOR;
cur_daily_operatoractivity T_CURSOR;
BEGIN
var_l_strsql := 'SELECT * FROM ALARMS_10_2007
WHERE ((MESSAGE LIKE ''%Login%'' OR MESSAGE LIKE ''%Logout%'')
AND CLASS = 455)
and time BETWEEN ''' || var_l_starttime || ''' AND ''' || var_l_endtime || '''' ||;
OPEN cur_daily_oper FOR var_l_strsql;
LOOP
FETCH cur_daily_oper INTO rec_daily_oper; --here i am facing problem
END LOOP;
END;
[Updated on: Mon, 22 October 2007 02:39] by Moderator Report message to a moderator
|
|
|
Re: Dynamic declaration of %rowtype [message #275633 is a reply to message #275630] |
Mon, 22 October 2007 02:43  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
How could Oracle know waht is %rowtype if the table does not exist?
This is Oracle not Sybase/SQL Server. YOu don't create a table on the fly.
Use a GTT (Global Temporary Table).
In addition, your query inside the string will not work. Use a static cursor with parameters.
Regards
Michel
|
|
|