Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: procedure error
You
need to use 'Dynamic SQL' in order to drop and create
tables.
<FONT face=Tahoma
size=2>-----Original Message-----From: Lance Prais
[mailto:lprais_at_ts.checkpoint.com]Sent: Wednesday, January 16, 2002
4:36 PMTo: Multiple recipients of list ORACLE-LSubject:
procedure error
<FONT color=#0000ff face=Arial
size=2>Why in toad when I try to create a stored proc where I drop and
create a table I get the following error:
<FONT color=#0000ff face=Arial
size=2>
<FONT color=#0000ff face=Arial
size=2>PLS-00103: Encountered the symbol "DROP" when expecting one of the
following:
<FONT color=#0000ff face=Arial
size=2> begin function package pragma procedure subtype type
use <an identifier> <a double-quoted
delimited-identifier> cur
<FONT color=#0000ff face=Arial
size=2>Here is my code:
<FONT color=#0000ff face=Arial
size=2>
<SPAN
style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">CREATE<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">OR<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">REPLACE<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">PROCEDURE<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: olive; FONT-FAMILY: Courier; FONT-SIZE: 10pt">Primus_Report<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">Drop<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">Table<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: olive; FONT-FAMILY: Courier; FONT-SIZE: 10pt">PRIMUS_TEMP_DUMP<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">;<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">CREATE<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">TABLE<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: olive; FONT-FAMILY: Courier; FONT-SIZE: 10pt">PRIMUS_TEMP_DUMP<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> SOLUTION_ID<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">85<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">)<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">NOT<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">NULL,<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> TITLE<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">3498<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> OWNER<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> P_TYPE<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> AUTHOR<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> MODIFIED_BY<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> ESCALATION_GROUP<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> TECH_RESOURCE<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> P_PARTITION<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> STATUS<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> HyperLinks<SPAN style="mso-spacerun: yes"> <SPAN style="mso-tab-count: 2"><SPAN
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> Style_reviewer<SPAN style="mso-tab-count: 2">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> DATE_CREATED<SPAN style="mso-spacerun: yes">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> DATE_MODIFIED<SPAN style="mso-spacerun: yes">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> ALERT<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> URGENCY<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">96<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> NO_PAGEVIEWS_LAST_MONTH<SPAN style="mso-spacerun: yes"> <SPAN style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> NO_PAGEVIEWS_QUARTER<SPAN style="mso-spacerun: yes"> <SPAN style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> NO_LINKS_LAST_WEEK<SPAN style="mso-spacerun: yes">
style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">),<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"><SPAN style="mso-spacerun: yes"> NO_LINKS_LAST_MONTH<SPAN style="mso-spacerun: yes"> <SPAN style="COLOR: red; FONT-FAMILY: Courier; FONT-SIZE: 10pt">VARCHAR2<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: maroon; FONT-FAMILY: Courier; FONT-SIZE: 10pt">255<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">)<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">);<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">
style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">INSERT<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">INTO<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: olive; FONT-FAMILY: Courier; FONT-SIZE: 10pt">PRIMUS_TEMP_DUMP<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">(<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">solution_id<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">,<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">Title<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">)<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">SELECT<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">pc_solution_id<SPAN
style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">FROM<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt"> <SPAN style="COLOR: olive; FONT-FAMILY: Courier; FONT-SIZE: 10pt">pt_solution<SPAN style="COLOR: #0000f0; FONT-FAMILY: Courier; FONT-SIZE: 10pt">;<SPAN style="COLOR: black; FONT-FAMILY: Courier; FONT-SIZE: 10pt">