CODEWITHSUNDEEP
sqloracle-databaseplsql
JCBA
JCBA

Reputation: 53

How to convert to procedure

I need help in creating this anonymous block into a procedure. I am new to PLSQL. Any help will be much appreciated. Thank you in advance. I would like this query to run just by calling a procedure.

TRUNCATE TABLE dblink_status_tbl;
set serveroutput on;
-- or equivalent for your client

declare
--  l_dummy dual.dummy%type;
l_dummy VARCHAR2(20);
l_status VARCHAR2(100);
begin
    for r in 
    (select db_link from all_db_links where db_link in 
    (   'WP1',
        '6P1',
        'OP3',
        'LP1',
        'ODS')
        and owner = 'CAMPER') 
    loop
        begin
            execute immediate 'select 1 from dual@' || r.db_link into l_dummy;
                l_status:= 'ACTIVE';
                dbms_output.put_line(r.db_link|| ',' || l_status);
                rollback work;
                execute immediate 'alter session close database link ' || r.db_link;
            exception
            when others then
                l_status:= sqlerrm;
                l_status := replace(replace(l_status,CHR(13), ' '),CHR(10),' ');
                l_status := '"' || l_status || '"';
                dbms_output.put_line(r.db_link|| ',' || l_status);
        end;
            insert into dblink_status_tbl 
            values(r.db_link,l_status);
            commit;
    end loop;
end;

Upvotes: 1

Views: 84

Answers (2)

maddy
maddy

Reputation: 358

Make it like something:

create or replace procedure proc_name as
  l_dummy VARCHAR2(20);
  l_status VARCHAR2(100);
begin
  ...
Using l_dummy,l_status
end;

And run this like :

"Exec proc_name" or "execute proc_name"

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Basically, you need only the first line in my example:

create or replace procedure p_your_proc as
  -- from now on, it is your original code
  l_dummy VARCHAR2(20);
  l_status VARCHAR2(100);
begin
  ...
end;
/

Once it is created, run it as

begin
  p_your_proc;
end;
/

P.S.

At the beginning, you're truncating a table - if it is necessary within the procedure, you'd use dynamic SQL (as it is a DDL):

begin
  execute immediate ('TRUNCATE TABLE dblink_status_tbl');
  ...
end;

Or, simply delete its contents as

begin
  delete from dblink_status_tbl;
  ...
end;

Upvotes: 2

Related Questions