1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL / SQL Example

Discussion in 'SQL PL/SQL' started by tomloera, Feb 27, 2011.

  1. tomloera

    tomloera Forum Expert

    Messages:
    167
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Monterrey Mexico
    Hi Friends;

    Can help me with any example of the structure of PL / SQL program with the tables of eBS?

    Thanks of your help
    Regards
    Tom
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Tom,

    What do you mean? Oracle eBS tables are like any other oracle tables. The structure of the tables are as per business need. To see the structure of any table just use the DESCRIBE command.

    And what do you mean by "structure pf PL/SQL program"? If you have access to an eBS schema, you can just see the procedure create scripts for yourself by accessing the USER_SOURCE or DBA_SOURCE views.
     
  3. tomloera

    tomloera Forum Expert

    Messages:
    167
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Monterrey Mexico
    Hi Sadik;

    Thanks by you answered, in the information by the tables i not have problem.

    My requirement is about any program in PL/SQL using the tables of eBS are not of sales, AR, AP or GL any program I only want the structure of the program for use how model for other programs.

    I hope explain beter my requirement

    Thanks a lot Sadik

    Tom
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, you don't. What, exactly, do you mean by "the structure of the program for use"?
     
  5. tomloera

    tomloera Forum Expert

    Messages:
    167
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Monterrey Mexico
    David;

    Good Morning;

    Well in my comment of "the structure of the program for use" is the next:
    This is the structure of the program in PL/SQL

    Declare
    Begin
    <SQL or PL/SQL instructions>
    Exception
    End;
    /

    I would like to review a program with this structure, because I want to develop a program in PL / SQL but I have not made it to work properly.

    I hope I have explained correctly.

    Regards
    Tom
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post what you have written and the error messages it generates and someone most likely will be able to assist you.
     
  7. tomloera

    tomloera Forum Expert

    Messages:
    167
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Monterrey Mexico
    David;

    This is my program

    Declare
    CompaƱia varchar2(50);
    Cliente varchar2(50);
    Num_Fact varchar2(10);
    Credito number(20);
    Nota_Credito varchar2(10);

    Begin

    select
    c.name,
    b.customer_name,
    a.trx_number Num_Factura,
    a.amount_credited,
    f.trx_number

    Into
    CompaƱia,
    Cliente,
    Num_Fact,
    Credito,
    Nota_Credito

    from ar_payment_schedules_all a,
    ar_customers b,
    hr_all_organization_units c,
    ar_receivable_applications_all g,
    ra_customer_trx_all f
    where a.customer_id = b.customer_id(+)
    and a.org_id = c.organization_id(+)
    and g.customer_trx_id = f.customer_trx_id(+) and a.customer_trx_id = g.applied_customer_trx_id(+)

    and nvl(a.amount_due_remaining,0)<>0
    and a.amount_credited <> 0;
    End;

    This is the errors " ORA-01422: exact recovery returns a larger number of rows than requested"
    " ORA-06512: in the linea 10"

    Thanks a lot by your help
    Tom
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Error- Cause - Action.

    Rewrite the query so that it return only one row.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Or use a cursor and a loop:

    Code (SQL):
     
    DECLARE
    ...
    cursor mycur IS ...
     
    BEGIN
    FOR myrec IN mycur loop
    ...
    END loop;
    END;
    /
     
    Access the values like this:

    myrec.name
    myrec.customer_name
    ...

    then do your processing.