• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2200
  • Last Modified:

dual table problem

hi, i am a very novice oracle user and need a help. i currently want to generate 100 random strings by using dual table, this is my simple script :

drop table testing;
drop sequence testingseq;
create table testing(
      column1 number(4) constraint  testing_pk primary key,
      column2 varchar2(10)
);
create sequence testingseq;
create or replace procedure test_random is
ccolumn1 number(4);
ccolumn2 varchar2(8);
begin
      for i in 1..100
      loop
            select initcap(dbms_random.string('l',8))
            into ccolumn2 from dual;
            insert into testing
                  values(testingseq.nextval,ccolumn2);
      end loop;
end;
/

but when I tried to execute 'select * from testing', it says 'no rows selected', can anyone help me? how come my table has no data, i thought i would get 100 data after executing the script

0
tabing16
Asked:
tabing16
1 Solution
 
seazodiacCommented:
did you actually execute the procedure?

run this again:

SQL>exec test_random;

then run this query 'select * from testing'
0
 
Mark GeerlingsDatabase AdministratorCommented:
I am not a novice with PL\SQL, but I have to admit that I never use "for" loops, so I'm guessing that the problem is in the loop syntax.

I also noticed that you have both a "select" and an "insert" in the loop when I think you just need an insert.  Try this procedure:
create or replace procedure test_random (qty_to_do in number default 100) is
  v_done pls_integer default 0;
begin
   while v_done < qty_to_do loop
      values(testingseq.nextval,initcap(dbms_random.string('l',8)));
      v_done := v_done +1;
   end loop;
end;
/

There is no "commit" in this procedure or yours, so the resulting rows will only be visible in the same session that created them, unless you do an explicit "commit" after running the procedure.
0
 
bvanderveenCommented:
Your loop looks OK to me.

Make sure you execute the procedure, and commit after you are done.

You can put dbms_output statements in your procedure so you know what is happening:
   dbms_output.put_line('Beginning execution');

Then, when you execute, run this first:
   SQL>set serveroutput on
 
0
 
tangocoderCommented:
When you create a sequence in Oracle you have to initialize the sequence by first doing a select seq_name.currval from dual and then use a cursor to add sequence values with seq_name.nextval.  If you dont initialize the value it will never increment,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now