interact command line select to files in this ? [message #621835] |
Mon, 18 August 2014 22:56 |
|
navaminroad
Messages: 15 Registered: August 2014 Location: Thai
|
Junior Member |
|
|
i saw information on
http://www.orafaq.com/forum/t/162827/
when i implemented this information in my job, It's well done!
i found many Out Put (10,080 rows selected.)
i'm used to Compiler Source code (FUNCTION your_func) with Oracle SqlPlus.
Select to files from the command line itterface ?
so, now i like some further information form you about Data management
-How i can queuing them?
-How to make them show some information that I choose.
please tell me
i look forward to hearing from you.
Yours sincerely,
Navamin-Thailand
navaminroad@hotmail.com
|
|
|
|
|
|
|
|
|
|
Re: interact command line select to files in this ? [message #621955 is a reply to message #621949] |
Wed, 20 August 2014 01:38 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you want to use the function in a SQL select statement from SQL*Plus and be able to restrict the results in the select statement, then I would use a pipelined function instead, as demonstrated below.
SCOTT@orcl12c> CREATE OR REPLACE FUNCTION my_func
2 (p_digits IN VARCHAR2)
3 RETURN SYS.ODCIVARCHAR2LIST PIPELINED
4 AS
5 BEGIN
6 FOR v_rec IN
7 (SELECT DISTINCT REPLACE (SYS_CONNECT_BY_PATH (digit, '*'), '*', '') digits
8 FROM (SELECT LEVEL i, SUBSTR (p_digits, LEVEL, 1) digit
9 FROM DUAL
10 CONNECT BY LEVEL <= LENGTH (p_digits))
11 WHERE LEVEL = LENGTH (p_digits)
12 CONNECT BY NOCYCLE PRIOR i != i
13 ORDER BY digits)
14 LOOP
15 PIPE ROW (v_rec.digits);
16 END LOOP;
17 END my_func;
18 /
Function created.
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('12342387'))
3 WHERE SUBSTR (COLUMN_VALUE, 1, 1) = '2'
4 AND SUBSTR (COLUMN_VALUE, 4, 1) = '8'
5 AND SUBSTR (COLUMN_VALUE, 8, 1) = '3'
6 /
COLUMN_VALUE
--------------------------------------------------------------------------------
21283473
21283743
21284373
21284733
21287343
21287433
21382473
21382743
21384273
21384723
21387243
21387423
21482373
21482733
21483273
21483723
21487233
21487323
21782343
21782433
21783243
21783423
21784233
21784323
22183473
22183743
22184373
22184733
22187343
22187433
22381473
22381743
22384173
22384713
22387143
22387413
22481373
22481733
22483173
22483713
22487133
22487313
22781343
22781433
22783143
22783413
22784133
22784313
23182473
23182743
23184273
23184723
23187243
23187423
23281473
23281743
23284173
23284713
23287143
23287413
23481273
23481723
23482173
23482713
23487123
23487213
23781243
23781423
23782143
23782413
23784123
23784213
24182373
24182733
24183273
24183723
24187233
24187323
24281373
24281733
24283173
24283713
24287133
24287313
24381273
24381723
24382173
24382713
24387123
24387213
24781233
24781323
24782133
24782313
24783123
24783213
27182343
27182433
27183243
27183423
27184233
27184323
27281343
27281433
27283143
27283413
27284133
27284313
27381243
27381423
27382143
27382413
27384123
27384213
27481233
27481323
27482133
27482313
27483123
27483213
120 rows selected.
|
|
|
Re: interact command line select to files in this ? [message #621958 is a reply to message #621955] |
Wed, 20 August 2014 01:56 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You could also use regexp_like instead of substr, as shown below.
SCOTT@orcl12c> CREATE OR REPLACE FUNCTION my_func
2 (p_digits IN VARCHAR2)
3 RETURN SYS.ODCIVARCHAR2LIST PIPELINED
4 AS
5 BEGIN
6 FOR v_rec IN
7 (SELECT DISTINCT REPLACE (SYS_CONNECT_BY_PATH (digit, '*'), '*', '') digits
8 FROM (SELECT LEVEL i, SUBSTR (p_digits, LEVEL, 1) digit
9 FROM DUAL
10 CONNECT BY LEVEL <= LENGTH (p_digits))
11 WHERE LEVEL = LENGTH (p_digits)
12 CONNECT BY NOCYCLE PRIOR i != i
13 ORDER BY digits)
14 LOOP
15 PIPE ROW (v_rec.digits);
16 END LOOP;
17 END my_func;
18 /
Function created.
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('12342387'))
3 WHERE REGEXP_LIKE (COLUMN_VALUE, '^2\d{2}8\d{3}3\d*')
4 /
COLUMN_VALUE
--------------------------------------------------------------------------------
21283473
21283743
21284373
21284733
21287343
21287433
21382473
21382743
21384273
21384723
21387243
21387423
21482373
21482733
21483273
21483723
21487233
21487323
21782343
21782433
21783243
21783423
21784233
21784323
22183473
22183743
22184373
22184733
22187343
22187433
22381473
22381743
22384173
22384713
22387143
22387413
22481373
22481733
22483173
22483713
22487133
22487313
22781343
22781433
22783143
22783413
22784133
22784313
23182473
23182743
23184273
23184723
23187243
23187423
23281473
23281743
23284173
23284713
23287143
23287413
23481273
23481723
23482173
23482713
23487123
23487213
23781243
23781423
23782143
23782413
23784123
23784213
24182373
24182733
24183273
24183723
24187233
24187323
24281373
24281733
24283173
24283713
24287133
24287313
24381273
24381723
24382173
24382713
24387123
24387213
24781233
24781323
24782133
24782313
24783123
24783213
27182343
27182433
27183243
27183423
27184233
27184323
27281343
27281433
27283143
27283413
27284133
27284313
27381243
27381423
27382143
27382413
27384123
27384213
27481233
27481323
27482133
27482313
27483123
27483213
120 rows selected.
|
|
|
|
|
|
|
Re: interact command line select to files in this ? [message #622090 is a reply to message #622085] |
Thu, 21 August 2014 00:00 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
navaminroad wrote on Thu, 21 August 2014 09:19If I want to display from 8 columns to display only 5 columns.
example
21283473
I want the results that display
21283
You are confused between digits and columns. Your question should have been, how to get the first 5 digits.
|
|
|
|
Re: interact command line select to files in this ? [message #622232 is a reply to message #622231] |
Fri, 22 August 2014 21:11 |
|
navaminroad
Messages: 15 Registered: August 2014 Location: Thai
|
Junior Member |
|
|
How do I Design (Database Management System) (Data Store) (Create Table) to store the Results and will be used in the calculation, search, find out Report?
To Run the Out Put (my_func) came out very long time. How can I Save file or use Temp File.
To insert into the Table A, B, C ... N under such conditions.
Table A collect all the Rows where first position is No. 1.
Table B collect all the Rows where first position is No. 2.
Table C collect all the Rows where first position is No. 3.
... truncated to save space
Table N collect all the Rows where first position is No. 0.
and how to reduce the size of the data.
please tell me.
i look forward to hearing from you.
Yours sincerely,
Thanks with best regards,
Navamin-Thailand.
navaminroad@gmail.com
**********************
Results all the Rows.
16494488379128759841194311665590386511882406836298391400708734708401382919636
46808466148656674590229646503558677146520742916825967268314373975466950565603
55540746705637562240501155776567564785022522219704232111595011221458008894065
76207005305754043815320793873732776711260530318905387139904743632738725295644
839894665068189311105518354942178373831442020424406884197134050000000 rows selected.
**********************
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('111111111111111111111111111111112222222222222222222222222222222233333333333
33333333333333333333344444444444444444444444444444444555555555555555555555555
55555555666666666666666666666666666666667777777777777777777777777777777788888
88888888888888888888888888899999999999999999999999999999999000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000'))
3 WHERE SUBSTR (COLUMN_VALUE, 1, 1) = '1'
4 /
----------------------
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('111111111111111111111111111111112222222222222222222222222222222233333333333
33333333333333333333344444444444444444444444444444444555555555555555555555555
55555555666666666666666666666666666666667777777777777777777777777777777788888
88888888888888888888888888899999999999999999999999999999999000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000'))
3 WHERE SUBSTR (COLUMN_VALUE, 1, 1) = '2'
4 /
---------------------------
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('111111111111111111111111111111112222222222222222222222222222222233333333333
33333333333333333333344444444444444444444444444444444555555555555555555555555
55555555666666666666666666666666666666667777777777777777777777777777777788888
88888888888888888888888888899999999999999999999999999999999000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000'))
3 WHERE SUBSTR (COLUMN_VALUE, 1, 1) = '3'
4 /
--------------------
... truncated to save space
---------------------
SCOTT@orcl12c> SELECT *
2 FROM TABLE (my_func ('111111111111111111111111111111112222222222222222222222222222222233333333333
33333333333333333333344444444444444444444444444444444555555555555555555555555
55555555666666666666666666666666666666667777777777777777777777777777777788888
88888888888888888888888888899999999999999999999999999999999000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000'))
3 WHERE SUBSTR (COLUMN_VALUE, 1, 1) = '0'
4 /
----------------------------
|
|
|
|
|
Re: interact command line select to files in this ? [message #622242 is a reply to message #622084] |
Sat, 23 August 2014 08:36 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Wed, 20 August 2014 20:41Sometimes we get so focused on the fancy new methods that we forget about the simple old ones.
In this particular case - we are looking for string that starts with certain character - it is not just "simple old one" but is also more efficient one since it supports index range scan, assuming column is indexed.
SY.
|
|
|
|
Re: interact command line select to files in this ? [message #622495 is a reply to message #622492] |
Wed, 27 August 2014 02:57 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
There were two other methods also mentioned :
LF suggested,
SQL> WITH data
2 AS (SELECT '122333444455555' COL
3 FROM dual)
4 SELECT col
5 FROM data
6 WHERE Substr(col, 1, 1) = '1'
7 AND Substr(col, 2, 1) = '2'
8 AND Substr(col, 4, 1) = '3'
9 AND Substr(col, 7, 1) = '4';
COL
---------------
122333444455555
Bill suggested,
SQL> WITH data
2 AS (SELECT '122333444455555' COL
3 FROM dual)
4 SELECT col
5 FROM data
6 WHERE col LIKE '12_3__4%';
COL
---------------
122333444455555
I would go with Bill's suggestion considering the performance point of view as SY already explained.
Regards,
Lalit
|
|
|
|
|
|
Re: interact command line select to files in this ? [message #622808 is a reply to message #622807] |
Sat, 30 August 2014 06:24 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
navaminroad wrote on Sat, 30 August 2014 16:09Will give GPU processing needs to be done to it.
Because CPU processing for a long time.
Sorry, I don't understand. Are you trying to say that you are facing performance issue?
Edit : Show what you did. I re-read your previous post about the query running too long. Can you post the execution plan for the above query using substr. Also, in sqlplus, set time on timing on; and then execute the query and post the results using copy paste. You might need to remove the entire resultset.
[Updated on: Sat, 30 August 2014 08:39] Report message to a moderator
|
|
|
|
|
|
Re: interact command line select to files in this ? [message #622819 is a reply to message #622816] |
Sun, 31 August 2014 02:42 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
I have no idea what your intentions are with above three posts. This discussion is now going directionless. Your original topic(which I still don't understand) was releated to finding values based on the position of the digits. Then you asked how to get only the first 5 digits, to which you didn't feedback. Now you talk about "CUDA", which I don't understand how is it related to Oracle. This is my opinion.
|
|
|
|
Re: interact command line select to files in this ? [message #622943 is a reply to message #622941] |
Tue, 02 September 2014 06:27 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
navaminroad wrote on Tue, 02 September 2014 16:46Dear Lalit Kumar B,
I have an intense number crunching application which I need converted into
CUDA to maximize speed. I will also need you to add a couple of features to
the program. I need someone with years of CUDA experience.
Sorry, but I can't help you with this "CUDA". This is an "Oracle" forum. You need to post in some "CUDA" forum.
|
|
|
|
Re: interact command line select to files in this ? [message #623223 is a reply to message #623064] |
Sat, 06 September 2014 06:37 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
navaminroad wrote on Thu, 04 September 2014 00:26Because came out very long time and Big Data.
You asked for assistance with generation of digit permutations for
'111111111111111111111111111111112222222222222222222222222222222233333333333
33333333333333333333344444444444444444444444444444444555555555555555555555555
55555555666666666666666666666666666666667777777777777777777777777777777788888
88888888888888888888888888899999999999999999999999999999999000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000'
This is 416 digit number. Do you know that number of permutations for N of N is N! (N factorial)? And do you realize what 416! is?
It is 1 * 2 * 3 * 4 *... * 416 which is infinity for all computers, well maybe except some supercomputers.
SY.
[Updated on: Sat, 06 September 2014 06:39] Report message to a moderator
|
|
|
|
|
Re: interact command line select to files in this ? [message #623662 is a reply to message #623621] |
Fri, 12 September 2014 06:50 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
navaminroad wrote on Fri, 12 September 2014 01:06What can I do to make the program. (Source code FUNCTION my_func.) As multi-threading (parallel programming)
It looks like you didn't get it. So I'll repeat again. Number of permutaions for 416 digit number is 416!. And 416! is 3846313387719957490284353898010600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000000000000000000000000000000000. How many parallel threads your computer can handle?
SY.
|
|
|
|