Select * from table in a procedure 2

[xxing]

Hi

I am new to Oracle having a SQL Server background. I like the richness of Oracle, but I am stumped on how to create a stored procedure that selects the columns from a table.

Like this:

create or replace procedure "P1"
is
begin
select * from T;
end;
/

The code to create Table T is shown:

whenever sqlerror continue
drop table T;
create table T
(
x number(2)
,y varchar2(1)
);
insert into T values(10,'a');
insert into T values(20,'b');
insert into T values(30,'c');
insert into T values(40,'d');
insert into T values(50,'e');
select * from T;

The stored procedure will be called in a report, so I wish to access the contents of table T as follows:

exec P1

Can someone please advise on how to do this. Thankyou

regards
Mark

 

[carp]

Mark -
Welcome to Mondo Oracle!
As you say, there is a richness in Oracle that gives you several approaches to most situations - and your case is one of those situations. If you could give us a better idea of what you want your procedure to do, it will help us help you find the most satisfactory path.
Standing by to be of assistance -

 

[Dagon]

You're obviously quite new to stored procedures since selecting columns from tables is the bread and butter of what stored procs do. Perhaps if you gave us what you have attempted to do so far we can then tell you why it's not working.

For Oracle-related work, contact me through Linked-In.

 

[xxing]

Hi Carp and Dagon

Thank you for replying. Yes I am very new to Oracle. But I have some experience in Sql Server.

Background
My boss wants to automate the counting of work that comes into his section.

What I've done
I've created a stored procedure that gets the data from history tables on different schemas. The data is inserted into a table. This stored procedure compiles successfully and works well. I want to create a second stored procedure that runs this stored procedure and then selects the data from the table. Something like this.

create or replace "ShowMonthlyReport"
as
begin
execute LoadMonthlyReport -- load the data into a table
select * from MonthlyReport -- display the data
end;

Can you please show how this is done?
Shown below is the stored procedure that loads the data into a table. All I want to do is display this data. A cognos report will execute the stored procedure and then display the data in its report.

create or replace procedure nzfsdata."MONTHLYREPORT_LOADDATA" 
as


	counter 		pls_integer;
	StartDate 		date;
	EndDate 		date;
	CPN				pls_integer;
	PFA				pls_integer;
	MonthYear		varchar2(20);
	PDA				pls_integer;
	Beatlist		pls_integer;
	DeploymentData	pls_integer;
	v_Callsigns		pls_integer;
	v_vehicles		pls_integer;
	v_PagerEntries	pls_integer;
	v_PagerGroups	pls_integer;
	Callsigns		pls_integer;
	DataSpatialZone	pls_integer;	
	DeploymentZone	pls_integer;
	SOP_HtmlPages	pls_integer;
	SOP_TablePages	pls_integer;
	WebStatic		pls_integer;
	TheZone			pls_integer;
	MySql			varchar2(4000);
	ColumnName		varchar2(50);
	i				pls_integer;
	WhereGoes		pls_integer;
	WhereFrom		pls_integer;
	TheDate			date;
	TheMonth		pls_integer;
	YearMonth		varchar2(7);

begin	

	MySql := 'Truncate table nzfsdata.MonthlyReport';
	execute immediate (MySql);
	
	for counter in 1 .. 14
	loop

		--------------------------------------------------
		-- Get the dates 
		TheDate:=  sysdate;              
		StartDate:= trunc(add_months(TheDate,-counter), 'MONTH');
		EndDate:= trunc(last_day(add_months(TheDate,-counter))+1);
		MonthYear:= to_char(StartDate,'mm/YYYY');
		
		--------------------------------------------------
		-- CPN data 
		
		select 
			coalesce(count(*),0) 
		into CPN
		from E3BOB2.ACTIONED_CHANGE_REQUESTS
		where 
			to_Datetime >= StartDate
			and to_datetime < EndDate
			and upper(Datatype) IN ('COMMONPLACE','COMMONALIAS')
			and upper(RequestType) IN ('NEW DATA','MODIFY EXISTING','DELETION');
			
		--------------------------------------------------
		-- PFA data		
		
		select 
			coalesce(count(*),0) 
		into PFA
		from E3BOB2.ACTIONED_CHANGE_REQUESTS
		where 
			to_Datetime >= StartDate
			and to_datetime < EndDate
			and upper(Requesttype) IN ('NEW PFA','NEW LINK PFA','MODIFY PFA','DELETION PFA');
		
		--------------------------------------------------
		-- Deployment data
		
		-- PDA data		
		select 
			coalesce(count(*),0) 
		into PDA
		from
		(
			select distinct
				list_id
				,modified_date
				,modified_time
			from
				nzfsdata.pda_hist
			where
				Action <> 'DELETE'
				and modified_date >= StartDate
				and modified_date < EndDate
		);
		
		-- Get Beatlists
		select 
			coalesce(count(*),0) 
		into BeatList
		from nzfsdata.Beatlists_hist
		where
			substr(Action,1,6) <> 'DELETE'
			and tycod = 'STRU'
			and Beatorder = 0
			and modified_date >= StartDate
			and modified_date < EndDate;
		
		-- Add together		
		DeploymentData := PDA + BeatList;		
		--------------------------------------------------
		-- Callsigns and Paging
		
		-- Callsigns
		select 
			coalesce(count(*),0)
		into v_callsigns
		from nzfsdata.callsigns
		where 
			(
				effectivefrom between startdate and enddate
				and effectiveto between startdate and enddate
			)
			or 
			(
				effectivefrom between startdate and enddate 
				and effectiveto is null
			);
			
		-- Vehicles
		select 
			coalesce(count(*),0)
		into v_vehicles
		from nzfsdata.vehicles
		where 
			(
				effectivefrom between startdate and enddate
				and effectiveto between startdate and enddate
			)
			or 
			(
				effectivefrom between startdate and enddate 
				and effectiveto is null
			);
			
		-- PagerEntries
		select 
			coalesce(count(*),0)
		into v_PagerEntries
		from nzfsdata.pagerentries
		where 
			(
				effectivefrom between startdate and enddate
				and effectiveto between startdate and enddate
			)
			or 
			(
				effectivefrom between startdate and enddate 
				and effectiveto is null
			);

		-- PagerGroups
		select 
			coalesce(count(*),0)
		into v_PagerGroups
		from nzfsdata.pagergroups
		where 
			(
				effectivefrom between startdate and enddate
				and effectiveto between startdate and enddate
			)
			or 
			(	effectivefrom between startdate and enddate 
				and effectiveto is null
			);
		
		-- Add together
		Callsigns:= v_callsigns + v_vehicles + v_PagerEntries + v_PagerGroups;
		--------------------------------------------------
		-- ZONES
		
			select 
				coalesce(sum(coalesce(new_zone,0)+coalesce(mod_zone,0)+coalesce(del_zone,0)),0)
			into DataSpatialZone
			from e3bob2.zone_version_history
			where
				LiveDate >= StartDate
				and LiveDate < EndDate;
				
			select
				coalesce(count(*),0)
			into DeploymentZone
			from nzfsdata.zone_hist
			where
				modified_date >= StartDate
				and modified_date < EndDate
				and action <> 'UPDATE - NEW';     
			
		-- Add together
		TheZone:= DataSpatialZone + DeploymentZone;			
		--------------------------------------------------
		-- WEB - STATIC - PAGES
				
		-- This is where the number of SOP pages that have changed is recorded. 
		-- Robocopy copies the files changed from Prod to Stage.
        -- A Robocopy log file shows the number of files that have changed.
		-- Sql Loader puts the log file into table MONTHLYREPORT_ROBOCOPY 
		-- This is done by a daily job.
		
		select 
			 coalesce(sum(copied),0)
		into SOP_HtmlPages
		from nzfsdata.monthlyreport_robocopy
		where
			to_date(processed,'yyyy-mm-dd') >= StartDate
			and to_date(processed,'yyyy-mm-dd') < EndDate;
		
		select 
			count(*)
		into SOP_TablePages
		from sys.dba_audit_trail dat
		where 
			dat.owner='HAZOWN'
			and dat.obj_name in ('BL_BUTTON_LIST','SOP','Z_ZONE','I_INFO','N_NOTIFY','R_RESPOND','FA_FIREAUTH','GA_GASAUTH','LA_LOCALAUTH','PA_POWERAUTH')
			and dat.timestamp >= StartDate
			and dat.timestamp < EndDate;

		
		-- Add together the SOP counts
		webstatic:= SOP_HtmlPages + SOP_TablePages;
		--------------------------------------------------	
		-- INSERT INTO TABLE MONTHLREPORT
	
		-- Insert ID and Description names
		if counter = 1 then
			insert into MonthlyReport(ID,Description) values (1,'Date Headers');
			insert into MonthlyReport(ID,Description) values (2,'CPN data');
			insert into MonthlyReport(ID,Description) values (3,'PFA data');
			insert into MonthlyReport(ID,Description) values (4,'Deployment data');
			insert into MonthlyReport(ID,Description) values (5,'Callsign '||'&'||' Paging');
			insert into MonthlyReport(ID,Description) values (6,'Zones');
			insert into MonthlyReport(ID,Description) values (7,'Web - Static Pages');
		end if;
		
			-- The ColumnName to use
			ColumnName:= 'M'||to_char(counter);
			
			-- Date headers 			
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||MonthYear||''' where ID = 1';
			execute immediate (mySql);
			
			-- CPN Data
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||CPN||''' where ID = 2';
			execute immediate (mySql);
			
			-- PFA Data
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||PFA||''' where ID = 3';
			execute immediate (mySql);
			
			-- Deployment Data
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||DeploymentData||''' where ID = 4';
			execute immediate (mySql);
			
			-- Callsign and Paging
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||Callsigns||''' where ID = 5';
			execute immediate (mySql);
			
			-- Zones
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||TheZone||''' where ID = 6';
			execute immediate (mySql);
			
			-- Web - Static Pages
			mySql := 'update MonthlyReport set '||ColumnName||' = '''||Webstatic||''' where ID = 7';
			execute immediate (mysql);
	end loop;
	commit;
end;
/

 

[Dagon]

create or replace "ShowMonthlyReport"
as
begin
   execute LoadMonthlyReport   -- load the data into a table
   select * from MonthlyReport -- display the data
end;

Three things that I can see are wrong with this:

a) You can't do execute in PL/SQL. Execute is just used in SQL*Plus to run a stored procedure without having to put begin..end around it. It's really just a piece of shorthand. In PL/SQL itself, you'd just need:

begin
LoadMonthlyReport;
end;

b) You have to select into a set of variables. PL/SQL won't display anything to the screen by default. But since you wrote the other stored procedure, you must know that.

c) You appear to be executing LoadMonthlyReport when your procedure is called MONTHLYREPORT_LOADDATA.

In general, I'm not puzzled why you need to display the data with PL/SQL when you say you have Cognos, which will probably do a better job of it. PL/SQL isn't really a reporting tool, so displaying data in fancy ways isn't its strong point.

However, some of the options you would have available are:

a) just do a direct select * from MonthlyReport in something like SQL*Plus without using PL/SQL. With appropriate formatting options (set heading off, pagesize 0 etc), the output may look reasonable.

b) Read through the table in PL/SQL with a cursor and use dbms_output.put_line to write each row to the screen in whatever format you want. However, it might be quite hard work to get all the columns lined up nicely with headings. You'll probably need to use things like RPAD to get the columns to be the same size on every row e.g.

for record in cursor loop
   dbms_Output.put_line(rpad(col1, maxlen)||rpad(col2, maxlen)||..);
end loop

Remember that to see the output of dbms_Output you need to execute the command "set serveroutput on size xxxx" in SQL*Plus. Note also that there are limits on the size of the buffer that dbms_output can hold (denoted by the size xxxx), so if it's a very big report that approach may not work.

c) Use UTL_FILE to write the data to an external file. That would remove the memory restrictions, but you'd still have to do the formatting of the rows yourself.



For Oracle-related work, contact me through Linked-In.

 

[xxing]

I found a website that answered my question. Compared to sql server it's surprisingly difficult to do a simple thing like select * from a table in a stored procedure. This website has a good explanation for SQL Server Programmers

http://www.akadia.com/services/ora_return_result_set.html

regards
Mark

 

[Dagon]

As I said, it's not what PL/SQL is designed for.

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

The page you've listed shows ways of getting data back programmatically from PL/SQL (cursors, objects, temporary tables) , but none of it will help you display the data on the screen or spool it to a file (if that was your intention). It also incorrectly describes database objects as "PL/SQL tables".

For Oracle-related work, contact me through Linked-In.

 

[carp]

xxing -
It may or may not be difficult, depending on what it is you are really trying to accomplish and how you go about it. For instance, if all you want to do is display the values to your screen via SQL*Plus, the following would do it:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
   -- POPULATE THE TABLE
   nzfsdata.MONTHLYREPORT_LOADDATA;
   -- DISPLAY THE RESULTS
   FOR i IN (SELECT * FROM t) LOOP
      dbms_output.put_line('x = '||i.x||' and y = '||i.y);
   END LOOP;
END;

As Dagon indicated earlier, if your intent is to write this to a file, it becomes marginally more complicated (you need to open and close the file; writing to the file is about as complicated as the dbms_output.put_line call above). So the degree of difficulty here is almost entirely dependent upon what it is you need to do with the data once you collect it.