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  |
 |
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  |
 |
Michel Cadot
Messages: 68421
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 #681342 is a reply to message #681341] |
Thu, 09 July 2020 13:23 |
Solomon Yakobson
Messages: 3213
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
; |
|
|
|
|
|
| Re: Run Time Errors 933 and 512 executing Proc [message #681344 is a reply to message #681343] |
Thu, 09 July 2020 13:43 |
|
cfairtp
Messages: 15
Registered: July 2020
|
Junior Member |
|
|
Well...almost good to go. 
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 #681348 is a reply to message #681345] |
Fri, 10 July 2020 06:20 |
|
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 '
;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 30 00:01:09 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
