Solved

dual table problem

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Where clause causing Invalid Number Error 4 45
Repeat query 13 61
Import and exporting Oracle Data with encrypted columns 4 48
Oracle encryption 12 48
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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

839 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