Home » SQL & PL/SQL » SQL & PL/SQL » Why this procedure is behaving typically
icon4.gif  Why this procedure is behaving typically [message #215017] Fri, 19 January 2007 00:11 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi Here is my procedure

CREATE OR REPLACE PROCEDURE test_ncsqls( intable VARCHAR2, outtable VARCHAR2, nctable VARCHAR2, Target_system varchar2) as
Sql1 LONG := '';
Sql2 LONG := '';
Sql3 LONG := '';



Begin
sql1:=test_genins(intable=>intable,outtable=>outtable, nctable=>nctable);
sql2:=test_gendel(intable=>intable,outtable=>outtable, nctable=>nctable);
sql3:=test_genupd(intable=>intable,outtable=>outtable, nctable=>nctable);

Delete from sys_nc_out_sqls where table_name=intable and target_system=target_system;
Insert into SYS_NC_OUT_SQLS (table_name, target_system, sql_string)
values (intable, target_system, 'isql1');
Insert into SYS_NC_OUT_SQLS (table_name, target_system, sql_string)
values (intable, target_system, 'isql2');
Insert into SYS_NC_OUT_SQLS (table_name, target_system, sql_string)
values (intable, target_system, 'sql3');

commit;


End test_ncsqls;

If I am passing parameters as
Exec test_ncsqls(intable=>'MST_ITEM',nctable=>'NC_OUT_ITEM_ORCL', outtable=>'TGT_ITEM_ORCL', target_system=>'orcl');

Output (When I querried a table select * from sys_nc_out_sqls)
Sys_nc_out_sqls
Table_name Target_system sql_string
MST_ITEM orcl isql1
MST_ITEM orcl isql2
MST_ITEM orcl sql3

If I am executing the same procedure with different parameters as
Exec test_ncsqls(intable=>'MST_ITEM',nctable=>'NC_OUT_ITEM_ORCL', outtable=>'TGT_ITEM_ORCL', target_system=>'abc');

Output (When I querried a table select * from sys_nc_out_sqls)
Its showing only 3 records instead of 6
as


Sys_nc_out_sqls
Table_name Target_system sql_string
MST_ITEM abc isql1
MST_ITEM abc isql2
MST_ITEM abc sql3

Instead of
Sys_nc_out_sqls
Table_name Target_system sql_string
MST_ITEM orcl isql1
MST_ITEM orcl isql2
MST_ITEM orcl sql3
MST_ITEM abc isql1
MST_ITEM abc isql2
MST_ITEM abc sql3

May I know where the mistake.
If we pass the same paramaeters it has to delete the existing rows and insert new rows fro that target system.
If we pass different parameters it has to insert new rows.

Can any one help me in doing this.

Thanks in advance
Re: Why this procedure is behaving typically [message #215060 is a reply to message #215017] Fri, 19 January 2007 03:11 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Any help really helpfull.
Re: Why this procedure is behaving typically [message #215062 is a reply to message #215060] Fri, 19 January 2007 03:16 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi Vikram,

Becuase of this statement , You try your procedure making comment of this statement :

Quote:
Delete from sys_nc_out_sqls where table_name=intable and target_system=target_system;


Bye
Ashu
Re: Why this procedure is behaving typically [message #215072 is a reply to message #215062] Fri, 19 January 2007 03:35 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Whats the problem in using delete statement overthere. As per requirement I am using. Is there any alternative to get the required solution. If so plese let me know.

I have already explained very clearly what my requirement is.

Any help can really helps me a lot
Re: Why this procedure is behaving typically [message #215075 is a reply to message #215017] Fri, 19 January 2007 03:54 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
As ab_trivedi said, the problem is this statement:

Delete from sys_nc_out_sqls where table_name=intable and target_system=target_system;

You have given your variable target_system the same name as a column on the table, so this statement is equivalent to:

Delete from sys_nc_out_sqls where table_name=intable and 1=1;

Rename the parameter to p_target_system i.e.

CREATE OR REPLACE PROCEDURE test_ncsqls( intable VARCHAR2, outtable VARCHAR2, nctable VARCHAR2, p_Target_system varchar2) as

..

Delete from sys_nc_out_sqls where table_name=intable and target_system=p_target_system;

[Updated on: Fri, 19 January 2007 03:56]

Report message to a moderator

Re: Why this procedure is behaving typically [message #215079 is a reply to message #215075] Fri, 19 January 2007 03:59 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi Vikram
have you got it now Laughing
Re: Why this procedure is behaving typically [message #215090 is a reply to message #215079] Fri, 19 January 2007 04:53 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hey thanks a lot. Really I struggled a lot in finding out this. Thanks for your clear explanation.
Re: Why this procedure is behaving typically [message #215092 is a reply to message #215017] Fri, 19 January 2007 04:57 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
In future, you should follow some standards in naming variables and parameters. Always prefix names to ensure they do not accidentally match the names of columns. The usual standard I adopt is to name parameters with a "p_" prefix and local variables with "v_".
Previous Topic: occurance of string
Next Topic: UTL_SMTP
Goto Forum:
  


Current Time: Tue Dec 06 06:47:16 CST 2016

Total time taken to generate the page: 0.08738 seconds