Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10

    Unanswered: problems in my SQL query

    Hi, I'm having a problem in my SQL query. I'm trying to make an SQL export/import of a table in my application. I mean saving the Create, Insert statements in a text file and then being able to pass the query back to Oracle XE. This is my exported SQL:

    CREATE TABLE "TEST" (
    "ID" NUMBER NOT NULL,
    "NAME" VARCHAR2(4000),
    "DAT" DATE,
    "EMAIL" VARCHAR2(4000)
    )

    BEGIN
    INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('1', 'name', TO_DATE('2007.01.01', 'YYYY.MM.DD'), 'noone@yahoo.com');
    INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('2', 'name', TO_DATE('2007.01.02', 'YYYY.MM.DD'), 'email');
    END;

    When I paste this into Oracle DB admin SQL Commands and click 'run', I get the following error:
    ORA-00922: missing or invalid option

    These two parts (the create statement and the begin-end nest) work fine separately. As I understand the problem is I'm missing something between these two, but couldn't find any info, so decided to post here and I hope someone helps me Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    get rid of the BEGIN & END statements
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    thanks for feedback, but that didn't help. without begin/end it doesn't even work without the Create part.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I have NO idea what you are doing but it works for me!
    Code:
    SQL> set time on echo on term on
    11:14:12 SQL> @test
    11:14:16 SQL> CREATE TABLE "TEST" (
    11:14:16   2  "ID" NUMBER NOT NULL,
    11:14:16   3  "NAME" VARCHAR2(4000),
    11:14:16   4  "DAT" DATE,
    11:14:16   5  "EMAIL" VARCHAR2(4000)
    11:14:16   6  )
    11:14:16   7  /
    
    Table created.
    
    11:14:17 SQL> INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('1', 'name', TO_DATE('2007.01.01', 'YYYY.MM.DD'), 'noone@yahoo.com');
    
    1 row created.
    
    11:14:17 SQL> INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('2', 'name', TO_DATE('2007.01.02', 'YYYY.MM.DD'), 'email');
    
    1 row created.
    
    11:14:17 SQL> select count(*) from test;
    
      COUNT(*)
    ----------
             2
    
    11:14:17 SQL>
    I suspect PEBKAC between you & what ever GUI client you are (ab)using.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2005
    Posts
    57
    You need a ';' or '/' after the create statement.

  6. #6
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    thank you. it seems to be an Oracle Express Edition GUI bug(?).

  7. #7
    Join Date
    Dec 2007
    Location
    India
    Posts
    5

    May I help you.

    Dear candidate , if you are writing this SQL query in PL/SQL , why don't you declare variables for each SQL statement that you write in a PL/SQL block and then run the block.If you don't know how to declare variables then tell me back so that I may help you .
    Regards ,
    Experts

    Quote Originally Posted by jur
    Hi, I'm having a problem in my SQL query. I'm trying to make an SQL export/import of a table in my application. I mean saving the Create, Insert statements in a text file and then being able to pass the query back to Oracle XE. This is my exported SQL:

    CREATE TABLE "TEST" (
    "ID" NUMBER NOT NULL,
    "NAME" VARCHAR2(4000),
    "DAT" DATE,
    "EMAIL" VARCHAR2(4000)
    )

    BEGIN
    INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('1', 'name', TO_DATE('2007.01.01', 'YYYY.MM.DD'), 'noone@yahoo.com');
    INSERT INTO "TEST" ("ID", "NAME", "DAT", "EMAIL") VALUES ('2', 'name', TO_DATE('2007.01.02', 'YYYY.MM.DD'), 'email');
    END;

    When I paste this into Oracle DB admin SQL Commands and click 'run', I get the following error:
    ORA-00922: missing or invalid option

    These two parts (the create statement and the begin-end nest) work fine separately. As I understand the problem is I'm missing something between these two, but couldn't find any info, so decided to post here and I hope someone helps me Thanks in advance.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @jur: A bug? What bug? Every statement needs to be terminated. It is a common practice that these are a semi-colon ( ; ) or a slash ( / ).

    @Experts: What kind of variables do you have in mind when there are CREATE TABLE and INSERT INTO statements to be executed?

  9. #9
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    well i took care of the problem. maybe it isn't a bug, i don't know. i know, that all the statements have to be terminated, but in Oracle XE's GUI I just couldn't get this one to work. I tried many combinations, but it still doesn't work. I think Expert's solution would work on the GUI, but I'm writing my own app in delphi that has to export/import SQL of the tables and I just don't want to get into details of PL/SQL language. I solved this by parsing statements one by one from the app and then executing them the same way. but I couldn't just execute all of this altogether (and I don't know if it is even possible). anyway, thanks to all of you!

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm writing my own app in delphi that has to export/import SQL of the tables ...
    I don't know anything about Delphi, but - did you consider using Oracle's Export and Import utilities (or even Data pump)? These utilities are designed to export tables (and constraints, indexes, ...) and data stored in them into a file (which has a format not readable by other applications but Import); it is transportable and you can import it into another Oracle database.

    There are issues when exporting and importing between different Oracle database versions, but that's another story (not that it can not be done!).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •