Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate error
Execute Immediate error [message #263834] Fri, 31 August 2007 02:44 Go to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
Hi there,

When trying to execute the following procedure I get an error:

"ERROR at line 1:
ORA-06550: line 1, column 21:
PLS-00103: Encountered the symbol "FROM" when expecting one of
the following:
:= . ( % ;
The symbol ":= was inserted before "FROM" to continue.
"

Here's the proc (it creates without errors) :

create or replace PROCEDURE TestRob(accid NUMBER)
IS
sql_stmt VARCHAR2(800) := '';
Begin
sql_stmt := 'insert into tblTempDays(a, b, c,d, e) select a,
b, c,d, e FROM tblx where ACC_ID = : accid order by a, b,
c';
execute immediate sql_stmt USING accid;
commit;
END;

Would someone be able to assit me as the insert itself actually runs fine...

Thanks in advance
Re: Execute Immediate error [message #263837 is a reply to message #263834] Fri, 31 August 2007 02:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why use dynamic sql??
Re: Execute Immediate error [message #263846 is a reply to message #263834] Fri, 31 August 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fisrt,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then,
Follow Frank's advice.

Finally,
": accid" is wrong syntax it is "acid".

Regards
Michel
Re: Execute Immediate error [message #263868 is a reply to message #263846] Fri, 31 August 2007 03:43 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
I'm using dynamic sql as create table doesn't work directly in a proc.

What do you mean it's mean to be "acid"? I am sending in a param called addid.
Re: Execute Immediate error [message #263870 is a reply to message #263868] Fri, 31 August 2007 03:47 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
I mean I am sending in a param called accid, not acid
Re: Execute Immediate error [message #263878 is a reply to message #263870] Fri, 31 August 2007 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If was a joke...
Remove ":".

Regards
Michel
Re: Execute Immediate error [message #263885 is a reply to message #263878] Fri, 31 August 2007 04:05 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
Remove the indication that it's expecting a bind variable?
Then no records will be inserted into the temp table as there will be no match for the number accid equal to a string 'accid'.
Re: Execute Immediate error [message #263891 is a reply to message #263885] Fri, 31 August 2007 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove ":" after you remove execute immediate I meant.
Copy and paste your execution in a formatted way.

Regards
Michel
Re: Execute Immediate error [message #263896 is a reply to message #263891] Fri, 31 August 2007 04:19 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
I would like to know how to get it to work using dynamic sql.
I'm trying to find out how to get it to work, not how to do it in another manor. Irrelevant of the fact that it can be done in another manor, I'm trying to test and grasp dynamic sql
Re: Execute Immediate error [message #263897 is a reply to message #263868] Fri, 31 August 2007 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create table doesn't work ...
RobRed
I'm using dynamic sql as create table doesn't work directly in a proc.


... but you are inserting records
RobRed
sql_stmt := 'insert into tblTempDays(a, b, c,d, e) select a,...


Where did CREATE TABLE go? By the way, are you SURE you want to create tables dynamically? Think twice! It is Oracle, not MS SQL Server (if that's your background).

By the second way, INSERT INTO ... SELECT ... ORDER BY doesn't make much sense. Why do you insist on ORDER BY clause?
Re: Execute Immediate error [message #263905 is a reply to message #263897] Fri, 31 August 2007 04:36 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
Create table comment can be ignored - previous logic I have moved on about.

Inserting with an order by makes total sense. You see, in order to calculate, for example, the running average total over the last 12 months for each record one would need the records to be ordered in order to cursor through the records and add the number of days between records up until and including the record that matches a total of 365 days, or just over. Then one can sum the values of the aforementioned records and calculate the running average. For each record. Date order dependant. Just as an example.

So, could someone please assist in letting me know how to work the dynamic sql as I asked above.
Re: Execute Immediate error [message #263923 is a reply to message #263905] Fri, 31 August 2007 05:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummmm ... you got something wrong here, I'm afraid. ORDER BY makes sense in select operations, not insert ones. It really doesn't matter in which order records are inserted into the table; you can't even insert record "between two existing records", even if you'd want it to. Cursor you mentioned SELECTS records, so - in there - you may use order by clause.

Here's a general idea how to use EXECUTE IMMEDIATEt:
SQL> CREATE OR REPLACE PROCEDURE testrob (par_deptno IN NUMBER)
  2  IS
  3    sql_stmt VARCHAR2(800);
  4  BEGIN
  5    sql_stmt := 'insert into dept (deptno, dname, loc) ' ||
  6                'select deptno, dname, loc ' ||
  7                'from dept ' ||
  8                'where deptno = ' || par_deptno;
  9
 10    EXECUTE IMMEDIATE sql_stmt;
 11  END;
 12  /

Procedure created.

SQL>
SQL> SELECT * FROM DEPT WHERE deptno = 10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL>
SQL> EXEC testrob (10);

PL/SQL procedure successfully completed.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM DEPT WHERE deptno = 10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK

SQL>
Re: Execute Immediate error [message #263937 is a reply to message #263923] Fri, 31 August 2007 05:33 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
So you don't know the way then.
Re: Execute Immediate error [message #263944 is a reply to message #263937] Fri, 31 August 2007 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1600/0/ Apparently not, but I did it my way.
Re: Execute Immediate error [message #263961 is a reply to message #263937] Fri, 31 August 2007 06:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you only tell us half your problem, how do you expect us to solve it?

You show us a procedure that uses dynamic sql to do a simple insert. When we tell you not to use dynamic sql for that, all of a sudden you mention you create a table on the fly.

When we advise you not to do that, because it is a VERY bad idea in Oracle, you refuse the advise and tell us to focus on the problem you THINK you have.

You show us a straightforward insert as select, but then you mention you need the order by because you apparently seem to use some aggregation using analytical function.

Better open your mind for well-meant advises. If you are unwilling to do so, Google would be a better place to use then an interactive forum..
Re: Execute Immediate error [message #263984 is a reply to message #263961] Fri, 31 August 2007 07:29 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
I am unsure as to how I have given only half of my problem.
I posted code that gives an error and asked how to resolve that specific error. I did not ask for other ways of resolving the error or advice on how to do it differently.

If you refer to the insert as shown in the original post there is an order by statement.

I have my mind open for well-meant advice. I am still awaiting it concerning my question on how to resolve the error returned from the code I posted.

I thought this was a forum to assist with SQL not an insult driven site.
Re: Execute Immediate error [message #263987 is a reply to message #263984] Fri, 31 August 2007 07:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I had no intention to insult you, sorry if I worded my reply more harshly than I intended.
As far as I can see, your question was "Would someone be able to assit me as the insert itself actually runs fine..."

Our answer was to get rid of the dynamic sql. This would be the easiest and best way to get rid of the error, given the information you provided.
Re: Execute Immediate error [message #263989 is a reply to message #263987] Fri, 31 August 2007 07:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What database version do you use?
Re: Execute Immediate error [message #263992 is a reply to message #263989] Fri, 31 August 2007 07:46 Go to previous messageGo to next message
RobRed
Messages: 9
Registered: August 2007
Junior Member
version 10.2.0.3
Re: Execute Immediate error [message #263993 is a reply to message #263987] Fri, 31 August 2007 07:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read OraFAQ Forum Guide to learn how to ask a question and properly format it, provide necessary information (including CREATE TABLE and INSERT INTO sample data statements).

Here's my second attempt (as you didn't like the first one).
SQL> CREATE TABLE tblx
  2    (a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, acc_id NUMBER);

Table created.

SQL> CREATE TABLE tbltempdays AS SELECT * FROM tblx;

Table created.

SQL> INSERT INTO tblx (a, b, c, d, e, acc_id) VALUES (1, 2, 3, 4, 5, 10);

1 row created.

SQL> CREATE OR REPLACE PROCEDURE Testrob (accid IN NUMBER)
  2  IS
  3    sql_stmt VARCHAR2(800);
  4  BEGIN
  5    sql_stmt := 'insert into tblTempDays( a, b, c, d, e) ' ||
  6                ' select a, b, c, d, e FROM tblx ' ||
  7                ' WHERE ACC_ID = ' || accid;
  8
  9    EXECUTE IMMEDIATE sql_stmt;
 10
 11    COMMIT;
 12
 13  END;
 14  /

Procedure created.

SQL>
SQL> EXEC Testrob (10);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM tbltempdays;

         A          B          C          D          E     ACC_ID
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3          4          5 NULL

Compare this code with yours to find the difference.
Re: Execute Immediate error [message #264008 is a reply to message #263993] Fri, 31 August 2007 08:57 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ouch. Don't get rid of a perfect good bind variable!

  ' WHERE ACC_ID = :accid';

execute immediate sql_stmt using accid;
Previous Topic: Like Pattern search
Next Topic: stored proc to add data to a new row?
Goto Forum:
  


Current Time: Sat Dec 10 05:25:39 CST 2016

Total time taken to generate the page: 0.09498 seconds