Solved

dual table problem

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Generate HTML charts from SQL 4 51
Using field values to generate columns - oracle query 6 47
oracle query help 18 78
sql query 9 21
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now