SQL & PL/SQL :: Script Store Data?

Feb 10, 2013

My requirement is to get primary key column name and there values of a table and store in a stage table

create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));

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

TAB_NAME V_PEIM_KEY V_PRIM_DATA DT
------- --------- ------- ------
EASY TXN_NAME,EVENT_ROWID,ID Ris11,7771,1 10-Feb-13
EASY TXN_NAME,EVENT_ROWID,ID Ris22,7772,2 10-Feb-13

View 5 Replies


ADVERTISEMENT

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

PL/SQL :: Store XML Data In Tables

Jul 21, 2012

I Have to write a procedure which takes XML data and inserts into some tables.

If the XML format is fixed then i can use extract function for parsing and can insert into the tables.

But the problem is there is not fixed format for the xml.

Are there any built in packages which takes the xml data for parsing..

View 3 Replies View Related

To Store Data In Materialized View

Apr 14, 2011

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:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 4 Replies View Related

SQL & PL/SQL :: Store Large XML Data In Blob?

May 8, 2010

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?

View 2 Replies View Related

SQL & PL/SQL :: Splitting Data And Store Into Columns?

Feb 28, 2013

I have string like 'PRASAD,ALLEN,STEWART,SMITH'.

LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH

I want to store the data into columns using SELECT statement only

View 7 Replies View Related

PL/SQL :: Column Wise Data Store

Feb 18, 2013

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.

View 1 Replies View Related

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]....

View 8 Replies View Related

Forms :: Read Data From Excel And Store It In Blob?

Aug 10, 2010

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.

View 8 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related

SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?

Apr 14, 2011

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:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 1 Replies View Related

SQL & PL/SQL :: Cursors To Update Data And Store It Back To Same Table

Sep 4, 2010

I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.

cursor c1
is
select distinct roles
from table1
where flag = '1';

cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop

here im updating the roles im picking to to a suffix and role.

update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.

for j in c2
loop

here im deleting all the roles that are not part of my comparing table.

delete from table1
where role = 'something';

But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.

View 12 Replies View Related

Forms :: Transaction Data Which Should Store Temporarily In Database?

Sep 17, 2013

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?

View 11 Replies View Related

Store Image And PDF Files In 10g?

Jan 5, 2011

i want to store image and pdf files in oracle 10g.

i success with blob variable.

what are the other methods.

View 2 Replies View Related

SQL & PL/SQL :: Hiding Store Procedure Possible?

Jan 11, 2012

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Store Xml In Blob From A Query

Jul 13, 2012

i want to store xml in database. i have following questions,

1) in which col should i keep xml .
2) right now i am keeping it in blob columns, how can i insert update a record in blob col from query, which can be run from worksheet.

I am using oracle 11g express edition.

View 2 Replies View Related

Forms :: How To Store PDF In Database

Mar 23, 2010

I want to store files in the database using oracle forms 6i. The file may be in the form of .pdf .

Its possible, if yes How to code in form.

View 2 Replies View Related

SQL & PL/SQL :: How To Store Value In Date Variable

Oct 17, 2012

i am having one query which is

select to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:ss')from dual;

when i am running this query i am getting this value: 01/01/1900 23:59:00 and this value i am inserting in one variable which has date datatype.here i am storing like this:

v_nshiftmax := to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:SS');

while storing getting error: not a valid month....how to store it in this variable?

View 3 Replies View Related

SQL & PL/SQL :: Right Way To Store IPV4 And MAC Address

Jan 26, 2012

The title for this new topic is self explanatory. Since PostgreSQL has data types for storing IPV4 and MAC address and Oracle don't, I would like to know the right way to store this values so we can do different operations and validations with these network address.

I been doing some research but I don't like the solutions like storing just number values whit out any validations like PostgreSQL.

View 4 Replies View Related

SQL & PL/SQL :: How To Store PDF Files In Oracle10g DB

Dec 2, 2011

how to store pdf files in oracle10g database

View 13 Replies View Related

SQL & PL/SQL :: FIFO Store Consumption

Apr 25, 2013

I need a query to divide the total consumption of an item into parts as it was stored in store on the basis of FIFO. The item that was stored first it will consumed first.

My sample data is given below.

DROP TABLE STORE_STOCK;
CREATE TABLE STORE_STOCK
(
no NUMBER(4),
vdate DATE,
code VARCHAR2(8),
clqty NUMBER(8,3),
dept_id NUMBER(4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]........

SQL>select * from store_stock
2 order by dept_id,vdate,no;

NO VDATE CODE CLQTY DEPT_ID
---------- --------- -------- ---------- ----------
359 21-JUL-12 012-042C 1050.1 19
2144 14-NOV-12 012-042C 990 19
1876 23-OCT-12 012-042C 1010 34
4006 12-MAR-13 012-043D 515.425 34
4086 15-MAR-13 012-043D 870 34
4495 13-APR-13 012-043D 498 34

6 rows selected.

SQL>select * from store_issue;

CODE IDATE DEPT_ID QTY
-------- --------- ---------- ----------
012-043D 24-APR-13 34 1650
012-042C 24-APR-13 19 990

Required Output will be

NO VDATE CODE CLQTY DEPT_ID ISS_QTY BAL_ISS
---------- --------- -------- ---------- ---------- ---------- ---------
359 21-JUL-12 012-042C 1050.1 19 990 0
2144 14-NOV-12 012-042C 990 19 0 0
1876 23-OCT-12 012-042C 1010 34 1010 640
4006 12-MAR-13 012-043D 515.425 34 515.425 124.575
4086 15-MAR-13 012-043D 870 34 124.575 0
4495 13-APR-13 012-043D 498 34 0 0

The total consumed against code '012-042C' is 990 for dept_id 19 and we have stored qty 1050.1 against goods receipt no 359 dated 12-jul-12. So have issued the whole qty against no 359 and balance is 0. For item code '012-043D' the total issuance is 1650 and we have issued it against 03 goods receipt nos.

View 5 Replies View Related

Store Procedure Insert / Update?

Dec 7, 2011

Store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.

View 2 Replies View Related

SQL & PL/SQL :: Store Image In Oracle 9i Table

Jan 3, 2012

I want to store Image file in Oracle table without storing it physically in the system. how can i store the image file into the oracle table and also retrieve it without storing it physically in the system at any path.

View 5 Replies View Related

SQL & PL/SQL :: How To Store Images In Table Column

Dec 21, 2011

How to store images in table column. I have table called emp with datatype empno,name,photo_id(long raw)
.
i want to store image in table for each record.

View 3 Replies View Related

SQL & PL/SQL :: How To Store A Large String In Oracle

Nov 20, 2009

how do i store large string in oracle.

View 19 Replies View Related

SQL & PL/SQL :: Store Date Time From CSV File?

Aug 6, 2013

I have a csv file where a field has values like "Fri Aug  2 19:22:02 EDT 2013". through unix i am able to place the value into an unix variable but the problem is this should be inserted into a date column in my database table. Basically, the relevant data to be extracted is "Aug 2 2013 19:22:02" and then i need to insert the same into my database column. And the date column in the table should displays like '08/02/2013 19:22:02'.

create table tst_var(a varchar2(50))
/
create table test_dt(a date)
/
insert into tst_var values('Fri Aug 2 19:22:02 EDT 2013')
/
commit;

View 4 Replies View Related

SQL & PL/SQL :: To Store The Date And Time In GMT Format

May 13, 2010

Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.

Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?

View 1 Replies View Related

SQL & PL/SQL :: How To Store Spanish Content In A Table

Jul 29, 2011

The requirement is to store "Spanish" content in a table. The input will be passed from the web page. What settings do i need to make in the database to store the Spanish content. Will a varchar2 accept Spanish text ? I need to retrieve back the content and send to web page based on request.

View 6 Replies View Related

SQL & PL/SQL :: How To Store Special Character In Database

Aug 19, 2010

i want to store special characters in database. i have tried following but not succeed. how to store and retrieve it. Herewith i'm posting my try.I want to store and retrieve following character.

SQL> select nchr(181) from dual;

N
-
µ

SQL> create table temp (uom varchar2(10));

Table created.

SQL> insert into temp values(nchr(181));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp;

UOM
----------
?

here i'm getting the '?' but i want 'µ'.

View 2 Replies View Related

Forms :: How To Store PDF File In Database

Dec 18, 2010

I want to store pdf file in table using forms 6i. Then i want to open and view it in Acrobat Reader. using Forms 6i only.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved