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.
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?
(1.) We have an Oracle 10g DB.
(2.) We want to store PDFs/MS Word docs inside BLOB column in a table.
(3.) We need to show PDF/MS Word content to the user, without opening the PDFs/MS World like we normally do (i.e. Using PDV reader/ MS Word).
(4.) i.e. We need to use SQL statements and
(a.) Get all contents of the PDF/MS WORD doc.
(b.) Search for specific strings inside these docs in the BLOB columns.
I have a table where user can store images in clob format.Need to convert the same image to blob and store in a dummy table.
I tried using this
--function creation
CREATE OR REPLACE FUNCTION FN_CLOB_TO_BLOB(CLOB_IN IN CLOB) RETURN BLOB IS
POS PLS_INTEGER := 1;
BUFFER RAW(32767);
[code]...
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 RelatedMy client system is WinXP.I want to store a Word/Excel document to Oracle Blob column and choosed the command named "piecewise.exe",it seems a free tool on the web, to store the document to oracle,but occausionally failed.So I need alternative which is stable and available.
View 1 Replies View Relatedexplain the difference in numbers between the queries? I am acutally more concerned about the ETM_XML clob since the descrepancy appears to be bigger.
SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = 'ETM_RAW_XML';
ETM_RAW_XML
IFD_XML
SYS_LOB0007260522C00012$$ 87870668800
ETM_RAW_XML
ETM_XML
SYS_LOB0007260522C00011$$ 125199974400
SQL> SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
83848300852 ~ 78.0898154266179 GB
SELECT NVL((SUM(DBMS_LOB.GETLENGTH(IFD_XML))),0) AS BYTES FROM ETM_RAW_XML;
BYTES
----------------
61907953222 ~ 57.6562743838876 GB
I've below test case
CREATE TABLE MYMERCHANT
(
SRNONUMBER(5),
PARENTSRNONUMBER(5),
LEVELNONUMBER(5),
LEVELNAMEVARCHAR2(25)
) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 1, NULL, 101, 'HQ1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 2, 1, 102, 'DIV1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 3, 1, 103, 'DIV2' ) ;
[code]........
Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.
DESIRED OUTPUT
User can generate reports at two levels i.e. Head Quarter level and Division Level.
When user wants report at Head Quarter Level :
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
HQ1 DIV2 LOC3 600
Page 2 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
Page 3 (Should Display as below)
HQ1 DIV2 LOC3 600
When user wants report at Division Level : eg DIV1
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
I have a query like this -
SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;
When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.
Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.
I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.
View 3 Replies View Relatedi want to store image and pdf files in oracle 10g.
i success with blob variable.
what are the other methods.
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.
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.
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
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?
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.
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..
how to store pdf files in oracle10g database
View 13 Replies View RelatedI 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.
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 RelatedI 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?
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 RelatedHow 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.
how do i store large string in oracle.
View 19 Replies View RelatedI 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;
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?
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 Relatedi 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 'µ'.
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 RelatedI 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