Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Create procedure with PL/SQL

    Please can anybody help me on how i can create a procedure with the below PL/SQL


    Set termout off
    set heading off
    set verify off
    set pagesize 0
    SET pages 1000
    set lines 500
    set trims on
    set feedback off
    set serveroutput on size 1000000
    set colsep |


    spool 'C:\Users\olumuyiwa.akusebo\Desktop\SQL_SCRIPTS\pr oxy.txt'

    Declare

    vforacid tbaadm.gam.foracid%type;
    vproxyacid tbaadm.ott.acid%type;
    vtranid tbaadm.ott.tran_id%type;
    vtrandate date;
    vparttype tbaadm.ott.PART_TRAN_TYPE%type;
    vorgacid tbaadm.ott.ORG_ACID%type;
    vpartsrlnum tbaadm.ott.PART_TRAN_SRL_NUM%type;
    vproxyacct tbaadm.gam.foracid%type;
    vorgaccount tbaadm.gam.foracid%type;
    vuserid tbaadm.ott.RCRE_USER_ID%type;
    vstaffname tbaadm.get.EMP_NAME%type;
    vacctname tbaadm.gam.acct_name%type;
    vorgamt tbaadm.ott.org_tran_amt%type;
    vsolid tbaadm.ott.sol_id%type;
    vcheck number;
    vnarratn tbaadm.ott.tran_particular%type;

    CURSOR cot_maint IS
    select gam.sol_id,ott.acid,tran_id,tran_date,PART_TRAN_TY PE,ORG_ACID,PART_TRAN_SRL_NUM,org_tran_amt,tran_pa rticular
    from tbaadm.ott,tbaadm.gam
    where ott.acid=gam.acid
    ---and ott.sol_id='014'
    and gam.sol_id in (select sol_id from tbaadm.sst where set_id='ALL')
    and bacid='4545230001'
    ---and tran_date='11-sep-2012'
    and org_tran_amt <>total_offset_amt;

    BEGIN

    open cot_maint;
    loop
    fetch cot_maint into vsolid,vproxyacid,vtranid,vtrandate,vparttype,vorg acid,vpartsrlnum,vorgamt,vnarratn;
    exit when cot_maint%notfound;

    ---Confirm that Records have not been reversed
    Begin
    select count(1) into vcheck from tbaadm.tct
    where trim(tran_id)=trim(vtranid)
    and tran_date=vtrandate
    and trim(part_tran_srl_num) =trim(vpartsrlnum)
    and AMT_OFFSET=vorgamt;
    End;

    If vcheck = 0 then

    --Get Accounts
    begin
    select foracid into vproxyacct from tbaadm.gam
    where acid=vproxyacid;
    exception
    when no_data_found then
    vproxyacct:='null';
    end;

    begin
    select foracid,acct_name into vorgaccount,vacctname from tbaadm.gam
    where acid=vorgacid;
    exception
    when no_data_found then
    vorgaccount:='null';
    vacctname:='null';
    end;

    --Staff Name
    Begin
    select ENTRY_USER_ID into vuserid from tbaadm.htd
    where trim(tran_id)=trim(vtranid)
    and tran_date=vtrandate
    and trim(PART_TRAN_SRL_NUM)=trim(vpartsrlnum);
    exception
    when no_data_found then
    vuserid:='null';
    End;

    if vuserid ='null' then
    vstaffname:=null;
    else
    Begin
    select EMP_NAME into vstaffname FROM tbaadm.GET
    where EMP_ID= vuserid;
    exception
    when no_data_found then
    vstaffname:=vuserid;
    End;
    end if;
    dbms_output.put_line(vsolid||'|'||vproxyacct||'|'| |vorgaccount||'|'||vacctname||'|'||vorgamt||'|'||v tranid||'|'||vtrandate||'|'||vparttype||'|'||vpart srlnum||'|'||vuserid||'|'||vstaffname||'|'||vnarra tn);

    End If;
    end loop;
    close cot_maint;
    End;
    /
    spo off

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    create or replace procedure procedure_name as

    begin

    end;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please can anybody help me on how i can create a procedure with the below PL/SQL
    consider to simply Read The Fine Manual (URL below) yourself

    Contents
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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