Avatar of brgdotnet
brgdotnet
Flag for United States of America asked on

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

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
Oracle Database

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
Pawan Kumar

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

ASKER CERTIFIED SOLUTION
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Pawan Kumar

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

johnsone

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Pawan Kumar

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

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.
Pawan Kumar

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.