Home » SQL & PL/SQL » SQL & PL/SQL » Run Time Errors 933 and 512 executing Proc (SQL Developer Version 17.3.2.341)
Run Time Errors 933 and 512 executing Proc [message #681339] Thu, 09 July 2020 12:27 Go to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
My goal is to pass a Table Name as an input parameter to a proc that calls a SELECT
statement using the Table Name, e.g. SELECT xxx FROM Table_Name. The table name
that resides in my schema is CLT_F850

---------------------------------------------
Here's the exec statement

exec TestProc2 ('P' , 'CLT_F850' ) ;

---------------------------------------------
Here's the error message
-- BEGIN TestProc2 ('P' , 'CLT_F850' ); END;
-- Error report -
-- ORA-00933: SQL command not properly ended
-- ORA-06512: at "XXX.TESTPROC2", line ##
-- ORA-06512: at line 1
-- 00933. 00000 - "SQL command not properly ended"
-- *Cause:
-- *Action:
--
-- Note: Line ## in the error message is this statement
-- EXECUTE IMMEDIATE InsertPrior ;

---------------------------------------------

Here's the Proc

create or replace PROCEDURE TestProc2
(Prior_Or_New CHAR, Table_Name CHAR) AS

InsertPrior VARCHAR2(2000) ;
InsertNew VARCHAR2(2000) ;
BEGIN

---------------------------------------------
InsertPrior :=

'INSERT INTO ' ||
' TEST.XTEST_Before_After_Counts ' ||
' (RptName , RunDate , ' ||
' Prior_Date , Prior_Date_Cnt , ' ||
' New_Date , New_Date_Cnt ' ||
' ) ' ||
' SELECT Table_Name AS RptName , RunDate , ' ||
' Load_Date AS Prior_Date , ' ||
' (SELECT COUNT(*) AS Cnt ' ||
' FROM CLT_F850) AS Prior_Date_Cnt , ' ||
' NULL AS New_Date , ' ||
' NULL AS New_Date_Cnt ' ||
' FROM CLT_F850 ' ||
' FETCH FIRST 1 ROWS ONLY ; '
;

---------------------------------------------

IF SUBSTR(Prior_Or_New,1,1) = 'P'
THEN
EXECUTE IMMEDIATE InsertPrior ;
END IF ;


COMMIT;

COMMIT;
END;

Re: Run Time Errors 933 and 512 executing Proc [message #681340 is a reply to message #681339] Thu, 09 July 2020 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

The problem is the ";" at the end of the statement.

For information ";" at the end of the statement is a terminator for SQL*Plus (and subsequent client tools) to tell that the statement text is completed and it has to execute it (terminator character that can be changed using set sqlterminator command).

In addition, in your example, there is no reason to use execute immediate as the statement text is constant and you don't use the procedure parameters.

[Updated on: Thu, 09 July 2020 12:50]

Report message to a moderator

Re: Run Time Errors 933 and 512 executing Proc [message #681341 is a reply to message #681339] Thu, 09 July 2020 13:17 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
I am a newbie so many apologies for my overwhelming number of newbie mistakes. I will certainly do better next time. And thank you for the lightning fast reply.

That said, the proc does not compile if I get rid of the semi-colon and\or get rid of the Execute Immediate. I see the Error message below in the Compiler Log

Error(49,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem return returning <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between into using || multiset bulk member submultiset The symbol ";" was substituted for "END" to continue.

Re: Run Time Errors 933 and 512 executing Proc [message #681342 is a reply to message #681341] Thu, 09 July 2020 13:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
InsertPrior :=

'INSERT INTO ' ||
' TEST.XTEST_Before_After_Counts ' ||
' (RptName , RunDate , ' ||
' Prior_Date , Prior_Date_Cnt , ' ||
' New_Date , New_Date_Cnt ' ||
' ) ' ||
' SELECT Table_Name AS RptName , RunDate , ' ||
' Load_Date AS Prior_Date , ' ||
' (SELECT COUNT(*) AS Cnt ' ||
' FROM CLT_F850) AS Prior_Date_Cnt , ' ||
' NULL AS New_Date , ' ||
' NULL AS New_Date_Cnt ' ||
' FROM CLT_F850 ' ||
' FETCH FIRST 1 ROWS ONLY ; ' -- remove semi-colon
;
SY.
Re: Run Time Errors 933 and 512 executing Proc [message #681343 is a reply to message #681342] Thu, 09 July 2020 13:30 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Thank you, Sir!!!!!!!!!!!!

I am good to go now

I bid both of you a most excellent rest of your day and week for that matter
Re: Run Time Errors 933 and 512 executing Proc [message #681344 is a reply to message #681343] Thu, 09 July 2020 13:43 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Well...almost good to go. Smile

InsertPrior :=

'INSERT INTO ' ||
' VACLT.XCLT_F800_F900_Before_After_Counts ' ||
' (RptName , RunDate , ' ||
' Prior_Date , Prior_Date_Cnt , ' ||
' New_Date , New_Date_Cnt ' ||
' ) ' ||
' SELECT Table_Name AS RptName , RunDate , ' ||
' Load_Date AS Prior_Date , ' ||
' (SELECT COUNT(*) AS Cnt ' ||
' FROM Table_Name) AS Prior_Date_Cnt , ' ||
' NULL AS New_Date , ' ||
' NULL AS New_Date_Cnt ' ||
' FROM Table_Name ' ||
' FETCH FIRST 1 ROWS ONLY '
;

I replaced CLT_F850 with the Input Parameter, Table_Name.

I now get this message

ORA-00942: table or view does not exist
ORA-06512: at "VACLT.CFTESTPROC2", line 49
ORA-06512: at line 1
00942. 00000 - "table or view does not exist"
Re: Run Time Errors 933 and 512 executing Proc [message #681345 is a reply to message #681344] Thu, 09 July 2020 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please format your post!

Quote:
I replaced CLT_F850 with the Input Parameter, Table_Name.
"Table_Name" inside a string is just the string "Table_Name".
You must use concatenation:
' FROM '||Table_Name||' 
But of course, the error you showed has nothing to do with the code you posted.

[Updated on: Thu, 09 July 2020 14:42]

Report message to a moderator

Re: Run Time Errors 933 and 512 executing Proc [message #681348 is a reply to message #681345] Fri, 10 July 2020 06:20 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
I hope I am now formatting the code per spec. I have yet one more question embedded below. Thank you for your answers and patience thus far


InsertPrior := 

'INSERT INTO                                     ' ||
'       VACLT.XCLT_F800_F900_Before_After_Counts ' ||
'             (RptName    , RunDate ,            ' || 
'              Prior_Date , Prior_Date_Cnt ,     ' ||
'              New_Date   , New_Date_Cnt         ' ||
'             )                                  ' ||
-- QUESTION: 
-- I am using NULL as the RptName.  How do I use the
-- value of input parm Table_Name, which is CLT_F850
-- and used in the FROM clauses below
' SELECT NULL AS RptName ,                       ' || 
'        RunDate ,                               ' ||
'        Load_Date AS Prior_Date ,               ' ||
'       (SELECT COUNT(*) AS Cnt                  ' ||
'          FROM ' || Table_Name || ')            ' ||
'            AS Prior_Date_Cnt ,                 ' ||
'        NULL AS New_Date ,                      ' ||
'        NULL AS New_Date_Cnt                    ' ||
'  FROM ' || Table_Name || '                     ' ||
' FETCH FIRST 1 ROWS ONLY                       '
; 
Re: Run Time Errors 933 and 512 executing Proc [message #681349 is a reply to message #681348] Fri, 10 July 2020 06:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Q'[ SELECT ']' || TABLE_NAME || Q'[' AS RptName ,                       ]' || 
SY.
Re: Run Time Errors 933 and 512 executing Proc [message #681350 is a reply to message #681349] Fri, 10 July 2020 07:03 Go to previous message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Thank you!
Previous Topic: Running script present on webserver
Next Topic: insert rows
Goto Forum:
  


Current Time: Fri Mar 29 06:39:23 CDT 2024