Solved

Oracle: Insert function

Posted on 2012-04-12
9
569 Views
Last Modified: 2012-04-12
Experts,

I have a function that uploads my data for each execution.  I am using a create and replace function.  The issue is that I want to keep my records in the table everytime I execute the function.

What is the best way to do this?  I assume the "replace" is the issue but want expert advice.

Thanks
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37837620
create or replace on the function declaration has no impact on data within tables that function references.

maybe I'm not understanding the question though.

can you post some code/data with expected results?
0
 

Author Comment

by:Maliki Hassani
ID: 37837705
Hi,

I have attached info that can help you understand.

So what is happening is that when I run execute function it replaces the values that I have already in the table.  For example:

if john doe and his stats are already in the table, when I execute the fuction is overwrites the data for john doe.

I want to have multiple stats for john doe, so I can store is daily productivity.

The way that my function is written isn't what I want.  I have seen insert statements but not sure how to modify my function to do that.
Code.txt
database-table.docx
0
 
LVL 2

Expert Comment

by:Mohamed_allabakash
ID: 37837801
Create or replace is to create the function and it doesn't affect the data. After you create function, you dont need to create it again and again.. the way the insert query is written looks weird... It has insert query for every operator (hard coded). instead you can pass the operator as parameter and use the insert query only once.. and call the function for every operator you wants to insert....
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 74

Expert Comment

by:sdstuber
ID: 37837825
I can't read your word document,  

looks like it was probably trying to show me some data and I think table structure.

just post text of those, not pictures
0
 

Author Comment

by:Maliki Hassani
ID: 37837831
Yeah, I was reading that I could do that but I am not sure how.  

If we take the way it is.. "one step at a time I guess".  How can I modiify it to insert?  Perhaps using a procedure?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 350 total points
ID: 37837850
your old data is disappearing because of this line


      EXECUTE IMMEDIATE 'TRUNCATE TABLE NOC_OPERATOR_REPORTING';
0
 

Author Comment

by:Maliki Hassani
ID: 37837868
Ahhh..  Let me see what happens when I remove it! thanks
0
 
LVL 2

Assisted Solution

by:Mohamed_allabakash
Mohamed_allabakash earned 150 total points
ID: 37837889
Its making the table empty

EXECUTE IMMEDIATE 'TRUNCATE TABLE NOC_OPERATOR_REPORTING';

You can comment this line., so that it stops overwriting and you can insert your new records and manipulate the data as you want.
0
 

Author Comment

by:Maliki Hassani
ID: 37837936
Great, thanks everyone.  I will be posting another question about how to pass the operator as parameter and use the insert query only once..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question