OraFAQ Forum: SQL & PL/SQL » Dynamically pass table name in PLSQL

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically pass table name in PLSQL (Oracle client 18.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamically pass table name in PLSQL [message #685468]

Sat, 22 January 2022 07:35

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;

Attachment: prc_AxtoLnLedger.txt
(Size: 2.36KB, Downloaded 268 times)

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685469 is a reply to message #685468]

Sat, 22 January 2022 13:17

Littlefoot
Messages: 21760
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,

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685482 is a reply to message #685468]

Sun, 23 January 2022 21:44

flyboy
Messages: 1901
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

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685483 is a reply to message #685482]

Sun, 23 January 2022 23:47

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;

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685484 is a reply to message #685483]

Mon, 24 January 2022 00:28

Michel Cadot
Messages: 68421
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.

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685485 is a reply to message #685483]

Mon, 24 January 2022 00:44

flyboy
Messages: 1901
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.

Report message to a moderator

Re: Dynamically pass table name in PLSQL [message #685487 is a reply to message #685485]

Mon, 24 January 2022 03:16

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;

Report message to a moderator

Previous Topic:	PL/SQL escaping variable with a single quote
Next Topic:	Hex Value to Base64 Value Convertion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 22:24:00 CDT 2023