• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Oracle: Insert function

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
Maliki Hassani
Asked:
Maliki Hassani
  • 4
  • 3
  • 2
2 Solutions
 
sdstuberCommented:
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
 
Maliki HassaniAuthor Commented:
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
 
Mohamed_allabakashCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
sdstuberCommented:
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
 
Maliki HassaniAuthor Commented:
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
 
sdstuberCommented:
your old data is disappearing because of this line


      EXECUTE IMMEDIATE 'TRUNCATE TABLE NOC_OPERATOR_REPORTING';
0
 
Maliki HassaniAuthor Commented:
Ahhh..  Let me see what happens when I remove it! thanks
0
 
Mohamed_allabakashCommented:
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
 
Maliki HassaniAuthor Commented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now