Solved

Need help with Oracle sql and I am using Aqua studio.

Posted on 2016-10-10
8
79 Views
Last Modified: 2016-10-11
I have created a very simple Oracle stored procedure using Aqua studio. When I execute/alter  the code below no errors are displayed and the stored procedure is created. However if I look in Aqua studio under the procedures folder there is a little red symbol next to the stored procedure? If I then try to execute the stored procedure I get an error message in red that looks like below:

So since the stored procedure was actually created, I thought that there were no syntax errors. However, according to the error message,
something is wrong, and I don't know why? Can anyone help me out here, to figure out what is wrong?

DBMS_OUTPUT

> Script lines: 1-1 -------------
 ORA-0900 invalid SQL Statement
Script line 1, statement line 1, coumn 0



CREATE OR REPLACE PROCEDURE "NARTI"."myProcedure"
AS
      dbNAME            VARCHAR(25);
        sqlStatement       VARCHAR(25);

BEGIN
      BEGIN
            sqlStatement := 5;
      END

      DBMS_OUTPUT.PUT_LINE('End of stored procedure myProcedure');

END myProcedure
0
Comment
Question by:brgdotnet
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837596
Sample example for you..

DECLARE
    pawan integer; -- declare
BEGIN
    pawan := 30;  --assign <<Value you want to search for >>
    
    dbms_output.Put_line(pawan); --display
           
END; 

Open in new window

0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 41837603
Pawan, that is an example of an anonymous block.  That does not address the issue that the poster is having at all.

I am not familiar with that particular tool.  A procedure is always "created", but if there are errors and the tool doesn't display them, you should be looking in USER_ERRORS (or ALL_ERRORS or DBA_ERRORS).

The issue with your procedure is missing semi colons.  You need one after the END.  If you are going to double quote the name of the procedure, you also need to double quote it in the END statement (FYI - the name of the procedure is not mandatory in the END statement).  This one should work for you:
CREATE OR REPLACE PROCEDURE "NARTI"."myProcedure"
AS
      dbNAME            VARCHAR(25);
        sqlStatement       VARCHAR(25);

BEGIN
      BEGIN
            sqlStatement := 5; 
      END;

      DBMS_OUTPUT.PUT_LINE('End of stored procedure myProcedure');

END "myProcedure";
/

Open in new window

I don't recommend using double quotes on procedure (or any other object's) name.  Makes it case sensitive and you will always need to use double quotes when accessing the procedure.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837609
Below is working for me. You dont need to specify the name of the procedure after end keyword. Double quotes in the name are also not required.

CREATE OR REPLACE PROCEDURE NARTI.myProcedure
AS
      dbNAME            VARCHAR(25);
        sqlStatement       VARCHAR(25);

BEGIN
      BEGIN
            sqlStatement := 5; 
      END

      DBMS_OUTPUT.PUT_LINE('End of stored procedure myProcedure');

END;

Open in new window

0
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.

 
LVL 34

Expert Comment

by:johnsone
ID: 41837630
Pawan,

Can you please explain how that is working?

Using the code that you posted, these are the errors:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/18    PLS-00103: Encountered the symbol "." when expecting one of the
         following:
         ;

11/66    PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         . ( ) , * % & = - + < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837777
Sorry again sir ! My bad !! Didn't test it. Shall be more careful in future.  I am also new & learning that why committing these mistakes. Also sometimes when we switch from one DB to another it happens. I understand that you have already informed me. Please note that going forward shall be more careful.

Thank you.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41838156
If you are posting code that you didn't test, say you didn't test it.  Don't say "Below is working for me".  That would imply that you tested it.  Also, where this question is specifically about a syntax issue, you really should test what you post.

There were obvious syntax issues in both the original and your posts.  I could have posted my code without testing it because I know those issues and have been using Oracle for a very long time.  I still tested it to be sure I caught all the issues.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838165
Point taken sir. Thank you very much for the help.  I am learning lot of things with EE. This is one area I need to learn quickly. Thank you again johnsone sir. I hope I shall not any more mistake.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

829 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