This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 15, 2012 3:06 AM by orafad RSS

Problem with SQL table

968167 Newbie
Currently Being Moderated
Hello,

My house mates and me are database development students, we started studying Oracle Database Express Edition. We both used same pen drive to install oracle and to transfer the tutorial sql table to C:\oraclexe\app\oracle\product\11.2.0\server\bin . When I use the command "select * from table;" im getting "no rows selected" .
With the same file and same command, the table is displayed on my housemate`s computer. Why it doesnt work on me ? The only difference is that im on XP, he is on win7.
  • 1. Re: Problem with SQL table
    AlbertoFaenza Expert
    Currently Being Moderated
    965164 wrote:
    Hello,

    My house mates and me are database development students, we started studying Oracle Database Express Edition. We both used same pen drive to install oracle and to transfer the tutorial sql table to C:\oraclexe\app\oracle\product\11.2.0\server\bin . When I use the command "select * from table;" im getting "no rows selected" .
    With the same file and same command, the table is displayed on my housemate`s computer. Why it doesnt work on me ? The only difference is that im on XP, he is on win7.
    Hi,

    the information you provided are not enough to make a correct analyis.

    I assume that when you say:
    SELECT * FROM table;
    table in this case is one of your tutorial tables.

    It looks that table or tables have been created but the command to insert data into the table have not been run or transaction have not been committed.
    Please explain how did you install your tutorial tables. If all tables are existing but empty you can try to run again the SQL which is inserting data into these tables.

    I'm not familiar with 10g XE. Are tutorial tables created and filled as part of the installation process or are they created with a separate script?

    Regards.
    Al
  • 2. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    965164 wrote:
    to transfer the tutorial sql table
    What tables?

    What does "the tutorial" refer to exactly? Url?

    Between "transfer", I assumed you copied some file, to server\bin and the select * from table there's a whole lot missing. Question is what.

    With the same file and same command,
    What file and format? What command? How was the file loaded and to what database schema?

    Perhaps you simply logged in as a different or forgot to use a schema identifier, e.g. select * from myschema.mytable.
  • 3. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    the table was provided in tutorial files - demo.sql . As tutorial says, I placed the file in server\bin.

    so I write start table.

    Then in SQL after Im connected, i write start demo.sql

    then SELECT * FROM table;

    says "no rows selected"

    Table is one of the rows provided.

    My housemate is doing exactly the same on his laptop and it works. Where is the problem ?

    Edited by: 965164 on Nov 4, 2012 11:25 AM
  • 4. Re: Problem with SQL table
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    I don't have 10g XE and could not find anything on the web about this demo.sql script file.

    What is this script doing?
    How did you run the script?
    Which user did you use to connect to sqlplus?
    I guess the script is simply creating a schema, tables and insert some data into these tables.
    Did you get any error while executing the script with SQLPLus?
    Please post the whole content of demo.sql

    Additionally specify:
    SELECT * from table;
    What are you trying to put instead of table? What's the real table name?

    As I said before if you get the errors no rows selected it seems that table has been created but no data have been inserted. Since we don't know which table you are selecting we cannot even know if you are selecting from a system table or from a table expected to be created by demo.sql.

    I don't think your problem is related to XP vs win 7. 10g XE should not have any problem with XP.

    Regards.
    Al
  • 5. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    here is the example file :

    http://www.cs.utexas.edu/~cannata/dbms/Downloads/demobld.sql


    after im connected i write :

    start demobld.sql ;

    then

    select * from emp ;

    and it says "no rows selected"

    with the same file, it works on my housemate's laptop. Whats the problem ?
  • 6. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    Again, what user runs that sql?

    How was the user created?

    What tutorial are you following and why?


    Comment out the line with "SET TERMOUT OFF" and re-run the changed script. Post full copy-paste of result.
  • 7. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    What do you mean by what user runs the sql?
    I just instaled Oracle XE and set my username and password in Getting Started page


    Before I type SET TERMOUT OFF in the sql, it looks like that :

    SQL> start demobld.sql;
    Building demonstration tables. Please wait.
    Demonstration table build is complete.
    SQL> select * from emp;

    no rows selected


    After I wrote Termout OFF :

    SQL> start demobld.sql;
    Building demonstration tables. Please wait.

    SQL> select * from emp;

    no rows selected



    It doesnt show me as before "Demonstration table build is complete."
  • 8. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    965164 wrote:
    What do you mean by what user runs the sql?
    Before start command, type in show user.

    Before I type SET TERMOUT OFF in the sql, it looks like that :

    SQL> start demobld.sql;
    Before that, did you open the demobld.sql file in a text editor and comment out the line with set termout off? It is probably the line directly after "PROMPT Building demonstration tables. Please wait.".
  • 9. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    the username is my personal name. I created the account via "Getting started" page..


    Even with "set term off" written in demobld with a text editor, I still cant select rolls. It still says "no rows selected"
  • 10. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    It's the other way around.

    Please carefully read, understand and follow what I wrote earlier:

    "Comment out the line with "SET TERMOUT OFF" and re-run the changed script. Post full copy-paste of result."

    Comment out, meaning prepend line with --, or even better add directly after that line a "set termout on". Hopefully output from the script will provide some clues.

    Be sure to also include the beginning of your command line session, including sqlplus login. Remember to type in show user right before you start the demobld script.
  • 11. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    After I modified the table it looks like this :


    prompt
    prompt Creating and populating tables and sequences for Employees
    prompt and Departments database.
    prompt Please wait.

    --set term on
    set term off

    CREATE TABLE DEPT (
    DEPTNO NUMBER(2) NOT NULL,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

    CREATE TABLE EMP (
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) NOT NULL,
    CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
    CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));



    The command prompt :

    SQL*Plus: Release 11.2.0.2.0 Production on ╤Ё ═юхь. 14 15:04:14 2012

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected


    LOGIN.SQL (Last amended by G. Stock/28-SEP-2012) has completed running.


    SQL> connect peter
    Enter password:
    Connected.

    Session altered.


    Session altered.

    ********************************************
    ********************************************
    ********************************************
    YOU ARE RUNNING ORACLE AT HOME.
    YOU DO NOT HAVE ACCESS TO THE TABLES
    HELD ON THE UNIVERSTIY SERVER IN CANTERBURY.
    ********************************************
    ********************************************
    ********************************************

    PL/SQL procedure successfully completed.

    Commit complete.


    LOGIN.SQL (Last amended by G. Stock/28-SEP-2012) has completed running.


    SQL> show user
    USER is "PETER"
    SQL> start demobld


    Dropping tables and sequences for Employees and Departments database.
    Please wait.

    Tables and sequences for Employees and Departments database
    created and populated.

    Demobld.sql last amended by G. Stock/15-Nov-11.


    SQL> select * from emp;

    no rows selected

    SQL>
  • 12. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    select * from cat;

    select * from dept;
  • 13. Re: Problem with SQL table
    968167 Newbie
    Currently Being Moderated
    SQL> select * from cat;
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    APEX$_ACL TABLE
    APEX$_WS_FILES TABLE
    APEX$_WS_HISTORY TABLE
    APEX$_WS_LINKS TABLE
    APEX$_WS_NOTES TABLE
    APEX$_WS_ROWS TABLE
    APEX$_WS_TAGS TABLE
    APEX$_WS_WEBPG_SECTIONS TABLE
    APEX$_WS_WEBPG_SECTION_HISTORY TABLE
    BIN$+14+HMhfQhmc/wBwDDgDMg==$0 TABLE
    BIN$+dLRoxSfRsS79lxdAW3H1g==$0 TABLE
    BIN$+uVuekYHTye3LWDqeeRmcw==$0 TABLE
    BIN$/4anwnG1RSCVM9ugl4ytOg==$0 TABLE
    BIN$0QrShGnYQ3+DRYEnnJFwwg==$0 TABLE
    BIN$0Z2X1K0XSxi97HriFGSd0Q==$0 TABLE
    BIN$0k4r8phUQ7Cc+M65swlLig==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$1UqGPQ3eRYuNkb3/XacgNQ==$0 TABLE
    BIN$1cwHQMkdSLG5cJ19OZy+ow==$0 TABLE
    BIN$243UziA/T5iejwir7s82xQ==$0 TABLE
    BIN$2I+OmIlfT6OW/3V1kj5LDQ==$0 TABLE
    BIN$2reTHL6MSS6UtzuNe5p0XQ==$0 TABLE
    BIN$3RYELYE0SRmJSbb22V+09Q==$0 TABLE
    BIN$40KATBFYR2KGOSX+NW3mzA==$0 TABLE
    BIN$4CvKoaIcRVOH3CjPI7x+2w==$0 TABLE
    BIN$4tB3PqH3QhuAzwX6JTQmfQ==$0 TABLE
    BIN$54XttiE/TLejC9R6zSvOhQ==$0 TABLE
    BIN$5bwIu8QJRk6RiqplhYOipg==$0 TABLE
    BIN$6QbqIX71Tqy2aU9lNfweJg==$0 TABLE
    BIN$6jUe6GNsT7Cfy606JJUOpg==$0 TABLE
    BIN$73QnPKxlQOSv5M5hewjUGg==$0 TABLE
    BIN$7Sxf2g9XRoOoOrN5xnl7Rw==$0 TABLE
    BIN$7ybCkN+mTPqEmkfeXvx9WQ==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$8Yes6m0ZT7arykKeIX5ylg==$0 TABLE
    BIN$8ktaa2UvRO6pJuvF5o5c3Q==$0 TABLE
    BIN$9IrxwjOAS4OqSH/+bvKpmg==$0 TABLE
    BIN$9KhJzVDAS4CBJt7JWogfmA==$0 TABLE
    BIN$9eaxchybT5aAtw3dSTHskA==$0 TABLE
    BIN$9fEMvhpLSdaw0eQBetffaw==$0 TABLE
    BIN$9jJYCasWSfmZ/I/Ou59zYA==$0 TABLE
    BIN$9rpQRYN+Tzi4MhnA/+9DVw==$0 TABLE
    BIN$AjXsa+bDR5uWpo1u2iPPCw==$0 TABLE
    BIN$AmZt7sqgSFKVlGZ1tobIkA==$0 TABLE
    BIN$B1Oafcq8SzameUXhNOoZFA==$0 TABLE
    BIN$B1PCjRt4SVuhjwg/FaCvNQ==$0 TABLE
    BIN$Bs1hr9f3S3ud0MhZO7JVCA==$0 TABLE
    BIN$CA7ACjcQQoesNqWg0k94rg==$0 TABLE
    BIN$CF2TpIgXT76mwpeYEORO3Q==$0 TABLE
    BIN$COnpWebyQyKOvQvt7OclJw==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$CVjTUKi7SMSua/LGz9fpzA==$0 TABLE
    BIN$CupfqQo7RTqqqF+xYcmUvA==$0 TABLE
    BIN$CxMLMmA4T0aIbqSKdxsZ0A==$0 TABLE
    BIN$E+TR+RejSY+t9O0NKqnEgA==$0 TABLE
    BIN$EbzQcDihSt2Cv2yGVYH76A==$0 TABLE
    BIN$F4mQgC3OQzyTHOfxe3EbMA==$0 TABLE
    BIN$FQxQIZ2nRDKWv5lhi2MgVQ==$0 TABLE
    BIN$GW80+MfyTjS2C7H/YnHKjA==$0 TABLE
    BIN$GuA4XOy+TjCgL8MwfaJ0Ag==$0 TABLE
    BIN$HPt4d6RlQHGODTK+3i9Ymg==$0 TABLE
    BIN$HtXPeXEqRxCcY1aUiSo+dQ==$0 TABLE
    BIN$Hx20oVXCReCZHWN+7aH0uw==$0 TABLE
    BIN$J9/wbv9bSbyU/2HrOBidIA==$0 TABLE
    BIN$JEsopaTPSQ2hdbLaw9KiYQ==$0 TABLE
    BIN$JGnvCmGIQ+e2Qki5AFaYDQ==$0 TABLE
    BIN$JcwyBadcTiS9pmpMi2GKwg==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$Jkf7/MHYT1qDeanLo/Sw2Q==$0 TABLE
    BIN$KAGywEHPRuepwC/S0Gv1jg==$0 TABLE
    BIN$LAJRRsjvSSWCuOxz/5NWbg==$0 TABLE
    BIN$M4/PznY8RW6/iAWbzrOYpQ==$0 TABLE
    BIN$MNavZEECR6GI7Alpo6GwxA==$0 TABLE
    BIN$MNs10fZTTii1JSzhHDL3rg==$0 TABLE
    BIN$MRQvyzXSRXK+cf0Chiv9+w==$0 TABLE
    BIN$Muw/JhnURvi8ZiS6RSpP4A==$0 TABLE
    BIN$NkeH50+nRAuypA2oBentLw==$0 TABLE
    BIN$OHpuIfw6TkKi5tEKmSbg5Q==$0 TABLE
    BIN$P1HlgkdlTJeysz9RxnEKdg==$0 TABLE
    BIN$PNgoaQR3Sq+4GhviMe7XKA==$0 TABLE
    BIN$PXM7RjH1TA+aKmNIZi6mzw==$0 TABLE
    BIN$PdfPSnT0SKOgVY5Hbzx4Jw==$0 TABLE
    BIN$QFwqh7JuTkq2beCXCYoRLw==$0 TABLE
    BIN$QVTPpzylREyM3PpRCT0UDQ==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$R4Bv3C5zRhWTMh/XAx/2KA==$0 TABLE
    BIN$R9cZvlVtS7KzPphwGIt5Gw==$0 TABLE
    BIN$RIxAOkFPQE6u7EqgPwJnWg==$0 TABLE
    BIN$RXK2T9M3SDK1cij82SYZlQ==$0 TABLE
    BIN$RkXaj9jBS02AJQR5uw4f6Q==$0 TABLE
    BIN$S0DV4Q5fROmt7zjTLIleqw==$0 TABLE
    BIN$SFdD1HNmSBmVW/9DxQofdw==$0 TABLE
    BIN$ST8Gk12YQkaq7jHAqrUPqg==$0 TABLE
    BIN$T01LdE/ZRRubJ7oSg5bgEg==$0 TABLE
    BIN$T2+bN3rETXy720BfLhMVeg==$0 TABLE
    BIN$T43E2dSZQLqX2boS5PK63A==$0 TABLE
    BIN$TMU/SouMQUycLIi5reN8ZQ==$0 TABLE
    BIN$Ti22a824SJqmEHXKJj6w0Q==$0 TABLE
    BIN$U7J7paMrR1uJIhDfWego1A==$0 TABLE
    BIN$UE/iro5GRz+mXr7kRnWUsg==$0 TABLE
    BIN$Ubgtu31tRniD+IDtfT00jA==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$UtkZpYeWTrW6Sf3xAlne6g==$0 TABLE
    BIN$V2qJgTnTQIyvUMPr7h5RRw==$0 TABLE
    BIN$ViJ7Kik0TLGqAeIeJ6i1uQ==$0 TABLE
    BIN$Vpx4c6lZTay4jNZApIwpPg==$0 TABLE
    BIN$W8qbHkYSQsW6USn4GLpVMA==$0 TABLE
    BIN$XClNraUxT+OWtUmxXrmtUw==$0 TABLE
    BIN$XfAw1Dl4S22ALnZOEKO6tA==$0 TABLE
    BIN$YCNF/5tASjW76KWJTJxQCw==$0 TABLE
    BIN$YTmiD0HjR6uevG2yQfNeYg==$0 TABLE
    BIN$YvZ85To1Rwqe0cZfYZd1bg==$0 TABLE
    BIN$Z2nomMoHSLObzFe84bpoKw==$0 TABLE
    BIN$a05wHoD7TH+b0OZIwLdkFA==$0 TABLE
    BIN$ajL4kZDNQ5CH+sVmr2T3uA==$0 TABLE
    BIN$atcjr20dQ/6nb8nJqi1Hhg==$0 TABLE
    BIN$b5cHFRdDQXeFg64XlP9Xew==$0 TABLE
    BIN$d15FUyO2TKCriycrXZGTmA==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$ezJVvthuSTKv/5tt9grKEQ==$0 TABLE
    BIN$fOH4cEYiQGaXsIy15nVBGw==$0 TABLE
    BIN$g4NzBIhFQaOFtrMg6BDXVQ==$0 TABLE
    BIN$gXS+NIYBQXyphJBPTbAfSA==$0 TABLE
    BIN$gjHOrcQ+RbOd54VUs0PqGg==$0 TABLE
    BIN$glmeEmJMTWuC375rGhPK1A==$0 TABLE
    BIN$hYCNT27iQASpxFk3QQO/WA==$0 TABLE
    BIN$i1OXZRCTSlykIzrl5cUGqg==$0 TABLE
    BIN$ilvBQOWrSOSQT01J+5MXPw==$0 TABLE
    BIN$k9oizkOsQauMFQQqYuRTaA==$0 TABLE
    BIN$kI1JVjkGQ2GPm/XiGPZjZg==$0 TABLE
    BIN$l1aRH4jvRTy0/f2o0e0IqQ==$0 TABLE
    BIN$mLVINEGoSoGBum7bIktbnQ==$0 TABLE
    BIN$mev7cot5S+iyE1NAXO2+1A==$0 TABLE
    BIN$mvIUnpItSh2+a8QbDajP8g==$0 TABLE
    BIN$mzDGCMEkR7i8LzX6cyZb+w==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$n2RcVd3wRZC/MEZbb5kQIA==$0 TABLE
    BIN$nvkvgxFQQSqpwivA9QJb/g==$0 TABLE
    BIN$olOmi9DyTOSAUMgBhWTzuQ==$0 TABLE
    BIN$om8c9R5BRRCqMIw4b3HZ9g==$0 TABLE
    BIN$p+tEJvchSKu7JzCJCl9euA==$0 TABLE
    BIN$pXjKxK23TbSuBph8rq3KRA==$0 TABLE
    BIN$q94rmRwfRgWyJ+zEOM/ZDg==$0 TABLE
    BIN$qFJb6gFNQY+mz4BPGlynSw==$0 TABLE
    BIN$qTc68mzdSN+BIZx2WvtsRA==$0 TABLE
    BIN$qbVj2OhwQNGcMfUOmv+UYQ==$0 TABLE
    BIN$qcrcUgfySaGTMLi/Ep3e/g==$0 TABLE
    BIN$st0EpAavTA+e9rrnEUI2cw==$0 TABLE
    BIN$tO53xOkNT1eDSbkY8Gft5A==$0 TABLE
    BIN$tvOzHRf2QRat10N5tO460g==$0 TABLE
    BIN$ubyEf/qiQ+qZbQXY31UprQ==$0 TABLE
    BIN$v3Pn2G1SRbSb8mVo7ks+pQ==$0 TABLE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    BIN$vHz3VmLhTA6iSnyF2nXx3g==$0 TABLE
    BIN$wELTa61TQMCn0/8pSu2XHg==$0 TABLE
    BIN$whMlaC4+StmeUeoz4OfSrQ==$0 TABLE
    BIN$xGIBh0mVSeaEoufrjVZf1Q==$0 TABLE
    BIN$xL1AsuodQMq/lbz81N6bng==$0 TABLE
    BIN$xeyqm9LcRLWp3A0Ql4icvg==$0 TABLE
    BIN$yuOzs3yORmmNmcV93KC/Vg==$0 TABLE
    BONUS TABLE
    CUSTID SEQUENCE
    CUSTOMER TABLE
    DEMO_CUSTOMERS TABLE
    DEMO_CUST_SEQ SEQUENCE
    DEMO_ORDERS TABLE
    DEMO_ORDER_ITEMS TABLE
    DEMO_ORDER_ITEMS_SEQ SEQUENCE
    DEMO_ORD_SEQ SEQUENCE
    Hit <RETURN> to continue ...


    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    DEMO_PRODUCT_INFO TABLE
    DEMO_PROD_SEQ SEQUENCE
    DEMO_STATES TABLE
    DEMO_USERS TABLE
    DEMO_USERS_SEQ SEQUENCE
    DEPT TABLE
    DUMMY TABLE
    EMP TABLE
    ITEM TABLE
    ORD TABLE
    ORDID SEQUENCE
    PRICE TABLE
    PRODID SEQUENCE
    PRODUCT TABLE
    SALES VIEW
    SALGRADE TABLE

    176 rows selected.

    SQL> select * from dept;
    Hit <RETURN> to continue ...


    DEPTNO DNAME LOC
    ------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON

    4 rows selected.
  • 14. Re: Problem with SQL table
    orafad Oracle ACE
    Currently Being Moderated
    965164 wrote:
    CREATE TABLE DEPT (
    ...
    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
    ...
    CREATE TABLE EMP (
    Script posted is missing insert statements for emp.
    select * from emp returns no rows, which is correct.

    Find the correct / full demobld.sql script and re-execute.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points