Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
3 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need help with Oracle sql and I am using Aqua studio.
Posted on 2016-10-10
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
3
8 Comments
Active today
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;
Active today
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:
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";
/
Active today
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;
Active today
Pawan,
Can you please explain how that is working?
Using the code that you posted, these are the errors:
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
Active today
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.
Thank you.
Active today
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.
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.
Featured Post
This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime? I'm talking things like:
* Dropping columns
* Shrinking allocated space
* Removing chained blocks and restoring the PCTFREE
* Re-or…
Cursors in Oracle:
A cursor is used to process individual rows returned by database system for a query.
In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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
Suggested Courses
Course of the Month3 days, 3 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.