Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle: Insert function

Posted on 2012-04-12
9
Medium Priority
?
581 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 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