Home » SQL & PL/SQL » SQL & PL/SQL » How to execute stored procedure in sql developer? (11g)
How to execute stored procedure in sql developer? [message #568221] Wed, 10 October 2012 05:09 Go to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi all,

sir i am running one stored procedure in sql developer it's compiled successfully

but when i am running this procedure i am not getting value in my table. i checked with break point it's going there but not inserting value.

i am thinking it's due to something wrong in stored procedure.

could you once convert it in oracle and check the flow.

here is my procedure in sql server.

Create PROCEDURE [dbo].[Sp_FetchEmployee]
 @userid int
AS
BEGIN
Declare 
@empid varchar(20),
@compid varchar(20)
truncate table employees
if exists (select 1 from user_list  where Access_level = 0  and User_id = @userid)    
   begin
   select @empid=Emp_ID from User_List Where User_id=@userid
   select @compid=Comp_ID from Employee where Emp_ID=@empid     
   insert into employees     
   select distinct Emp_id,@userid from employee Where Comp_ID=@compid 
   end    
if exists (select 1 from user_list  where Access_level = 2  and User_id = @userid)    
begin    
insert into employees     
select e.Emp_ID, @userid from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID    
where User_ID = @userid   
end    
if exists (select 1 from user_list  where Access_level = 1  and User_id = @userid)    
begin    
Declare @Emp_id varchar(50)    
select @Emp_id = e.Emp_id from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID    
where User_ID = @userid


--insert @employees select e.Emp_id,User_ID from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID    
--where User_ID = @user_id 
  
    
insert into employees     
--select Emp_ID,@user_id from employee  where Emp_ID = @Emp_id    
--union     
select Emp_ID, @userid from employee where Managerid = @Emp_id and status=1    
end    
	

END

thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568223 is a reply to message #568221] Wed, 10 October 2012 05:13 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Hello,

Quote:

could you once convert it in oracle and check the flow.


But How ?

Veeresh

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568224 is a reply to message #568221] Wed, 10 October 2012 05:14 Go to previous messageGo to next message
Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'd rather post Oracle version of the procedure. Now you have to wait for someone who knows both Oracle and SQL Server, his/her good will to convert the above code to Oracle and, hopefully, find the culprit. On the other hand, with Oracle code in front of us, someone might notice what's wrong in a matter of minutes.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568229 is a reply to message #568223] Wed, 10 October 2012 05:22 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
sir here i am pasting my converted oracle stored procedure
so that you can do it.

Here is converted oracle stored procedure:

   create or replace
PROCEDURE Sp_FetchEmployee(
    v_userid IN NUMBER DEFAULT NULL )
AS
  v_empid  VARCHAR2(20);
  v_compid VARCHAR2(20);
  v_temp   NUMBER(1, 0);
BEGIN
  EXECUTE IMMEDIATE ' TRUNCATE TABLE employees ';
   BEGIN
    v_temp:=0;
  END;
  BEGIN
    SELECT 1
    INTO v_temp
    FROM DUAL
    IF Where EXISTS( SELECT 1 FROM user_list WHERE Access_level = 0 AND USER_ID = v_userid);
  --EXCEPTION
  --WHEN OTHERS THEN
    --NULL;
  END;
  IF v_temp = 1 THEN
    BEGIN
      SELECT Emp_ID INTO v_empid FROM User_List WHERE USER_ID = v_userid;
      SELECT Comp_ID INTO v_compid FROM Employee WHERE Emp_ID = v_empid;
      INSERT INTO employees(Empid) SELECT DISTINCT Emp_id  FROM employee WHERE Comp_ID = v_compid;
      
    END;
  END IF;
  BEGIN
    SELECT 1
    INTO v_temp
    FROM DUAL
    WHERE EXISTS
      ( SELECT 1 FROM user_list WHERE Access_level = 2 AND USER_ID = v_userid
      );
  --EXCEPTION
  --WHEN OTHERS THEN
    --NULL;
  END;
  IF v_temp = 1 THEN
    BEGIN
      INSERT
      INTO employees
        (SELECT e.Emp_ID ,
            v_userid
          FROM employee e
          INNER JOIN user_list ul
          ON ul.Emp_ID  = e.Emp_ID
          WHERE USER_ID = v_userid
        );
    END;
  END IF;
  BEGIN
    SELECT 1
    INTO v_temp
    FROM DUAL
    WHERE EXISTS
      ( SELECT 1 FROM user_list WHERE Access_level = 1 AND USER_ID = v_userid
      );
  --EXCEPTION
  --WHEN OTHERS THEN
    --NULL;
  END;
  IF v_temp = 1 THEN
    DECLARE
      v_Emp_id VARCHAR2(50);
    BEGIN
      SELECT e.Emp_id
      INTO v_Emp_id
      FROM employee e
      JOIN user_list ul
      ON ul.Emp_ID  = e.Emp_ID
      WHERE USER_ID = v_userid;
      --insert @employees select e.Emp_id,User_ID from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID
      --where User_ID = @user_id
      INSERT
      INTO employees
        (
          --select Emp_ID,@user_id from employee  where Emp_ID = @Emp_id
          --union
          SELECT Emp_ID ,
            v_userid
          FROM employee
          WHERE Managerid = v_Emp_id
          AND STATUS = 1
        );
    END;
  END IF;
END;


after converted i got many difference between that.
please complete at lease this one so that i will get the idea about this procedure.

thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568233 is a reply to message #568229] Wed, 10 October 2012 05:32 Go to previous messageGo to next message
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
Senior Member
This is not valid SQL,
SELECT 1
    INTO v_temp
    FROM DUAL
    IF Where EXISTS( SELECT 1 FROM user_list WHERE Access_level = 0 AND USER_ID = v_userid);
but overall, I can't see what you are trying to do. I suspect that you are trying to implement a SQL Server approach to a problem, that is really not applicable to the Oracle environment. If you are trying to populate some sort of "temporary" table on which you want to do further processing, forget it: Oracle does not have the read consistency issues one finds in SQL Server that make temporary tables necessary.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568234 is a reply to message #568233] Wed, 10 October 2012 05:35 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir as you can see i am not using there temporary table. this table is created named employees.

that's why i given you both oracle and sql server procedure to check where it is wrong.

please complete this procedure based on sql procedure so that i can do others easily.
thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568236 is a reply to message #568234] Wed, 10 October 2012 05:38 Go to previous messageGo to next message
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
Senior Member
I've already pointed out one syntax error. Perhaps you should fix that one, and then see if your code compiles. I'm not going to thatc for you: even if I wanted to, I couldn't, because you haven't provided the necessary CREATE TABLE statements.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568238 is a reply to message #568236] Wed, 10 October 2012 05:45 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
k sir here i am pasting my table scripts.

CREATE TABLE employees
(
empid VARCHAR2(20) ,
compid VARCHAR2(20)
);

please correct the error so that i can get some ideas about oracle stored procedure.

thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568246 is a reply to message #568238] Wed, 10 October 2012 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good readings for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568248 is a reply to message #568246] Wed, 10 October 2012 06:14 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
sir this i can do it because i am having internet. but due to time limitation i told you these things.
i have to complete this very urgent. if you correct the error it's good otherwise no problem.
thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568251 is a reply to message #568248] Wed, 10 October 2012 06:23 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
Can you post me user_list table script with data?

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568255 is a reply to message #568251] Wed, 10 October 2012 06:37 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll also need the script for employee table.

@vikashkrburnwal@gmail.com - how do you expect us to fix the code if we don't know what it's supposed to do?
Spotting syntax errors is (usually) easy enough, but knowing what the correct code should be requires more information.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568258 is a reply to message #568251] Wed, 10 October 2012 06:45 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir,

here is my user_list script with data

 CREATE TABLE USER_LIST
   (	"USER_ID" NUMBER(10,0), 
	"USER_NAME" VARCHAR2(20 CHAR), 
	"PASSWORD" VARCHAR2(20 CHAR), 
	"SITE_CODE" VARCHAR2(5 CHAR), 
	"ACCESS_LEVEL" NUMBER(10,0), 
	"LASTLOGIN" DATE, 
	"AVAILABILITY" NUMBER(10,0), 
	"EMP_ID" VARCHAR2(20 CHAR)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


REM INSERTING into AMPLEX_GRAND1.USER_LIST


SET DEFINE OFF;


Insert into AMPLEX_GRAND1.USER_LIST (USER_ID,USER_NAME,PASSWORD,SITE_CODE,ACCESS_LEVEL,LASTLOGIN,AVAILABILITY,EMP_ID) values (1,'admin','admin','TAMS',0,to_date('10-OCT-12','DD-MON-RR'),1,'00000001');


and here is my employee table data where it's fetching the emp_id and comp_id

 CREATE TABLE EMPLOYEE
   (	"EMP_ID" VARCHAR2(8 BYTE), 
	"EMP_FIRSTNAME" VARCHAR2(50 BYTE), 
	"EMP_LASTNAME" VARCHAR2(50 BYTE), 
	"CARD_ID" VARCHAR2(20 BYTE), 
	"COMP_ID" VARCHAR2(3 BYTE), 
	"CAT_CODE" VARCHAR2(50 BYTE), 
	"DOB" DATE, 
	"DOJ" DATE, 
	"DEPT_CODE" VARCHAR2(10 BYTE), 
	"DESIG_CODE" VARCHAR2(10 BYTE), 
	"ADDRESS" VARCHAR2(200 BYTE), 
	"PHONE" VARCHAR2(20 BYTE), 
	"CREATEDDATE" DATE, 
	"CREATEDBY" VARCHAR2(50 BYTE), 
	"MODIFIEDDATE" DATE, 
	"MODIFIEDBY" VARCHAR2(50 BYTE), 
	"ISMANAGER" NUMBER(1,0), 
	"MANAGERID" VARCHAR2(20 BYTE), 
	"EMP_EMAIL" VARCHAR2(50 BYTE), 
	"SHIFT_CODE" VARCHAR2(20 BYTE), 
	"MOBILE" VARCHAR2(10 BYTE), 
	"STATUS" NUMBER(10,0), 
	"TERMDATE" DATE, 
	"GENDER" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;



REM INSERTING into AMPLEX_GRAND1.EMPLOYEE


SET DEFINE OFF;


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000050','Anand','M','154278455','003','003',null,null,'003','003','Bng','9548745214',null,'Admin',null,null,0,'00000061','anand@g mail.com','SG00','6584715424',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000008','Madhu','sudhan','2145788','001','001',null,null,'001','001','bangalore','9591087134',null,'Admin',null,null,0,'00000001' ,'mmmadhusoodhan@gmail.com','SN00','9591087134',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000009','ragesh','s','85984578','001','001',null,null,'004','001','bangalore',null,null,'Admin',null,null,0,'00000001','mmmadhuso odhan@gmail.com','SG00',null,1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values ('00000001','Satya Narayana','M  V','00000001','001','001',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'001','001','Bangalore','9857456321',null ,'admin',null,'admin',1,'select1','SatyaIcard@gmail.com','SN00','9568546584',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values ('00000033','Chandra  Sekharan','K','0000003','001','001',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'001','004','Bangalore','789456 1234',null,'admin',null,'admin',0,'00000001','chandra@gmail.com','SG00','7878765463',0,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000055','Sagar','Deshpande','00000055','002','002',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'002','002', 'Delhi','7894561230',null,'admin',null,'admin',1,'Selec','Sagar@gmail.com','SG00','7456456798',0,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values ('00000061','Kiran','Kumar  N','00000061','003','003',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'003','003','Bangalore','8888888888',null ,'admin',null,'admin',1,' Selec','mmmadhusoodhan@gmail.com','SG00','8958745874',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000038','Chandan','Sharma','00000038','002','002',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'002','002',' Mysore','8547852456',null,'admin',null,'admin',0,'00000055','chandan@yahoo.com','SG00','8465464787',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000043','Manjunath','S','00000043','002','002',null,null,'002','002','Tumkur','8574521456',null,'admin',null,'admin',0,'00000061' ,'manju@yahoo.com','SG00','9446486458',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000035','Kanak','Raju','00000035','001','001',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'001','004','Mysore','7895456245',null,'admin',null,'admin',0,'00000001','raju@gmail.com','SG00','7546546546',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000057','Sikandar','Pasha','00000057','003','003',to_date('10-OCT-12','DD-MON-RR'),null,'003','003','Bangalore','7894561233',null ,'admin',null,'admin',0,'00000061','Sikandar@gmail.com','SG00','3456456465',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000063','Swetha','A','00000063','003','003',null,null,'003','003','Bangalore','8547852144',null,'admin',null,'admin',0,'00000061' ,'swetha@gmail.com','SG00','7456478545',1,null,'Female');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('EMP00005','spoorthy','A','00005','001','004',null,null,'001','001','Bangalore','9590991410',null,'admin',null,null,0,'00000001','ss ss@gmail.com','SG00','9568745854',1,null,'Female');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000888','Vikash','Kumar','154587545','001','088',null,null,'001','004','Bangalore','9590991710',null,'admin',null,null,0,'0000000 1','vikashkrburnwal@gmail.com','SG00','4545454245',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000022','Hemanth','g','225566','003','003',null,to_date('10-OCT-12','DD-MON-RR'),'003','003','Bangalore','2222222222',null,'admin ',null,null,0,'00000061','madhucs42@gmail.com','SN00','7777777777',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000077','vikash','kumar','0454545','002','005',to_date('10-OCT-12','DD-MON-RR'),null,'002','002','bangalore','9590991711',null,'a dmin',null,null,0,' Selec','vikashkrburnwal@gmail.com','SG00','9590114574',0,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000078','Vicky','Kumar','45454545','001','001',to_date('10-OCT-12','DD-MON-RR'),null,'001','004','Bangalore','5665446565',null,'a dmin',null,null,0,'00000001','vikashkrburnwal@gmail.com','SG00','5956565655',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('12154545','vifkjfkj','fjfkdjk','45454554','002','002',to_date('10-OCT-12','DD-MON-RR'),null,'002','002','fdfddfdf','4545454545',nul l,'Admin',null,null,0,'Selec',null,'SG00','4545454545',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000003','Madhusudhan','k','54687566','003','003',to_date('10-OCT-12','DD-MON-RR'),null,'003','003','Bng','9591087134',null,'admin ',null,null,0,'00000061','madhu@gmail.com','SN00','9591874512',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000050','Anand','M','154278455','003','003',null,null,'003','003','Bangalore','9548745214',null,'Admin',null,null,0,'00000061','a nand@gmail.com','SG00','6584715424',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000008','Madhu','sudhan','2145788','001','001',null,null,'001','001','bangalore','9591087134',null,'Admin',null,null,0,'00000001' ,'mmmadhusoodhan@gmail.com','SN00','9591087134',1,null,'Male');


Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000009','ragesh','s','85984578','001','001',null,null,'004','001','bangalore',null,null,'Admin',null,null,0,'00000001','mmmadhuso odhan@gmail.com','SG00',null,1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000018','Shantanu','Kumar','4545454','003','003',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'003','003','B angalore','5444545454',null,'Admin',null,null,0,'00000061','shantanu@gmail.com','SG00','9590991710',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000019','Vikash','Kumar','4512687933','001','004',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'004','004',' Bangalore','9590991711',null,'Admin',null,null,0,'00000001','vikas@securax.in','SG00','4545454545',1,null,'Male');



Insert into AMPLEX_GRAND1.EMPLOYEE  (EMP_ID,EMP_FIRSTNAME,EMP_LASTNAME,CARD_ID,COMP_ID,CAT_CODE,DOB,DOJ,DEPT_CODE,DESIG_CODE,ADDRESS,PHONE,CREATEDDATE,CREATEDBY,MODIFIED DATE,MODIFIEDBY,ISMANAGER,MANAGERID,EMP_EMAIL,SHIFT_CODE,MOBILE,STATUS,TERMDATE,GENDER) values  ('00000020','Rocky','Kumar','4138545457','001','004',to_date('10-OCT-12','DD-MON-RR'),to_date('10-OCT-12','DD-MON-RR'),'004','004','B angalore','4545454545',null,'Admin',null,null,1,'00000001','rocky@securax.in','SG00','9590991710',1,null,'Male');



if any issues let me know

thanks

[Updated on: Wed, 10 October 2012 06:51] by Moderator

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568260 is a reply to message #568258] Wed, 10 October 2012 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I see at least one:

Quote:
@vikashkrburnwal@gmail.com - how do you expect us to fix the code if we don't know what it's supposed to do?


Regards
Michel

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568261 is a reply to message #568260] Wed, 10 October 2012 06:52 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
sir i given you both code in sql and oracle even i given you all scripts with data also so you can't get wat's happening there.
k i am telling you it's checking the condition based on user_id value which i passed in stored procedure if it is one then it's checking in user_list table user_id if it is matching to this then it's going inside and taking the empid from employee table which has companyid related to that employee and storing in employees table.
that's it.

thanks

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568262 is a reply to message #568260] Wed, 10 October 2012 06:53 Go to previous messageGo to next message
ramoradba
Messages: 2427
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please format your Queries by using Instant SQL Formatter

Sriram Sanka

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568264 is a reply to message #568261] Wed, 10 October 2012 07:23 Go to previous messageGo to next message
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
Senior Member
I have to apologize for suggesting you had a syntax error, that ...IF WHERE... construct does work (I've never seen it before) and the procedure does compile.
Your INSERTs into EMPLOYEE all fail, I think because of line breaks which I'm not going to fix for you.

What is the error you are getting?

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568265 is a reply to message #568264] Wed, 10 October 2012 07:28 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@vikashkrburnwal@gmail.com
if i pass the userid value is 1 there is no record for the comp_id..

Framed proc...
Create OR REPLACE PROCEDURE proc1 (v_userid in number) is
v_empid varchar(20);
v_comp_id varchar(20);

v_counter1 number;
v_counter2  number;
v_counter3  number;

BEGIN

execute immediate 'truncate table employees';

select 1 into v_counter1  from user_list  where Access_level = 0  and User_id = v_userid;

if v_counter1 >0 then     
   begin
   select emp_id  into v_empid from User_List Where User_id = v_userid;
   select comp_id into v_Comp_ID from Employee where Emp_ID = v_empid;
   
   dbms_output.put_line();
   
   insert into employees (Emp_id,comp_id) select distinct Emp_id, v_userid from employee Where Comp_ID= v_Comp_ID;
   
   --alter table employees modify emp_id varchar2(100)  
    
     
   end;
end if;
select 1 into v_counter2 from user_list  where Access_level = 2  and User_id = v_userid;

if v_counter2>0 then
     
begin    
insert into employees( emp_id,comp_id)  select e.Emp_ID,v_userid from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID    
where User_ID = v_userid;   
end;    
end if;

select 1 into v_counter3 from user_list  where Access_level = 1  and User_id = v_userid;

if v_counter2>0 then 
begin
select e.Emp_id into v_empid from employee e inner join user_list ul on ul.Emp_ID = e.Emp_ID    
where User_ID = v_userid;
   
insert into employees(emp_id,comp_id) select Emp_ID, v_userid from employee where Managerid = Emp_id and status=1;    
end;
end if;    
    
end;
/

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568266 is a reply to message #568264] Wed, 10 October 2012 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
The if works because oracle is treating it as a table alias. It is not recognizing it as a keyword.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568268 is a reply to message #568265] Wed, 10 October 2012 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@muralikri

What a bad (Oracle) code.
Never heard about join and/or correlated subquery?

Regards
Michel

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568271 is a reply to message #568266] Wed, 10 October 2012 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
@muralikri - you're not checking the value of v_counter3 at any point.

I suspect this code code be simplified by selecting access_level from user_list and then doing an if statement based on it's value.

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568272 is a reply to message #568271] Wed, 10 October 2012 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
@vikashkrburnwal@gmail.com - do your tables have primary/unique keys?

Report message to a moderator

Re: How to execute stored procedure in sql developer? [message #568273 is a reply to message #568272] Wed, 10 October 2012 07:49 Go to previous message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
@muralikri - you're not using v_empid from the final select either.

Report message to a moderator

Previous Topic: How to execute function in sql developer
Next Topic: Preventing concurrent running of a stored procedure
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 23:14:07 CDT 2013

Total time taken to generate the page: 0.11230 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.