Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "CREATE"
PLS-00103: Encountered the symbol "CREATE" [message #222867] Tue, 06 March 2007 15:23 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
Hi,

I don't mean to post a dumb question here. But I can't find out the reason why I am getting this error.

PLS-00103: Encountered the symbol "CREATE"


The following is what I ran
--DROP TABLE DM_METRICS.DP_vars; 
	BEGIN DROP_TABLE('DP_vars'); END; 
	
	CREATE TABLE DM_METRICS.DP_vars nologging as
		   SELECT DISTINCT 
		   		  coalesce(a.mkt_unit, b.mkt_unit, c.mkt_unit) mkt_unit,
				  DUNS_NUMBER, ORG_ID, STAND_ALONE_BUSINESS_FLG, MU_ATS_INDUSTRY, DOMESTIC_ULTIMATE_FLG, 
				  GCD_CUSTOMER_FLAG_GLOBAL_ULT, GCD_CUSTOMER_FLAG_MU, GLOBAL_ULTIMATE_FLG, GCD_CUSTOMER_FLAG_SITE,
				  GSRT_CUSTOMER_FLAG_GLOBAL_ULT, GSRT_CUSTOMER_FLAG_MU, GSRT_CUSTOMER_FLAG_SITE, MKT_UNIT_FLG, 
				  EMPLOYEES_TOTAL, ANNUAL_SALES_USD, SIC_CODE, MU_ANNUAL_SALES_USD, MU_EMPLOYEES_HERE, 
				  MU_NO_OF_YEARS, MU_STAND_ALONE_BUSINESS_FLG, MU_CREDIT_RATING, MU_URLS_REG, MU_PUBLIC_PRIVATE_INDICATOR, 
   				  FLG, b.COMPANY_SPONSOR_OU, c.T3_AS, c.T3_DB, c.T3_COLLABORATION, c.T3_CRM, c.T3_ERP, c.T3_EBS, 
				  c.T3_OTHER_APPLICATION, c.T3_UNSPECIFED_OTHER, c.IBM_DB, c.INFORMIX_DB, c.LOTUS_EMAIL, 
				  c.MICROSOFT_DB, c.MICROSOFT_EMAIL, c.SYBASE_DB
		   FROM 
		   	(SELECT * FROM DM_METRICS.VARS_DNB_SMAD  where org_id ^= 99999999) a
		   		  	  	 	  	  	FULL OUTER JOIN DM_METRICS.DP_ou_mkt2 b 
									ON (a.mkt_unit = b.mkt_unit)
									FULL OUTER JOIN DM_METRICS.DP_prod_mkt2 c
									ON ( a.mkt_unit = c.mkt_unit );



And here is the procedure drop_table I created.

CREATE OR REPLACE 
	   PROCEDURE DROP_TABLE
	(p_table_name IN VARCHAR2)
	IS
	i INTEGER;
	BEGIN
	SELECT count(1)
	  INTO i
	  FROM user_tables
	 WHERE table_name = trim(upper(p_table_name));
	IF i > 0
	THEN
	    EXECUTE IMMEDIATE 'DROP TABLE ' ||  p_table_name;
		DBMS_OUTPUT.PUT_LINE('TABLE ' ||p_table_name||' dropped'); 
	ELSE DBMS_OUTPUT.PUT_LINE('TABLE '||p_table_name||' not found');

	END IF;
END;
/



Please help.
Re: PLS-00103: Encountered the symbol "CREATE" [message #222868 is a reply to message #222867] Tue, 06 March 2007 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You can not do DDL from within PL/SQL without using EXECUTE IMMEDIATE; but you already knew that 'cuz that is how the table was dropped.
Re: PLS-00103: Encountered the symbol "CREATE" [message #222871 is a reply to message #222868] Tue, 06 March 2007 15:35 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
The reason why I'm creating this procedure is I don't want to keep getting the error messages table/view does not exist. Cannot drop table blah...


So I devise this to skip getting those error messages ... But the procedure compiled Valid. If I run it separately, there's no problem at all.

Re: PLS-00103: Encountered the symbol "CREATE" [message #222874 is a reply to message #222867] Tue, 06 March 2007 15:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> But the procedure compiled Valid. If I run it separately, there's no problem at all.
Why do you INSIST on telling us what you think is happening rather than doing COMPLETE cut & paste so we can see for ourselves?
If you knew what you were really doing, you would not be having problems & whining about the results.
Maybe you should stick with ACCESS & avoid future challenges with Oracle.
I wonder if you have even ever opened the PL/SQL & SQL Reference manuals found at http://tahiti.oracle.com
Re: PLS-00103: Encountered the symbol "CREATE" [message #222875 is a reply to message #222867] Tue, 06 March 2007 15:46 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
As I have extensively tested it independently, the drop_table procedure worked perfectly fine alone.
Re: PLS-00103: Encountered the symbol "CREATE" [message #222876 is a reply to message #222867] Tue, 06 March 2007 15:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>As I have extensively tested it independently, the drop_table procedure worked perfectly fine alone.
Rinse, lather, Repeat for CREATE.....

Then life is golden & Your On Youre Own (YOYO)!

[Updated on: Tue, 06 March 2007 15:58] by Moderator

Report message to a moderator

Re: PLS-00103: Encountered the symbol "CREATE" [message #222877 is a reply to message #222876] Tue, 06 March 2007 16:05 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Please stop posting nonsensical posts as it's wasting the resources of this community.
Re: PLS-00103: Encountered the symbol "CREATE" [message #222879 is a reply to message #222867] Tue, 06 March 2007 16:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Please stop posting nonsensical posts as it's wasting the resources of this community.
POT<==>KETTLE<==>BLACK
Re: PLS-00103: Encountered the symbol "CREATE" [message #222935 is a reply to message #222879] Wed, 07 March 2007 00:28 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
BEGIN DROP_TABLE('DP_vars'); END;

CREATE TABLE DM_METRICS.DP_vars nologging as


You need a slash after END; to first execute the anonymous block.
Previous Topic: Session Information & SQL Call
Next Topic: How to DELETE The data FROM a PL/SQL table
Goto Forum:
  


Current Time: Sun Dec 04 06:25:13 CST 2016

Total time taken to generate the page: 0.17171 seconds