Procedure to insert values into multiple tables with conditions

Hi,

   I am new to oracle technical. I need to create plsql procedure to insert value into multiple tables with some conditions.

   Is there any sample procedure.


Thanks
Sunil
sunil_dsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sunil_dsAuthor Commented:
Hi,

  My actual problem  

  I have to write a procedure to insert values into multiple table where i have to satisfy some conditions.

example:

    there are 3 tables say a, b, c

    select * from table a;
    a_no   a_name    a_id
    ----------------------
    a1      hyd            231
    a2          blr       343
    a3      com       312

    a_no column has the sequentially generated number


    select * from table b;
    b_no   b_task
    -------------
    a1      hyd-b           
    a2          blr-b
    a3      com-b

    b_no = a_no


    select * from table c;
    c_no   c_len    c_dep     c_tot
    --------------------------------
    a1      1           4          4
    a2          4        6          24
    a3      3        8          24

    c_no = b_no

How can i write the procudure..

Hope im clear. Please let me know if you need more explanation.

Thanks
Sunil
0
sunil_dsAuthor Commented:
Hi,

  Am i clear on this?


Thanks
Sunil
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Swadhin RaySenior Technical Engineer Commented:
What are the insert values you want to pass and to which table .. Provide some more information like

1) Values for the columns with regards to the target table
2) is there any sequence no in your tables ... which is inserting can insert while you do an insert to the table.


Please  provide the input data you want to insert and show the expected result as an example which will be more clear.
0
sarabandeCommented:
why do you need 3 tables if they all have same key?

why do you need a procedure and not simply use insert statements?

Sara
0
slightwv (䄆 Netminder) Commented:
The online docs have information on writing procedures.  We can also help but your requirements are still not clear.

In any language:  In a procedure, you need to define your input and output parameters.

You then need to define your 'conditions' for what happens when.

Once you do all that it is a simple matter of adding the code to perform those actions.

The PL/SQL doc can be found here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

It is up to you to provide the parameters and logic.  Until we get that we really cannot help.
0
sunil_dsAuthor Commented:
Hi,

  Thanks for the reply..

actually i am using a custom for which consists of 3 parts in canvas. they are..
1. header
2. middle
3. detail

now i have developed a custom excel sheet to make the data entry easy. so the data entered in the excel columns get
stored in the OLTP database.

so i need to create one custom procedure to insert data into the tables. so that when ever i enter the data in excel and
save it, the data get stored in the tables.

Header table consists of
1. report_no
2. report_date
3. location
4. item_description
report_no is genereated sequentially (automatically)

middle table consists of
1. report_no  (cannot see in form but stored in database)
2. boq_number
3. boq_id
report_no is used to join the header and middle table

detail table consists of
1. report_no  (cannot see in form but stored in database)
2. length
3. depth
4. height
5. total (formula column of l * b * h)
6. lbd_id
7. boq_id
here middle.report_no and middle.boq_id should be equal to detail.report_no and detail.boq_id

hope im clear now..

Thanks
Sunil
0
slightwv (䄆 Netminder) Commented:
To clarify:
you created an Excel macro that will in turn call an Oracle stored procedure once for each row of the spreadsheet.

All this works and you just need to Oracle stored procedure.

Providing an example procedure is easy.  providing a 100% working solution will require more information.

Below is a very simple test case.

for example: "report_no is genereated sequentially (automatically)"

Is this generated in Excel or in Oracle with a trigger?
drop table tab1 purge;
create table tab1(col1 char(1));

drop table tab2 purge;
create table tab2(col1 char(1));


create or replace procedure myProc(invar1 in char, invar2 in char)
is
begin
	insert into tab1 values(invar1);

	insert into tab2 values(invar2);

	commit;
end;
/

exec myproc('a','b');

select * from tab1;
select * from tab2;

Open in new window

0
sunil_dsAuthor Commented:
Hi,

  Thanks for ur reply..

  As i already replied with the details I am slo sending you some more information.

  Canvas has 3 parts..
1. header part -- table name ==> XXSURVEY_REPORT_HEADER
2. middle part -- table name ==> XXSURVEY_REPORT_WBS_DETAILS
3. detail part -- table name ==> XXSURVEY_REPORT_MORE_LBD

report_no is genereated sequentially (automatically) in Oracle with a trigger

XXSURVEY_REPORT_HEADER
----------------------
Name                 Null     Type          
-------------------- -------- --------------
REPORT_NO            NOT NULL VARCHAR2(30)  
REPORT_DATE                   DATE          
ORGANIZATION_ID               NUMBER        
ORGANIZATION_CODE             VARCHAR2(3)    
PROJECT_ID                    NUMBER        
PROJECT_CODE                  VARCHAR2(25)  
RFI_NO                        VARCHAR2(30)  
CLIENT_MSHEET_NO              VARCHAR2(30)  
WBS_TASK_ID                   NUMBER        
WBS_TASK_NUMBER               VARCHAR2(25)  
SUBCON_MSHEET_NO              VARCHAR2(30)  
BOQ_ID                        NUMBER        
BOQ_NUMBER                    VARCHAR2(25)  
RFI_QUANTITY                  NUMBER        
UOM_CODE                      VARCHAR2(6)    
INSPECTED_QUANTITY            NUMBER        
ACCEPTED_QUANTITY             NUMBER        
CREATED_BY                    NUMBER        
CREATION_DATE                 DATE          
LAST_UPDATE_LOGIN             NUMBER        
LAST_UPDATED_BY               NUMBER        
LAST_UPDATE_DATE              DATE          
REJECTED_QUANTITY             NUMBER        
RFI_REV_NO                    NUMBER        
PARTY_ID                      NUMBER        
PARTY_NAME                    VARCHAR2(360)  
PARTY_NUMBER                  VARCHAR2(30)  
CUSTOMER_ID                   NUMBER        
ACCOUNT_NUMBER                VARCHAR2(30)  
AUTHORIZATION_STATUS          VARCHAR2(150)  
APPROVED_DATE                 DATE          
COMMENTS                      VARCHAR2(2000)
AUTHORIZED_BY                 NUMBER        
LOCATION_DESC                 VARCHAR2(250)  
LENGTH                        NUMBER        
BREADTH                       NUMBER        
HEIGHT                        NUMBER        
NO_OF_UNITS                   NUMBER        
CONV_FACT                     NUMBER        
MEASURED_QUANTITY             NUMBER        
CHAINAGE_FROM                 NUMBER        
CHAINAGE_TO                   NUMBER        
WORK_DONE_DATE                DATE          
MORE_LBD_QUANTITY             NUMBER        
CLIENT_IPC_QUANTITY           NUMBER        
SUBCON_IPC_QUANTITY           NUMBER        
CLIENT_CERT_QUANTITY          NUMBER        
REV_NO                        NUMBER        
REV_DATE                      DATE          
RFI_REQD                      VARCHAR2(1)    
EXECUTED_BY                   VARCHAR2(10)  
WO_NUM                        VARCHAR2(20)  
IS_CHAINAGE                   VARCHAR2(1)    
ITEM_DESC                     VARCHAR2(250)  
DOC_NAME                      VARCHAR2(60)  
DOC_PREFIX                    VARCHAR2(30)  
LAYER                         VARCHAR2(20)  
CD_NO                         VARCHAR2(30)  
KERB_TYPE                     VARCHAR2(10)  
DESCRIPTION                   VARCHAR2(250)  
BILL_ID                       NUMBER        
SHIFT_DESC                    VARCHAR2(80)  
WORK_START_DATE               DATE          
ATTRIBUTE1                    VARCHAR2(150)  
ATTRIBUTE2                    VARCHAR2(150)  
ATTRIBUTE3                    VARCHAR2(150)  
ATTRIBUTE4                    VARCHAR2(150)  
ATTRIBUTE5                    VARCHAR2(150)  
LOC_LINE_ID                   NUMBER    

--------------------------------------------------------------------------
XXSURVEY_REPORT_WBS_DETAILS
---------------------------
Name              Null Type          
----------------- ---- -------------
REPORT_NO              VARCHAR2(30)  
REV_NO                 NUMBER        
WBS_TASK_ID            NUMBER        
WBS_TASK_NUMBER        VARCHAR2(25)  
BILL_ID                NUMBER        
EXECUTED_BY            VARCHAR2(10)  
WO_NUM                 VARCHAR2(20)  
BOQ_ID                 NUMBER        
BOQ_NUMBER             VARCHAR2(25)  
UOM_CODE               VARCHAR2(3)  
CREATED_BY             NUMBER        
CREATION_DATE          DATE          
LAST_UPDATE_LOGIN      NUMBER        
LAST_UPDATED_BY        NUMBER        
LAST_UPDATE_DATE       DATE          
ATTRIBUTE1             VARCHAR2(150)
ATTRIBUTE2             VARCHAR2(150)
ATTRIBUTE3             VARCHAR2(150)
ATTRIBUTE4             VARCHAR2(150)
ATTRIBUTE5             VARCHAR2(150)
LINE_NUM               NUMBER

--------------------------------------------------------------------------
XXSURVEY_REPORT_MORE_LBD
---------------------------
Name                 Null Type          
-------------------- ---- -------------
REPORT_NO                 VARCHAR2(30)  
LENGTH                    NUMBER        
BREADTH                   NUMBER        
HEIGHT                    NUMBER        
NO_OF_UNITS               NUMBER        
CONV_FACT                 NUMBER        
TOTAL_QUANTITY            NUMBER        
CREATED_BY                NUMBER        
CREATION_DATE             DATE          
LAST_UPDATE_LOGIN         NUMBER        
LAST_UPDATED_BY           NUMBER        
LAST_UPDATE_DATE          DATE          
CHAINAGE_FROM             NUMBER        
CHAINAGE_TO               NUMBER        
ACCEPTED_QUANTITY         NUMBER        
COMMENTS                  VARCHAR2(250)
WORK_DONE_DATE            DATE          
LBD_ID                    NUMBER        
REV_NO                    NUMBER        
STATUS                    VARCHAR2(10)  
WIP_ORIGINATE_IPC_NO      VARCHAR2(30)  
LAYER                     VARCHAR2(20)  
CD_NO                     VARCHAR2(30)  
KERB_TYPE                 VARCHAR2(10)  
DESCRIPTION               VARCHAR2(250)
DIA_ID                    NUMBER        
DIAMETER                  NUMBER        
SHAPE                     VARCHAR2(150)
MATERIAL                  VARCHAR2(30)  
LENGTH1                   NUMBER        
LENGTH2                   NUMBER        
BREADTH1                  NUMBER        
BREADTH2                  NUMBER        
HEIGHT1                   NUMBER        
HEIGHT2                   NUMBER        
AREA                      NUMBER        
WBS_TASK_ID               NUMBER        
IS_PARTIAL_WIDTH          VARCHAR2(1)  
ATTRIBUTE1                VARCHAR2(150)
ATTRIBUTE2                VARCHAR2(150)
ATTRIBUTE3                VARCHAR2(150)
ATTRIBUTE4                VARCHAR2(150)
ATTRIBUTE5                VARCHAR2(150)
ATTRIBUTENUM1             NUMBER        
ATTRIBUTENUM2             NUMBER        
ATTRIBUTENUM3             NUMBER        
ATTRIBUTENUM4             NUMBER        
ATTRIBUTENUM5             NUMBER        
LINE_NUM                  NUMBER

----------------------------------------------------------------------
JOINS:
------
XXSURVEY_REPORT_HEADER.REPORT_NO = XXSURVEY_REPORT_WBS_DETAILS.REPORT_NO
XXSURVEY_REPORT_WBS_DETAILS.REPORT_NO = XXSURVEY_REPORT_MORE_LBD.REPORT_NO
XXSURVEY_REPORT_WBS_DETAILS.WBS_TASK_ID =XXSURVEY_REPORT_MORE_LBD.WBS_TASK_ID
----------------------------------------------------------------------
IN EXCEL I HAVE ALL THE COLUMNS

wHEN THEY ENTER THE DATA IN EXCEL WORK SHEET THE DATA SHOULD POPULATE IN DATABASE. SO I WANT TO USE THIS
STORED PROCEDURE TO DO THAT.

PLEASE LET ME KNOW IF U NEED MUCH MORE INFORMATION ON THIS.
0
slightwv (䄆 Netminder) Commented:
>>  As i already replied with the details I am slo sending you some more information.

You are not understanding what I was saying.  For example:  What are you seeing as the inputs to the procedure?

Are you wanting to pass in a single XML document or are you wanting one input parameter for each column in the spreadsheet?

We cannot define this for you since we do not understand your requirements.

If you have no requirements then it sounds like you want us to write all the code for you.  This site really isn't set up to do that.  Some Experts here have a 'Hire Me' button in their profiles and you can pay them to write code for you.

>>wHEN THEY ENTER THE DATA IN EXCEL WORK SHEET THE DATA SHOULD POPULATE IN DATABASE.

How do you see this working?  Will you have a button that executes a macro?


I provided an example procedure that takes parameters and inserts values into two tables.  This should be a great starting point for you to build from.

0
sunil_dsAuthor Commented:
Hi,

    I am not looking for the code. But some suggestions.

    And i did it with the example provided . Thanks for the example..

    I am new to technical. Can you please provide me the good sites for the learners to learn the oracle procedures, cursors, functions and triggers..

    Thanks for the help

Thanks
Sunil
0
slightwv (䄆 Netminder) Commented:
The online docs are the best place to start.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

There are several books on the subject but I've not read any so cannot recommend any.

Steven Feuerstein has a great site on PL/SQL:
http://www.stevenfeuerstein.com/

There are many sites out there once you understand what to search for.  You can almost always find examples if you look around.  If you cannot, that is what Experts-Exchange is for.
0
sunil_dsAuthor Commented:
Thanks
0
slightwv (䄆 Netminder) Commented:
Do you need any additional assistance with this question?

If not, do not forget to close it out.
0
sunil_dsAuthor Commented:
May i know how to close this question

but should be available for future references
0
slightwv (䄆 Netminder) Commented:
I'm not sure what you are asking.  You have already successfully closed several questions and even split points.

All your questions will remain in your asked questions so they will always be available for review.  There are a few ways to look at this information.  I typically view my profile and I can see my comments, asked, answered, blog posts, etc...

If this does not answer what you are asking please clarify what you are wanting.
0
sunil_dsAuthor Commented:
I have closed the question and split points.

 even then i got a comment asking me to close the question out if i dont need further clarification.

So I am in a doubt whether i am following the correct procedure for closing the question and split points.

Thats it..

Thanks
0
Swadhin RaySenior Technical Engineer Commented:
if you agree on the solutions that were provided to you then you should give the points and close the question.
For more information please go through the below URl and close this ticket if you get the solution:

http://www.experts-exchange.com/help.jsp?hs=26

0
slightwv (䄆 Netminder) Commented:
>>I have closed the question and split points.

The question is not closed.  If you have accepted multiple solutions and assigned the points then you have not submitted it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.