Home » SQL & PL/SQL » SQL & PL/SQL » dynamic copy with PLSQL
dynamic copy with PLSQL [message #281219] Fri, 16 November 2007 03:08 Go to next message
thorod
Messages: 3
Registered: November 2007
Junior Member
First of all, I am sorry of my English Embarassed

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 #281228 is a reply to message #281219] Fri, 16 November 2007 03:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
thorod wrote on Fri, 16 November 2007 10:08

First of all, I am sorry of my English Embarassed



Nothing to worry about, as long as one of us can understand what you're asking it's fine.

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 ;

Re: dynamic copy with PLSQL [message #281229 is a reply to message #281228] Fri, 16 November 2007 03:35 Go to previous messageGo to next message
thorod
Messages: 3
Registered: November 2007
Junior Member
Quote:


The syntax is wrong, it should read:
INSERT INTO APPLICATION VALUES new_app ;



I changed it and got the following message:

This INSERT statement requires VALUES clause containing a parenthesised list of values

[Updated on: Fri, 16 November 2007 03:36]

Report message to a moderator

Re: dynamic copy with PLSQL [message #281232 is a reply to message #281229] Fri, 16 November 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of telling what you get, copy and paste what you get.

Syntax INSERT VALUES requires values are between ().

In addition:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: dynamic copy with PLSQL [message #281245 is a reply to message #281228] Fri, 16 November 2007 03:56 Go to previous messageGo to next message
thorod
Messages: 3
Registered: November 2007
Junior Member
MarcS wrote on Fri, 16 November 2007 03:29

thorod wrote on Fri, 16 November 2007 10:08

First of all, I am sorry of my English Embarassed



Nothing to worry about, as long as one of us can understand what you're asking it's fine.

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 ;




sorry, forgot my Oracle version, it is 10g

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


Re: dynamic copy with PLSQL [message #281251 is a reply to message #281245] Fri, 16 November 2007 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The message is clear.
Have a look at INSERT syntax.

Regards
Michel
Re: dynamic copy with PLSQL [message #281259 is a reply to message #281251] Fri, 16 November 2007 04:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Fri, 16 November 2007 11:13

The message is clear.
Have a look at INSERT syntax.

Regards
Michel



Don't forget this as well.
The original question stated that the insert statement is called from within PL/SQL
Re: dynamic copy with PLSQL [message #281403 is a reply to message #281245] Fri, 16 November 2007 13:15 Go to previous message
Barbara Boehmer
Messages: 8625
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

Previous Topic: Pls -000306
Next Topic: Create Directory
Goto Forum:
  


Current Time: Sun Dec 04 16:41:00 CST 2016

Total time taken to generate the page: 0.10412 seconds