Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic declaration of %rowtype
Dynamic declaration of %rowtype [message #275630] Mon, 22 October 2007 02:31 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: proble in sql?
Next Topic: Statspack (merged by MHE)
Goto Forum:
  


Current Time: Sat Dec 10 18:54:03 CST 2016

Total time taken to generate the page: 0.13678 seconds