Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 02, 2016 - 5:00 am UTC

Last updated: September 06, 2016 - 2:13 am UTC

Version: 11.0

Viewed 1000+ times

You Asked

1)If I created a procedure in trigger after that i modify the procedure. my question is the trigger is working fine or procedure changes, effected trigger also?..

2)There is one table.

table1(inpt)...........target(output)...

1 0000000001
12 0000000012
123 0000000123
1234 0000001234
12345
123456

I have already asked this question a few days back.You gave me an answer like.

Select colname,to_char(123,'fm0000000000000') from tbl.

But we can solve with lpad also...like...Select lpad(col,10,0) from table.

Note:--what is the major diff of both the codes.

and Connor said...

Minimal difference. The to_char might be a tiny bit faster because the lpad is actually TWO operations (1) convert the number to string, (2) pad it out.


SQL> set timing on
SQL> declare
  2    x varchar2(20);
  3  begin
  4  for i in 1 .. 10000000 loop
  5    x := to_char(i,'fm0000000000');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.98
SQL>
SQL>
SQL> declare
  2    x varchar2(20);
  3  begin
  4  for i in 1 .. 10000000 loop
  5    x := lpad(i,10,'0');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.15


but it's hardly noticeable

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But it was other way for us

Rajeshwaran Jeyabal, September 02, 2016 - 7:25 am UTC

********************************************************************************

declare
     x varchar2(20);
   begin
   for i in 1 .. 10000000 loop
     x := to_char(i,'fm0000000000');
   end loop;
   end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.13          0          0          0           0
Execute      1      4.16       4.15          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.16       4.28          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

declare
      x varchar2(20);
    begin
    for i in 1 .. 10000000 loop
      x := lpad(i,10,'0');
    end loop;
    end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          0          0          0           0
Execute      1      3.72       3.73          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.72       3.81          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Connor McDonald
September 06, 2016 - 2:13 am UTC

All sorts of potential dependencies there. Other activities, platform, version, type of CPU etc.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library