OraFAQ Forum: SQL & PL/SQL » Need sql script or proceedure

Home » SQL & PL/SQL » SQL & PL/SQL » Need sql script or proceedure (11G R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need sql script or proceedure [message #632137]

Mon, 26 January 2015 08:01

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi,

I will receive the log file as below ...

Sample file test.log
---------------------
--

--

--[1] tue jan

-- redolog

--redolog

--original rowid

--row not found

update "schema.table" set column=1;

--

--

--[2] tue jan

-- redolog

--redolog

--original rowid --

insert "schema.table" (column1) values ('test');

--

--[2] tue jan

-- redolog

--redolog

--original rowid

--row not found

delete schema.table where column='test';

------------------------------------------------------------------------------->end of the file ------------------------>

Requirement
----------->:Now i need to insert only insert/update/delete statements in a table ....
how can i achieve this (external table or any proceudre)

could you please help on this regard.

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632138 is a reply to message #632137]

Mon, 26 January 2015 08:04

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

I do not understand your post at all, Rajesh. Can you try again with a clearer statement of what you are trying to do and what the problem is?

Report message to a moderator

Re: Need sql script or proceedure [message #632139 is a reply to message #632137]

Mon, 26 January 2015 08:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

eliminate records that do not contain SQL statements.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

on which OS name & version does this file reside>

Report message to a moderator

Re: Need sql script or proceedure [message #632140 is a reply to message #632138]

Mon, 26 January 2015 08:07

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi john,

I need to load the sql statements only(update /insert /delete statements only from a file)...need to ignore the rest of the lines..
How can i achieve this??

sample log file as below
-----------------------
--

--

--[1] tue jan

-- redolog

--redolog

--original rowid

--row not found

update "schema.table" set column=1;

--

--

--[2] tue jan

-- redolog

--redolog

--original rowid --

insert "schema.table" (column1) values ('test');

--

--[2] tue jan

-- redolog

--redolog

--original rowid

--row not found

delete schema.table where column='test';

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632141 is a reply to message #632140]

Mon, 26 January 2015 08:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

root cause of problem & solution have NOTHING to do with any Oracle database.
It is just a simple text file manipulation that can be accomplished any number of different ways depending upon specifics local to OP.

Report message to a moderator

Re: Need sql script or proceedure [message #632142 is a reply to message #632140]

Mon, 26 January 2015 08:14

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just do it before:

cat file | egrep '^(insert|delete|update)'

Report message to a moderator

Re: Need sql script or proceedure [message #632143 is a reply to message #632142]

Mon, 26 January 2015 08:19

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Michel,

Thanks for your reply...I am not familiar with UNIX....
I need to insert sql statements only in a table from that file....
Could you please help on this issue...

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632144 is a reply to message #632143]

Mon, 26 January 2015 08:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Could you please help on this issue...
Does this mean you expect us to teach you *NIX now?

How else can we help you? PLEASE be specific.

Report message to a moderator

Re: Need sql script or proceedure [message #632146 is a reply to message #632144]

Mon, 26 January 2015 08:38

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Blackswan,

Sorry , actually i will receive the one sql log file,which will update continuosly with sql statements...Now i need to extract only those insert/update/delete sql statements and load into a table ..Each sql statement as a row...So i planned to prepare external table for this scenario..but here i need to skip the lines except insert/update/delete statements ..

Could you please help me to achive the solution?

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632150 is a reply to message #632146]

Mon, 26 January 2015 08:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>So i planned to prepare external table for this scenario.

Can you write SQL when you know neither the table name nor the column name?
Why should we do your job for you?
Do you know how to write basic SELECT statement? if so, show us how to obtain INSERT statement.

Report message to a moderator

Re: Need sql script or proceedure [message #632151 is a reply to message #632146]

Mon, 26 January 2015 08:52

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

What is wrong with the solution that Michel offered you?

Report message to a moderator

Re: Need sql script or proceedure [message #632153 is a reply to message #632151]

Mon, 26 January 2015 09:07

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Gaggag,

there is no wrong with Michael solution....

Actually i need to insert all the dml statements into a table from the log file....

So i created one external table for this issue...but i am facing problem while skipping the lines except insert/update/delete ..

How can i skip the rest of the lines while creating external table ,except dml statements?

[Sample file looks like below]

--

--

--[1] tue jan

-- redolog

--redolog

--original rowid

--row not found

update "schema.table" set column=1;

--

--

--[2] tue jan

-- redolog

--redolog

--original rowid --

insert "schema.table" (column1) values ('test');

--

--[2] tue jan

-- redolog

--redolog

--original rowid

--row not found

delete schema.table where column='test';

--

--

--[1] tue jan

-- redolog

--redolog

--original rowid

--row not found

update "schema.table" set column=1;

--

--

--[2] tue jan

-- redolog

--redolog

--original rowid --

insert "schema.table" (column1) values ('test');

--

--[2] tue jan

-- redolog

--redolog

--original rowid

--row not found

delete schema.table where column='test';

[end of the sample file]

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632154 is a reply to message #632153]

Mon, 26 January 2015 09:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can i skip the rest of the lines while creating external table ,except dml statements?

just do not SELECT them by using simple WHERE clause

Report message to a moderator

Re: Need sql script or proceedure [message #632155 is a reply to message #632153]

Mon, 26 January 2015 09:15

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Like Blackswan said, use a WHERE clause. Hint: also look into "NOT IN (...)"

Report message to a moderator

Re: Need sql script or proceedure [message #632159 is a reply to message #632154]

Mon, 26 January 2015 09:26

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Blackswan,

I tried to create a external table as below,but i don't know how can i skip the non dml lines...

CREATE OR REPLACE DIRECTORY exec_dir AS '/bin';

CREATE TABLE log_sql (
sqlst long)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE

)
LOCATION ('sqlfile.log')
);

in the above external table creation,where can i skip the lines...could you please help me?

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632160 is a reply to message #632159]

Mon, 26 January 2015 09:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You don't exclude them from the external table.
You exclude them when selecting data from the external table.

Report message to a moderator

Re: Need sql script or proceedure [message #632162 is a reply to message #632160]

Mon, 26 January 2015 09:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

LONG Data Type

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

Report message to a moderator

Re: Need sql script or proceedure [message #632164 is a reply to message #632162]

Mon, 26 January 2015 09:39

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi blackswan,

Surely i will change the column datatype, but here how can i skip the lines...is there any way while creating the external table only?

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632165 is a reply to message #632164]

Mon, 26 January 2015 09:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

mvrkr44 wrote on Mon, 26 January 2015 07:39

Hi blackswan,

Surely i will change the column datatype, but here how can i skip the lines...is there any way while creating the external table only?

Regards,
Rajesh

http://www.orafaq.com/forum/mv/msg/196190/632160/#msg_632160

Report message to a moderator

Re: Need sql script or proceedure [message #632167 is a reply to message #632164]

Mon, 26 January 2015 10:04

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

but here how can i skip the lines..

When you select from the external table.

Report message to a moderator

Re: Need sql script or proceedure [message #632168 is a reply to message #632164]

Mon, 26 January 2015 10:06

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

use a WHERE clause. Hint: also look into "NOT IN (...)"

Report message to a moderator

Re: Need sql script or proceedure [message #632169 is a reply to message #632167]

Mon, 26 January 2015 10:07

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Michael,

I thought that is not good option...is there any other solution to achive the same..

Regards,
Rajesh

Report message to a moderator

Re: Need sql script or proceedure [message #632170 is a reply to message #632169]

Mon, 26 January 2015 10:12

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

If you want to process it at O/S level before loading into the database, what is wrong with Michel's earlier solution?

cat file | egrep '^(insert|delete|update) > sql_file'

[Edit: typo]

[Updated on: Mon, 26 January 2015 10:13]

Report message to a moderator

Re: Need sql script or proceedure [message #632171 is a reply to message #632169]

Mon, 26 January 2015 10:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

mvrkr44 wrote on Mon, 26 January 2015 16:07

I thought that is not good option

Seems like a perfectly good option to us, what do you think the problem with it is?

Report message to a moderator

Previous Topic:	variable declaration in procedure if we are calling the procedure within itself
Next Topic:	Group by clause doubt - Can you please suggest me the query to this problem???

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:40:34 CDT 2024