SQL & PL/SQL :: Store Procedure To Truncate And Copy Data
Jan 27, 2012
I am very new to oracle and SQL.I am trying to create a store proc that will copy 14 day of data into a table and then truncate the original table. When i compile following code....
CREATE OR REPLACE PROCEDURE STOPROC_TRUNCATE
( dateNum IN NUMBER )
IS
BEGIN
create table AUDIT_14Days as select * from AUDIT where TIMESTAMP >= (SYSDATE - dateNum);
truncate table AUDIT drop storage;
[code]....
I have a question like, Is it possible to hide the Store Procedure?
Scenario: I have write the SP which contains some logic based on my requirement. Once i developed this i need to implement this to my client page. So i need to hide the logic even the client opened the SP like exe file.
The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.
I'm having a trouble creating a store procedure that will just update the provided fields only.
i am trying to update multiple records using store procedure but failed to achieve
for example my source is
emp_name sal abhi 2000 arti 1500 priya 1700
i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only
i have tried following code
create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor) is begin open p_cursor for select sal into p_sal from emp; if sal<2000 then update emp set sal= sal+200; end i;f end;
and i have called the procedure using following codes
set serveroutput on declare p_sal emp.sal%type; v_cursor sys_refcursor; begin upt_sal(p_sal,v_cursor); fetch v_cursor into p_sal; dbms_output.put_line(p_sal); end;
the program is executing but i should get o/p like this after updating
Need a trigger in view with select statement that means
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE; CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1 INSTEAD OF DELETE ON TEST_VIEW DECLARE BEGIN Dbms_Output.Put_Line('STATEMENT TRIGGER.'); END;
i wanted to use select statement instead of delete.How can i get that
create or replace PROCEDURE INSERT_TESTTABLE ( PrimaryKey IN NUMBER ,One IN VARCHAR2 ,Two IN VARCHAR2 ,Three IN VARCHAR2 ,Four IN VARCHAR2 [code].......
And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist
I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.
For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".
DECLARE bef_val CUST%ROWTYPE; aft_val CUST_TEMP%ROWTYPE; BEGIN SELECT * INTO bef_val FROM CUST WHERE name = 'ABC'; SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC'; [code]....
For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?
I got to pull 30M data into local DB from a remote DB. As of now we have procedure to perform this task using an
Insert /*+append*/ table1 select * from rdb_view;
I observed using the copy command in sql plus has taken very less time than the procedure.Can we use the Copy command inside the PL/SQL block? I tried with dynamic SQL but It is not allowed I guess.
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2) is begin EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'|| 'PASSWORD EXPIRE'; end addUsers; /
I have found one interview question no.7 @ [URL] According to that it can be rollbackQuote:The truncate command is a DDL operation and just moves the high water mark and produces few rollback data.
But, I read That is not true in the case of oracle but it is true in the case of SQL Server.
I couldn't either DROP or TRUNCATE the table partitions that were created. Here are the DDLs and DMLs I'm using.
Create table student(no number(2),name varchar(2)) partition by range(no) (partition p1 values less than(10), partition p2 values less than(20), partition p3 values less than(30),partition p4 values less than(40)); Insert into student values(1,'a'); Insert into student values(11,'b'); Insert into student values(21,'c'); Insert into student values(31,'d');
When I do the following query, it returns data.
SELECT * FROM STUDENT PARTITION(p1);
But, when I try to perform any of the following queries, it says invalid partition name.
ALTER TABLE STUDENT DROP PARTITION p4; ALTER TABLE STUDENT TRUNCATE PARTITION p3;
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Our client has this scenario:May 23th 4:00 PM a user truncate a critical table. The customer need to recover the data before truncate.
The recover materials list like this: 1. A cold backup of this database at May 1th. 2. The archive log except May 1th ~ May 15th. 3. Every day exp at 00:30 and 12:00.
Is there any way to recover the data before truncate table with these material?
Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.
My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
begin for i in 1..5 loop insert into easy values(i,'777'||i,i||89,'Ris'||i||i);
[code]...
I was able to fetch primary keys and there values for a table and store them in stage table but only 1 record , I am stuck when there are more than 1 record Here is what i tried
SQL> SET serveroutput ON SQL> DECLARE 2 V_prim_key VARCHAR2(2000); 3 l_tab DBMS_UTILITY.uncl_array;
[code]...
If you look at the above code in the execute immediate where condition i have used id=1, which is 1 records only but if i change id <3 then there will be 2 records and i will get below error ORA-01422: exact fetch returns more than requested number of rows
Other problem if there are 3 primary keys as in above case i am fetching there values 1 by 1 , is there any way if i can fetch those values once in for all data to be stored in stage table in below format
Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.
But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?
how to tune qurey for coulumn wise data saved.because we have to join same table n number of times.for reference go through the following scnarios.
Suppose one table T1 is there and it has two column KEY and VALUE.if we are writing qurey for retriving desire result in row manner we have to join samae table no of times.
KEY Value agreesWith true id 1 assessment False basisForDateOfProgression 1 bestOverallResponse 2 bestOverallUnknownComments data is ok
Qurey:
select * from ( select t.agreesWith from t1 t )a
[Code]...
In this manner we can join upto bestOverallUnknownComments .so which method we follow to reduce the execution time and performance should be good.
I am getting the file using CLIENT_GET_FILE_NAME. I need to read the data from the .xsl file and convert it into blob. The file should not be stored in DB.
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT (identifier VARCHAR2(6), name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
I have a Data entry form which is a multirecord block;
Question: for example that form has 10 to 25 fields or columns more than that all the data has been entered, but before committing or saving that form i need to cross check the the data with a select query, whether the data entered is correct or not but before committing, that data it should be posted into that table if i find that one data is entered wrongly then i will modify that and again cross check and save the transaction permanently into the database table?