Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically pass table name in PLSQL (Oracle client 18.0)
Dynamically pass table name in PLSQL [message #685468] Sat, 22 January 2022 07:35 Go to next message
345678
Messages: 3
Registered: January 2022
Junior Member
Hi,
I am passing table name to oracle stored procedure and wants to execute it dynamically . Below is snippet of my stored procedure.
When i execute this ,i'm getting "ORA-00984: column not allowed here" error . Could you please help me in solving this issue.

execute immediate 'INSERT INTO ' ||P_TableName|| '(T$IDNO,T$TEDT,T$CONO,T$TTYP,T$TSER,T$FYER,T$FPRD,T$RPRD,T$VYER,T$VPER,T$ITYP,T$ISER,T$IDOC,T$STAT,T$BTNO,T$BREF,T$USER,T$TRST,T$TRD T,T$MAIM,T$REFCNTD,T$REFCNTU) VALUES (p_IdentificationNumber,p_TransactionDate,p_BatchCompany,p_TTransactionType,p_TransactionSeries,p_FiscalYear,p_FiscalPeriod,p_Reporti ngPeriod,p_TaxYear,p_TaxPeriod,p_IImportJournalType,p_ImportJournalSeries,p_ImportJournalDocument,p_ImportJournalStatus,p_BatchNumber ,p_BBatchReference,p_UUser,p_TransferStatus,p_TransferDate,p_ManualImport,p_REFCNTD,p_REFCNTU)';

COMMIT;
Re: Dynamically pass table name in PLSQL [message #685469 is a reply to message #685468] Sat, 22 January 2022 13:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Column name can't have an alias, here:
T$TRST,
T$TRD T,    --> remove trailing "T"
T$MAIM,
Re: Dynamically pass table name in PLSQL [message #685482 is a reply to message #685468] Sun, 23 January 2022 21:44 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
In addition, the variables are not accessible directly in the dynamic statement. You should pass them e.g. by binding:
execute immediate 'INSERT INTO '|| p_tablename ||'(T$IDNO, T$TEDT, <other_columns>) VALUES (:p1, :p2, <other_placeholders>)'
  using p_identificationnumber, p_transactiondate, <other_variables>;
You may find more about binding in dynamic SQL including examples in the Oracle documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-30ADFC05-8DFB-4C8A-831D-426C2E97F217
Re: Dynamically pass table name in PLSQL [message #685483 is a reply to message #685482] Sun, 23 January 2022 23:47 Go to previous messageGo to next message
345678
Messages: 3
Registered: January 2022
Junior Member
Thanks a lot, it worked for me.

Can you please help me with one more stored procedure also , I am passing table name dynamically to delete the rows based on Identification number .
But ,I'm getting this error "ORA-00933: SQL command not properly ended".

code snippet:

CREATE OR REPLACE PROCEDURE PRC_DELETEBAANTTXGLD
(p_IdentificationNumber IN VARCHAR2,
P_TableName1 IN VARCHAR2,
p_TableName2 IN VARCHAR2,
RETURN_CODE OUT NUMBER,
RETURN_MSG OUT VARCHAR2)
AS
BEGIN
execute immediate 'DELETE FROM ' || P_TableName1 || 'WHERE T$IDNO= :p_IdentificationNumber'
using p_IdentificationNumber;
execute immediate 'DELETE FROM ' || P_TableName2 || 'WHERE T$IDNO= :p_IdentificationNumber'
using p_IdentificationNumber;
COMMIT;
RETURN_CODE := 1;
RETURN_MSG := 'IdentificationNumber:'||p_IdentificationNumber||' '||'Successfully deleted from LN Staging Tables';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN_CODE := sqlcode;
RETURN_MSG := 'IdentificationNumber:'||p_IdentificationNumber||' '||sqlerrm;
END;




Re: Dynamically pass table name in PLSQL [message #685484 is a reply to message #685483] Mon, 24 January 2022 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.
If you don't know how to format the code, learn it using SQL Formatter.

Re: Dynamically pass table name in PLSQL [message #685485 is a reply to message #685483] Mon, 24 January 2022 00:44 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You may benefit from writing down the constructed string (e.g. using DBMS_OUTPUT) before executing it.

In this case, you would see something like this (as I do not know exact name of your table):
DELETE FROM TableName1WHERE T$IDNO= :p_IdentificationNumber
Can you spot the missing space before WHERE? Analogically, the space is explicitly stated in the constructed MSG a few rows after.
Re: Dynamically pass table name in PLSQL [message #685487 is a reply to message #685485] Mon, 24 January 2022 03:16 Go to previous message
345678
Messages: 3
Registered: January 2022
Junior Member
Thank you , I have added space before where condition , It worked fine

execute immediate 'DELETE FROM ' || P_TableName1 || ' WHERE T$IDNO= :p_IdentificationNumber'
using p_IdentificationNumber;
execute immediate 'DELETE FROM ' || P_TableName2 || ' WHERE T$IDNO= :p_IdentificationNumber'
using p_IdentificationNumber;
Previous Topic: PL/SQL escaping variable with a single quote
Next Topic: Hex Value to Base64 Value Convertion
Goto Forum:
  


Current Time: Thu Mar 28 12:37:40 CDT 2024