Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: anonymous PL/SQL block to procedure

    here is the block:

    DECLARE
    i NUMBER:=0;
    d VARCHAR(8);
    start_hour NUMBER := 0;
    end_hour NUMBER;
    BEGIN
    select to_char(datetime, 'YYYYMMDD') into d from current_auth_logs where rownum = 1;
    LOOP
    end_hour := start_hour +1;
    if end_hour > 23 then
    end_hour := 0;
    end if;
    INSERT INTO hourly_reports VALUES(d,
    lpad(start_hour,2,'0'),
    lpad(end_hour,2, '0'),
    nvl((select count(*) from current_auth_logs
    where to_char(datetime, 'HH24') = i), 0),
    nvl((select sum(amount/100) from current_auth_logs
    where to_char(datetime, 'HH24') = i), 0)
    );
    start_hour := start_hour + 1;
    i := i+1;
    EXIT WHEN i > 23;

    END LOOP;

    END;


    i tried to change it to a procedure by adding
    create or replace procedure test123() is at the beginning and remove DECLARE, but it doesn't seem to work.

    what i want is a procedure that doesn't take parameters and just run the sql statements.

    Thanks in advance

    mark

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: anonymous PL/SQL block to procedure

    change to :

    create or replace procedure test123 is
    // declaration of internal variables starts here
    i NUMBER:=0;
    d VARCHAR(8);
    start_hour NUMBER := 0;
    end_hour NUMBER;
    begin
    // coding comes here ...
    select to_char(datetime, 'YYYYMMDD') into d from current_auth_logs where rownum = 1;
    ...
    end test123;
    /
    show errors

    execute test123;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •