Oracle Application Express (APEX)


ORA-06550 with PL/SQL code process


Hi I have a process hard-coded locally in a page. P74_TABLE and P74_CLEF_VAR are items onf the page. 
insert into  V('P74_TABLE') (CLEF_VAR, DATE1, VALEUR)  
SELECT NV('P74_CLEF_VAR'), DATE1, REPLACE(VALEUR, ''',''', '''.''') FROM TELEVERSEMENT ;
 I do get an error when checking the code in the PL/SQL editor :ORA-06550: line 1, column 79: PL/SQL: ORA-00928: missing SELECT keyword ORA-06550: line 1, column 64: PL/SQL: SQL Statement ignored Thank you for your help.
Does that work? Shouldn't it be simply INSERT INTO P74_TABLE (Clef_Var, Date1, Valeur) SELECT NV ('P74_CLEF_VAR'), date1, REPLACE(VALEUR, ''',''', '''.''') FROM TELEVERSEMENT ;   ?
Chipniz wrote:
 
I have a process hard-coded locally in a page. P74_TABLE and P74_CLEF_VAR are items onf the page.
 
insert into  V('P74_TABLE') (CLEF_VAR, DATE1, VALEUR)     SELECT NV('P74_CLEF_VAR'), DATE1, REPLACE(VALEUR, ''',''', '''.''') FROM TELEVERSEMENT ; 
I do get an error when checking the code in the PL/SQL editor :
 
ORA-06550: line 1, column 79: PL/SQL: ORA-00928: missing SELECT keyword ORA-06550: line 1, column 64: PL/SQL: SQL Statement ignored
Assuming that you are trying to dynamically insert into a table identified by the value of the P74_TABLE page item, that is invalid SQL syntax and will not work. A solution using dynamic SQL would be possible, but this approach is more likely to suggest that there is something far wrong with your data model, application design, or both. What problem are you actually trying to solve?
Hi, I want to insert into a table, which name is "dynamic", its name is contained into the item P74_TABLE. One of the column of the table will be filled up with a number contained into the item P74_CLEF_VAR. The result of the PL/SQL Code process should be for example a statement like that : insert into  EVV_T004 (CLEF_VAR, DATE1, VALEUR)    SELECT 5331, DATE1, REPLACE(VALEUR, ''',''', '''.''') FROM TELEVERSEMENT ; Thank you for your suggestion about having a better approach to muy data model.
Chipniz wrote:
 
I want to insert into a table, which name is "dynamic", its name is contained into the item P74_TABLE.
That isn't the problem, it's what you suppose to be the solution. Why are you trying to insert into different tables dynamically? What problem do you think this will solve? Why would the table name not be known and fixed at design time?
Hello fac586 ! I did not explained all the background of my APEX application. I am building a data load feature using the dataload wizzard. Users will be able to download datas into tables. I need to compute the table where the data will be loaded because they will not download data into a fixed table but into one of the hundreds of table of the schema. So this table is not know in advance. The user will provide a kind of key, and my application from this key will retrieve the name of the table corresponding to this key. And the data will be inserted into this table. The table "TELEVERSEMENT" is a table used for loading the data. After that, the data will be inserted into the table (which name will be computed) from this table.Hope I am clear, english is not my mother tongue.
Hi, You can try following in your PL SQL code: 
DECLARE
   l_query   CLOB;
BEGIN
   l_query :=
         q'! insert into  !'
      || :p74_table
      || q'! (CLEF_VAR, DATE1, VALEUR) SELECT NV('!'
      || :p74_clef_var
      || q'!'), DATE1, REPLACE(VALEUR, ''',''', '''.''') FROM TELEVERSEMENT;!';
   EXECUTE IMMEDIATE l_query;
END;
 -- Sunil Bhatia
Thank you.
Chipniz wrote:
 
I did not explained all the background of my APEX application. I am building a data load feature using the dataload wizzard. Users will be able to download datas into tables. I need to compute the table where the data will be loaded because they will not download data into a fixed table but into one of the hundreds of table of the schema. So this table is not know in advance. The user will provide a kind of key, and my application from this key will retrieve the name of the table corresponding to this key. And the data will be inserted into this table. The table "TELEVERSEMENT" is a table used for loading the data. After that, the data will be inserted into the table (which name will be computed) from this table.
Hope I am clear, english is not my mother tongue.
The table name "EVV_T004" hints at the existence of tables EVV_T001, EVV_T002, EVV_T003, and more. The fixed set of columns in the insert suggests that they all have the same structure. The question is why there are "hundreds" of these tables in the schema? The existence of multiple tables with the same structure is indicative of data model problems.
Hi fac586 ! You are pefectly right when you notice they all get the same tructure : 3 columns : CLEF_VAR, DATE1, VALEUR. And we get lot of tables with names like EVV_T01, EVV_T02, etc.... The DB is a real-time 24/24 DB. We are a government company in France and we are managing a River, "L'Orge". We receive more than 5000 different datas coming from equipments  measuring real-time datas like height of the water in the river, rate of flow of the water, chemical datas like Ph of the water, rain gauges, etc...These datas are automatically inserted into tables with the help of a trigger. The date is recorded, the key of the measurement and the value of the measurement. Each table correspond to an equipment. Each equipment can send up to hundreds of different measurements. So each table contains all the measurements of an equipment.  I may understand that our model is not a good approach but it is robust, flexible and meet our needs. Why asking for more ?

Related Links

Possible error: APEX USER GUIDE 2.0, Page 13-2, Para 1, Section 4
Help ::Object Query Langauge
Sample XE Drill down report
Creating a process button in PL/SQL
Excel Upload.
The script exceeds the maximum size allowed.
How to pass a LOV variable to javascript?
Update textfiled based on onChange event AND select
If it is stupid question,I am sorry:How to Import a page to my application?
HTMLDB Cookie and clear text USERNAME
apex.oracle.com
PAGE BLINK INTERMITTENT
Selectable columns on report
Trying to import Amazon example amazonstore_1.6.sql script
Express Database edition
Passing Session Values to the Tabular Form Element of a Report Column

Categories

ORACLE DEV
WebCenter
Business Intelligence
Identity Management
Performance & Availabi...
Database Mobile Server...
LCM: 11i Install/Upgrade
Recovery Manager (RMAN)
ODP.NET
Business Intelligence ...
Oracle Application Exp...
Human Capital Manageme...
Customers
Real-Time Collaboratio...
Scheduler
Calendar Web Services ...
JD Edwards EnterpriseO...
.NET Stored Procedures
Secure Backup
EJB (Korean)
Python
SCM - Asset Lifecycle ...
SQR Reporting
CRM On Demand Analytic...
Upgrades
On Demand: SaaS and Ma...
WebLogic Server - JDBC
ACE Director - Applica...
WebLogic Server/Java E...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
WebLogic Event Server/...
weblogic.network.gatek...
SQL Developer Data Mod...
Business Intelligence ...
Evolve
Design Pattern (Chinese)
WebLogic Server - Perf...
Oracle Policy Automation
Performance Management...
Java APIs
New To Java
JMX
Solaris Zones
Algorithms
Developing Application...
Internationalization (...
Java Desktop Applications
Java Plug-In
DTrace
Sun Update Connection
Java Programming [Arch...
E-mail, Calendar, & Co...
Sun ONE Integration Se...
データベース
エンタープライズ管理
バックアップ・リカバリ
EPM System Infrastructure
Windows .NET (Korean)
Developer [ARCHIVE]
Israel Oracle User Gro...
Build
Java Sessions
Oracle Fusion Middlewa...
Arena
Bulk API
Twin Cities Eloqua Use...
Philadelphia PA Eloqua...
Eloqua Users with MS C...
Seed Your Success
Markies: Extraordinary...
Privacy and Compliance
Canadian B2B Marketers
SRM/Eloqua users
OKM
The Hangout
Java SE Early Access
JavaOne
Manufacturing Intellig...
BICS Remote Data Conne...
Software in Silicon (S...
Data Visualization Des...
Brasil
NightHacking
See It
Web Cache and Edge Sid...
Financials
Exchange
Berkeley DB
JD Edwards World Gener...
Essbase Analytics
Oracle VM Server for x86
weblogic.SIP.server
Primavera Risk Analysi...
Oracle Cloud - General...
Oracle HCM Cloud Service
Parallel Graph Analytics
Oracle Storage Cloud S...
Test Partner Industry ...

Resources

Encrypt Message



code
soft
python
ios
c
html
jquery
cloud
mobile