?
Solved

dual table problem

Posted on 2004-10-14
4
Medium Priority
?
2,196 Views
Last Modified: 2008-02-26
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
Comment
Question by:tabing16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 600 total points
ID: 12311202
did you actually execute the procedure?

run this again:

SQL>exec test_random;

then run this query 'select * from testing'
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12311323
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
 
LVL 7

Expert Comment

by:bvanderveen
ID: 12311391
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
 
LVL 1

Expert Comment

by:tangocoder
ID: 12313600
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question