sql functions to trim absolute path to display just filename


You can do it this way in perl script.



#!/usr/bin/perl

################################################################################
# Author: Malay Biswal
# Date : 2009-08-25
#
###################
use strict;


use constant LOOKUP => "D:/perl/perl_data/file.txt";
my $line;my@x;my $x;my $len;my $len;my $name;

MAIN: {

open(IN, "
Posted On: Thursday 15th of November 2012 10:44:01 PM Total Views:  487
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Script to find size of Procedures, functions...

Hi How can we find the size (In MB) of a Proecudure or Function Is anybody having Script for that
VIEWS ON THIS POST

195

Posted on:

Wednesday 17th October 2012
View Replies!

oracle pl/sql procedures & functions

, i have 3 doubts regarding pl/sql procedures. i will be ever greatful to you if you clarify my doubts . -------------------------------------------------------------------- First 1 is: How can I find charecters presented in a DNAsequence (column name) inserted in a table(varchar2 or long )(A,T's) using PL/SQL sub programme i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data) In this sequence to find out how many A,G,T,Cs are present. ---------------------------------------------------------------------- Second 1 is: how can i find the size of a DNA sequence, inserted in a table, using PL/SQL function (without using Length() function) i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data) In this sequence to find out total length of the sequence without using the Length() function. ---------------------------------------------------------------------- Thrid 1 is: How can i find total number of charecters in a DNA sequence, inserted in a table, using PL/SQL Trigger i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data). In this sequence to find out the total number of characters(A,G,T,Cs) are present or number of characters in the sequence. --------------------------------------------------------------------- please clarify my doubts . waiting for yur reply...
VIEWS ON THIS POST

202

Posted on:

Wednesday 17th October 2012
View Replies!

info about stored procedures and functions

Hi I have a few questions on Stored procedures and functions. From which view in Data Dictionary I can see the details of procedure and function and Packages To modify a procedure which method is better; storing code of procedure in a sql file and at the time of appying a change modify the code and run that SQL file OR writing every time a method with code ALTER PROCEDURE Amol
VIEWS ON THIS POST

373

Posted on:

Friday 19th October 2012
View Replies!

How to get rid of trailing characters (trim doesn't work)

, Could someone help me out with this one: I have several strings which lookk like this: P830201001 Q450302001 L659832001 In my sql statement, I need to compare only the 7th character of this string. How do I get rid of the 6 initial ones and 3 trailing ones
VIEWS ON THIS POST

289

Posted on:

Friday 19th October 2012
View Replies!

How to export only Procedure/functions?

How to export only Procedure/functions
VIEWS ON THIS POST

215

Posted on:

Friday 19th October 2012
View Replies!

How to pass an Array to the functions or Stored procedures

Hi all Can we pass an array definition in the parameters list of a function or procedures. For e.g. Create or replace function tem_1 () begin for i in .. loop end loop; This is required for Java programmers who are calling different procedures where the no. of parameters are fixed from Java programs, but now they want that the list of parameters should not be fixed and they should be able to pass as many parameters with datatypes. I havent found any such facility in oracle. Can you all help me out in this
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

subselect vs. no subselect sql with analytical functions

The optimizer in the explain plan's cost and rows are the same for the two following queries however one is efficient (a) and the one using a subselect is extremely slow (b). Any ideas why this would occur, it seems like it's trying to satisfy the subselect before filtering it with a literal. (a) select * from ( select a.a, b.b, a.c, rank () over (partition by b.b order by a.c desc) from a, b where a.x=b.x and a.x = 555) (b) select * from ( select a.a, b.b, a.c, rank () over (partition by b.b order by a.c desc) from a, b where a.x=b.x) where x = 555 , Your first query would be the way to go. The basic rule is to trim your result set as early as possible. In order to do that you need to have a predicate with pretty good selectivity. BTW, what is the purpose of using rank() function Do you have another query wrapped around this one So far it does not serve any obvious purpose. Explain plan for two queries you have should have different figures for rows and bytes on intermediate steps given your statistics is up to date and no extreme values set for instance wide parameter (optimizer_*). Hope you are not using RBO (I don't think it is possible with analytics) :-) Have fun, Sergey
VIEWS ON THIS POST

157

Posted on:

Thursday 1st November 2012
View Replies!

oracle pl/sql procedures & functions

, i have 3 doubts regarding pl/sql procedures. i will be ever greatful to you if you clarify my doubts . -------------------------------------------------------------------- First 1 is: How can I find charecters presented in a DNAsequence (column name) inserted in a table(varchar2 or long )(A,T's) using PL/SQL sub programme i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data) In this sequence to find out how many A,G,T,Cs are present. ---------------------------------------------------------------------- Second 1 is: how can i find the size of a DNA sequence, inserted in a table, using PL/SQL function (without using Length() function) i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data) In this sequence to find out total length of the sequence without using the Length() function. ---------------------------------------------------------------------- Thrid 1 is: How can i find total number of charecters in a DNA sequence, inserted in a table, using PL/SQL Trigger i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data). In this sequence to find out the total number of characters(A,G,T,Cs) are present or number of characters in the sequence. --------------------------------------------------------------------- please clarify my doubts . waiting for yur reply...
VIEWS ON THIS POST

229

Posted on:

Friday 2nd November 2012
View Replies!

Taking oracle dump without procedures and functions

Hi all, I want to take the dump of a schema. but i need only the tables and the data. I don't want to export procedures, functions or packages. Is there any ways to export a schema like that. Please help. regards Saneesh
VIEWS ON THIS POST

203

Posted on:

Friday 2nd November 2012
View Replies!

need sql server equilent functions in oracle 11g

Hi , i am newbie to oracle, i need to convert my sql server statement to oracle which has so many function, pls help me in this query, here is sql query.. i want it in oracle.... pls help select * from (select distinct s.ID as ID,u.ID as uploadid,s.FilenamePath as UploadedFilePath,substring(s.FilenamePath, Len(s.filenamepath)-CHARINDEX('\\',REVERSE(s.FilenamePath))+2, CHARINDEX('\\',REVERSE(s.FilenamePath))) as UploadedFileName,Convert(varchar(20), u.UploadedDate, 103) as UploadedDate,(STUFF(stuff(s.doctype,len(s.doctype),2, reverse(right(s.doctype,2))), LEN(s.DocType)+1, LEN(s.FilenamePath),substring(s.FilenamePath, Len(s.filenamepath)-CHARINDEX('',REVERSE(s.FilenamePath))+1, CHARINDEX('',REVERSE(s.FilenamePath))))) as FilePath from Scanned_Files_Table s,Uploaded_Files_Table u,Index_Parameters_Table i,Parameters_Table p where s.ID=u.ScannedFileId and u.ID=i.Uploadedfileid and i.Parameterid=p.ID and u.DeleteFlag=0 and s.DocType='' ) m where uploadedfilename LIKE '%%' order by CONVERT(smalldatetime,uploadedDate,103) DESC
VIEWS ON THIS POST

227

Posted on:

Friday 2nd November 2012
View Replies!

trim off the write space

Hi all, I have a question on the trimming off the extra white space. SET LINESIZE 300 f1 f2 f3 aaaaa,bbbb,ccccc ddddd,eeee,fffff SELECT f1||chr(13),f2||chr(13),f3 FROM aaaaa, bbbbb, ccccc < THERE are SOME big white space here > ddddd, eeeee, fffff < There are Some big white space here > What is the best way to remove off the big white space Thank You
VIEWS ON THIS POST

158

Posted on:

Friday 2nd November 2012
View Replies!

Accessing oracle user functions with Asp.NET

I am accessing functions similar to procedures and passing parameters. The out put parameters are declared and passed with in to. But I am getting an error saying that their is no data. When I run the function on Sql worksheet, I get the results... Is oracle functions supported with OracleClient Any Ideas what could be wrong.
VIEWS ON THIS POST

322

Posted on:

Friday 2nd November 2012
View Replies!

any functions to change a text field from all upper case to Upper and lower case

I have fields that are all upper case, and I want the it converted to a proper case. Like the first lettet upper and the rest in lower case. any thoughts bev
VIEWS ON THIS POST

195

Posted on:

Friday 2nd November 2012
View Replies!

trimming white space & blanks

I am loading data through sqlldr direct=true. My data fileds are terminated by some control character like '^^'. I want both leading & trailing blanks removed in character fields. Is it possible through control file If yes, how By default, sqlldr does not do. kailash pareek
VIEWS ON THIS POST

156

Posted on:

Wednesday 7th November 2012
View Replies!

sql functions

Hi Friends, I have student-id primary key on a student table, with the format YYYYMMDD-999. Example 20040702-999. It is in varchar2 datatype coz of the inclusion of the "-". How can i write a sqlplus program that add 1 to the sequence 999 and make it 1000. or 20040702-1000
VIEWS ON THIS POST

292

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle to_date and SYSDATE functions

I am having a problem with trying for format the SYSDATE result and place it into a DATE field. Here is my table: CREATE TABLE REC_COUNT ( REC_ID NUMBER(10,0) PRIMARY KEY, REC_DATE DATE NOT NULL, REC_NAME VARCHAR2(255) NOT NULL ) The format I want the date to be in is MM/DD/YYYY HH:MI A.M., which translates to something like: 3/26/2009 9:00 A.M. Here is the SQL I am trying to run, but is returning an ORA-01843: not a valid month error: INSERT INTO rec_count VALUES (:recId, to_date(SYSDATE, 'MM/DD/YYYY HH:MI A.M.'), :recName) Any idea on why it is returning an invalid month error and how to fix it
VIEWS ON THIS POST

197

Posted on:

Wednesday 7th November 2012
View Replies!

query trim

Hi I\twant to trim the item number example i have item no example '602-14314w-std' ,'602-564/w-020' but i got one correct that is i got 14314w for the '602-14314w-std' i got wroing for one item that is i got 564/w- but i need to get 564/w please guide me very urgent 602-14314w-std i need 14314w only 602-564/w-020 i need 564/w only below i am trying the queryt but i am not get right SELECT SUBSTR(mtl.SEGMENT1,INSTR(mtl.SEGMENT1,'-')+1, INSTR(mtl.SEGMENT1,'-')+ 3) as segment1 from apps.ja_in_so_picking_lines jispl, apps.mtl_system_items_b mtl where jispl.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID and mtl.ORGANIZATION_ID = 104 and mtl.segment1 like '6%' and substr(mtl.segment1,3,1)='2' and jispl.ORGANIZATION_ID in ('128','112') and jispl.EXCISE_INVOICE_NO is not null
VIEWS ON THIS POST

187

Posted on:

Wednesday 7th November 2012
View Replies!

creating Oracle s.p, triggers ,functions or table in c#

, i got an requirement where the user inputs the oracle stored procedures,triggers,functions,tables,views or packages and when they press the execute button they should be execute or else if there's an error in the scripts while being debugged they must be roll backed .had any one worked on such stuff before
VIEWS ON THIS POST

248

Posted on:

Wednesday 7th November 2012
View Replies!

Calling oracle functions with a return value

Can c# call oracle functions I can call stored proecedures easily enough but for some reason I cannot call a function Lil help
VIEWS ON THIS POST

303

Posted on:

Wednesday 7th November 2012
View Replies!

sqlldr-conv path!

Does sqlldr's conv path load data below the HWM Do not use it often and remember that the direct path loads above the HWM.
VIEWS ON THIS POST

293

Posted on:

Thursday 15th November 2012
View Replies!