Home » SQL & PL/SQL » SQL & PL/SQL » pl sql insert (oracle 10g r2)
pl sql insert [message #337956] Fri, 01 August 2008 13:07 Go to next message
tanneyman13
Messages: 1
Registered: August 2008
Junior Member
I have written the following procedure and it does not throw any errors upon compiling. I have also run the select statements independtly of this pl sql statement to ensure that they are returning data and they are. The problem is that when I run this statement no data gets inserted into the table. I have a feeling I have the syntax wrong for the insert statement?
CREATE OR REPLACE PROCEDURE find_uid_time
AS
	v_logical_path		valid_proj.logical_path%TYPE;
	v_domain		valid_proj.domain%TYPE;
	v_usid			file_metadata.usid%TYPE;
	v_atime			file_metadata.atime%TYPE;
	v_mtime			file_metadata.mtime%TYPE;
	v_ctime			file_metadata.ctime%TYPE;

	CURSOR cur_prj_list IS

		SELECT	logical_path,
			domain
		FROM	valid_proj;

BEGIN
	OPEN cur_prj_list;
	LOOP
		FETCH cur_prj_list INTO v_logical_path, v_domain;
	EXIT WHEN cur_prj_list%NOTFOUND;
	
	INSERT INTO users_temp(logical_path, usid, domain, atime, mtime, ctime) 
		
		SELECT	logical_path,
			usid,
			domain,
			atime,
			mtime,
			ctime
		FROM	file_metadata
		WHERE	logical_path LIKE CONCAT(v_logical_path, '%')
		AND	domain = v_domain;

	END LOOP;
	CLOSE cur_prj_list;

END;
/

[EDITED by LF: added [code] tags to preserve formatting]

[Updated on: Fri, 01 August 2008 14:12] by Moderator

Report message to a moderator

Re: pl sql insert [message #337957 is a reply to message #337956] Fri, 01 August 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Nice try but PL/SQL does not work the way you think it should.
One possibility is to use "bind variables".
Another is to use "EXECUTE IMMEDIATE"

Plenty of examples for each type exist on this site & via GOOGLE;
or visit http://asktom.oracle.com
Re: pl sql insert [message #337960 is a reply to message #337957] Fri, 01 August 2008 14:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It might be that I'm getting too tired but I don't see the use for binds here.
It looks to me like this boils down to a simple insert:
insert into users_temp (logical_path, usid, domain, atime, mtime, ctime)
SELECT fma.logical_path,
fma.usid,
fma.domain,
fma.atime,
fma.mtime,
fma.ctime
FROM file_metadata fma
,    valid_proj    vpj
WHERE fma.logical_path LIKE CONCAT(vpjlogical_path, '%')
AND fma.domain = vpj.domain;


If I am right, you might be missing a commit in your original code, but the above single SQL is way easier (it also needs a commit, by the way)
Re: pl sql insert [message #337962 is a reply to message #337957] Fri, 01 August 2008 14:10 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
I tried with emp table. it works well.

SQL> select count(*) from emp1;

  COUNT(*)
----------
         0
SQL> declare
  2  v_ename emp.ename%type;
  3  CURSOR cur_prj_list IS
  4  SELECT ename
  5  FROM emp where rownum < 5;
  6  
  7  BEGIN
  8  OPEN cur_prj_list;
  9  LOOP
 10  FETCH cur_prj_list INTO v_ename;
 11  EXIT WHEN cur_prj_list%NOTFOUND;
 12  
 13  INSERT INTO emp1(empno,ename)
 14  
 15  SELECT empno,ename
 16  FROM emp
 17  WHERE ename LIKE CONCAT(v_ename, '%')
 18  and rownum <2;
 19  
 20  END LOOP;
 21  CLOSE cur_prj_list;
 22  
 23  END;
 24  /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp1;

  COUNT(*)
----------
         4

SQL> 


You may not have the data to match in where clause..
Re: pl sql insert [message #337964 is a reply to message #337956] Fri, 01 August 2008 14:16 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tanneyman13 wrote on Fri, 01 August 2008 20:07
I have written the following procedure ...

First of all, I agree with Frank - why would you want to involve PL/SQL into something that can easily be done using SQL?

Also, why did you declare variables in this procedure, but never used them? Or, did you use them in the original procedure (but decided to post only a part of it)? If so, is - perhaps - the missing part responsible for the problem?
Previous Topic: Ratio_to_report analytical function
Next Topic: order by letters then numbers
Goto Forum:
  


Current Time: Sat Dec 03 18:27:17 CST 2016

Total time taken to generate the page: 0.13533 seconds