Home » SQL & PL/SQL » SQL & PL/SQL » storing the value into a variable in a stored procedure (oracle 9i, unix)
storing the value into a variable in a stored procedure [message #315646] Tue, 22 April 2008 05:19 Go to next message
bapalu
Messages: 5
Registered: April 2008
Junior Member
I want to store the output value into a variable say 'i' while running the query against v$views in a stored procedure. I want to take the output from the query as input for checking the condition.

In addition to the above, Is there any oher way or thoughts where we can stored value in a variable ??? Is it possible with declaring global variables? or using RETURN VALUE??

Any help will be appreciated.

Thanks Venkat


CREATE OR REPLACE procedure policy_dg001_1(policy_id in varchar2, db_link varchar2, db_name varchar2)
as
status varchar2(5);
hostname varchar2(30);
created_date date;
i varchar2 (10):=0;
policyid varchar2(10) := policy_id;
dbname varchar2(30) := db_name;
db_lnk varchar2(50) ;
job_que varchar2(50) := 'job_queue_processes';
--val varchar2(512) := 'value';

--cursor x is select policy_id from policy_master_tbl;

begin
--select db_link_name into db_lnk from db_master_tbl where dbname=db_name; --dbname should be unique in db_master_tbl
--dbms_output.put_line(db_lnk);

select db_name,hostname into dbname,hostname from db_master_tbl where dbname=db_name;
select POLICY_CREATION_DATE into created_date from policy_master_tbl where policy_id = policyid;

--open x;
begin

EXECUTE IMMEDIATE 'select value from v$parameter@' ||db_link|| ' where name= :job_queue' into i using db_name;
dbms_output.put_line(i); -- please consider that this value should be populated with the above query


--select value into i from v$parameter@db_lnk where name= :job_queue;
--dbms_output.put_line(i);

if (i>0) then

insert into report_tbl values ('DG001','Unauthorized Database job/batch queues are present in the database.','Fail',db_name,hostname,created_date, sysdate,'');
update report_tbl set Remarks=('Database jobs were submitted unauthorizedly') where policy_id = policyid and dbname=db_name;
update db2policy_tbl set status='Fail';

else

insert into report_tbl values ('DG001','Unauthorized Database job/batch queues are not present in the database.','Pass',db_name,hostname,created_date, sysdate,'');
update db2policy_tbl set status='Pass'where policy_id=policyid and dbname=db_name;

end if;

--close x;


end;


end;
/
Re: storing the value into a variable in a stored procedure [message #315663 is a reply to message #315646] Tue, 22 April 2008 06:13 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Quote:
Is it possible with declaring global variables? or using RETURN VALUE??

Yes. Yes.

Regards
Michel
Previous Topic: Selecting one of the Union Clause
Next Topic: group by range
Goto Forum:
  


Current Time: Tue Dec 06 04:27:52 CST 2016

Total time taken to generate the page: 0.18338 seconds