Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Works in SQL Worksheet, fails in SQL Developer...

    This code works just dandy in Oracle's SQL Plus Worksheet:
    Code:
    VAR CSR REFCURSOR;
    DECLARE V_STRING VARCHAR2(30) := 'TESTSTRING';
    BEGIN
      OPEN :CSR FOR
      SELECT  V_STRING
      FROM    DUAL;
    END;
    /
    PRINT :CSR
    ...but fails in the new SQL Developer's SQL Worksheet with the error "Missing IN or OUT parameter at index ::1"

    The only information I have found on the web indicates that this is a java error, which doesn't make sense to me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If it will make you feel any better, SQL Developer isn't the only tool which doesn't like such a code: TOAD (quite popular in Oracle world) is also one of them.

    I can't explain it officially- could try, though, using my own words: SQL*Plus is a command-line tool (the one which has a strictly defined prompt). TOAD and SQL Developer are not - there's, well, some kind of an editor which is also used to execute commands. Variables' declaration, as well as some SQL*Plus commands (PRINT is one of them; there are many others!) are not valid in this context.

    Error message doesn't seem to be meaningful; however, you got it and, as far as I can tell, you'll have to live with it. Therefore, either run such things in SQL*Plus, or rewrite the code.

    Not very helpful, I'm afraid ... But, wait a little bit more - perhaps you'll hear better news from someone else.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I don't have a copy of SQL Developer to hand, but it does not claim to support all SQL*Plus commands (as it makes clear here). VARIABLE, REFCURSOR and PRINT are from SQL*Plus and not necessarily supported by any other tools.

  4. #4
    Join Date
    Sep 2004
    Posts
    60
    we had similar experience.

    A code was working in sqlplus work sheet but not on sql promt.
    we copied same code in Unix & found some characters, which were not visible in sqlprompt & work sheet. Removing that character solved the problem.

    I am not sure but youcan give a try to find out any such unwanted charater.

  5. #5
    Join Date
    Nov 2006
    Location
    midwest
    Posts
    3

    Angry It runs in TOAD for me...

    [QUOTE=Littlefoot]If it will make you feel any better, SQL Developer isn't the only tool which doesn't like such a code: TOAD (quite popular in Oracle world) is also one of them.
    QUOTE]

    Littlefoot,

    I'm curious what version of TOAD you're using. I won't speak the the free version but licensed versions (I'm on 8.6) had no trouble executing the SCRIPT - script is the operative word. i.e. It won't execute as an SQL statement but it will execute as a script (lightening bolt on the script debugging toolbar or execute as script from the SQL editor toolbar.)

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jhebert
    I won't speak the the free version but licensed versions (I'm on 8.6) had no trouble executing the SCRIPT
    Well, then TOAD obviously implemented the SQL*Plus commands itself. SQL Developer does not do that (though they are trying to implement more and more SQL*Plus commands).

    @blindman:

    If you are unsure if a certain SQL command is a special SQL*Plus command, it's pretty easy to find out:
    All statements processed by the server are listed in the SQL reference manual:
    http://download-west.oracle.com/docs...a96540/toc.htm

    All commands that are unique to SQL*Plus are listed in the SQL*Plus reference manual:
    http://download-west.oracle.com/docs...a90842/toc.htm

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for the replies, everyone.

    But how would I rewrite the script to accomplish the same thing using SQL Developer?

    My goal is to be able to create test harnesses for debugging procedures. IE: declare parameter variables, execute the code, see the results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2006
    Location
    midwest
    Posts
    3

    SQL Developer Alternatives

    Blindman,

    Can't help you out on making it work in SQL Developer - it's free and I have a copy collecting dust on my harddrive but it didn't take me long to figure out that SQL Developer is well behind other tool's cababilities.

    If budget is the reason you're intent on SQL Developer you may be hosed. I don't know if Bethnic Software's offerings will run you scripts (such as Golden and PLEdit.) They are considerably more affordable than TOAD and may be worth a look. I have a colleague who prefers them to TOAD - you can get trial versions at their website.

    jh

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, SQL Developer is a friendlier interface than Oracle's Enterprise Manager, which sucks loads in a uniquely Java sort of way, and that is why we've been trying out Developer. But I quickly found the SQL Developer lacks almost all admin functionality, which is definitely a minus.
    I've used TOAD, which was OK, but I actually preferred another product that I tried out last year, of which I can't currently remember the name...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    but I actually preferred another product that I tried out last year, of which I can't currently remember the name...
    Can't be that impressive then ...

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I can never understand why people pay so much for TOAD when PL/SQL Developer is less buggy, more configurable and one sixth of the price.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @JHebert: my TOAD is 7.6 and it won't run such a code (not even as a script) - it says "ORA-01008: not all variables bound" and ends the execution.

    P.S. I first thought you were Johnny Herbert (former Formula 1 driver)

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Isn't the current version 8.6? 7.6 must be from five years ago. In any case are you sure you are running it as a script? TOAD has a separate "Run As Script" button as jhebert mentioned.

    PL/SQL Developer doesn't suppport REFCURSOR variables in its SQL*Plus emulator (Command Window), but you would just set it up in a Test Window and define bind variables in the lower panel of the GUI. You can also run multiple tests from the Test Manager which lights up red or green indicators depending on success or failure.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I didn't check the current version, but mine is 7.6. Perhaps we should purchase a new version?

    I pressed "Execute as a script" button and got a nice little message.
    Attached Thumbnails Attached Thumbnails toad76_run_as_script.JPG   toad76.JPG  

  15. #15
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Hey this is fun. Here's mine:
    Attached Thumbnails Attached Thumbnails PLSQLDevScreenshot.png  

Posting Permissions

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