?
Solved

Oracle: Insert function

Posted on 2012-04-12
9
Medium Priority
?
574 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 1400 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 600 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

'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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 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