dynamic copy with PLSQL [message #281219] |
Fri, 16 November 2007 03:08 |
thorod
Messages: 3 Registered: November 2007
|
Junior Member |
|
|
First of all, I am sorry of my English
My problem is, that I cannot copy values from a %ROWTYPE to a table in a dynamic way. It is necessary, because I would like to call a stored procedure from java, that copies my %ROWTYPE, that changes in a database are considered and I do not have to change the source code each time my table is changed.
-- procedure
CREATE OR REPLACE PROCEDURE copyApplication(AppID NUMBER, newName VARCHAR2) AS
new_app APPLICATION%ROWTYPE;
BEGIN
--fill rowtype with new data --> works
SELECT * INTO new_app FROM APPLICATION WHERE APP_ID=AppID;
--change name because of constraint --> works
new_app.app_name := newName;
--insert rowtype in database --> does not work
->statement does not work, because it is not possible
--to get access of all coulumns, do you know the
--solution?
--INSERT INTO APPLICATION SELECT * FROM new_app.VALUES;
-- It is possible to get information from %ROWTYPE
-- by indicating the name of column. The problem is,
-- that changes in the database are not considered
dbms_output.put_line('Name: ' || new_app.app_name);
dbms_output.put_line('Desc: ' || new_app.app_description);
END copyApplication;
--test
SET serveroutput ON;
BEGIN
copyApplication(1,'Test');
END;
[Updated on: Fri, 16 November 2007 03:50] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: dynamic copy with PLSQL [message #281403 is a reply to message #281245] |
Fri, 16 November 2007 13:15 |
|
Barbara Boehmer
Messages: 9100 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
thorod wrote on Fri, 16 November 2007 01:56 |
MarcS wrote on Fri, 16 November 2007 03:29 |
thorod wrote on Fri, 16 November 2007 10:08 |
--INSERT INTO APPLICATION SELECT * FROM new_app.VALUES;
|
The syntax is wrong, it should read:
INSERT INTO APPLICATION VALUES new_app ;
|
I changed it and got:
PROCEDURE DBASE.COPYAPPLICATION
On line: 17
PLS-00518: This INSERT statement requires VALUES clause containing a parenthesised list of values
|
It works for me, so you must have done something wrong:
SCOTT@orcl_11g> CREATE TABLE application
2 (app_id NUMBER,
3 app_name VARCHAR2 (8),
4 app_description VARCHAR2 (15),
5 CONSTRAINT application_pk PRIMARY KEY (app_id))
6 /
Table created.
SCOTT@orcl_11g> CREATE SEQUENCE application_seq
2 /
Sequence created.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER application_bir
2 BEFORE INSERT ON application
3 FOR EACH ROW
4 BEGIN
5 SELECT application_seq.NEXTVAL INTO :NEW.app_id FROM DUAL;
6 END application_bir;
7 /
Trigger created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO application (app_name, app_description) VALUES ('app1', 'desc1')
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO application (app_name, app_description) VALUES ('app2', 'desc2')
2 /
1 row created.
SCOTT@orcl_11g> SELECT * FROM application
2 /
APP_ID APP_NAME APP_DESCRIPTION
---------- -------- ---------------
1 app1 desc1
2 app2 desc2
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE copyApplication
2 (AppID NUMBER,
3 newName VARCHAR2)
4 AS
5 new_app APPLICATION%ROWTYPE;
6 BEGIN
7 SELECT * INTO new_app FROM APPLICATION WHERE APP_ID = AppID;
8 new_app.app_name := newName;
9 INSERT INTO application VALUES new_app;
10 END copyApplication;
11 /
Procedure created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SET serveroutput ON
SCOTT@orcl_11g> EXEC copyApplication (1, 'Test');
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM application
2 /
APP_ID APP_NAME APP_DESCRIPTION
---------- -------- ---------------
1 app1 desc1
2 app2 desc2
3 Test desc1
SCOTT@orcl_11g> ALTER TABLE application ADD (another_column VARCHAR2 (15))
2 /
Table altered.
SCOTT@orcl_11g> UPDATE application SET another_column = 'some data'
2 /
3 rows updated.
SCOTT@orcl_11g> EXEC copyApplication (2, 'Test2');
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM application
2 /
APP_ID APP_NAME APP_DESCRIPTION ANOTHER_COLUMN
---------- -------- --------------- ---------------
1 app1 desc1 some data
2 app2 desc2 some data
3 Test desc1 some data
4 Test2 desc2 some data
SCOTT@orcl_11g> spool off
|
|
|