Solved

Oracle: Insert function

Posted on 2012-04-12
9
558 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

820 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