Solved

dual table problem

Posted on 2004-10-14
4
2,188 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 150 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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 …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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