Solved

Stored procedure

Posted on 2011-03-24
44
548 Views
Last Modified: 2012-05-11
Hi

Can we pass table name as  varible name for following Store Procedure.  I want to pass table name as input parameter.

CREATE OR REPLACE procedure test(v_atc_id number,v_S_SI_group_id number,v_SS_V_ID number,
V_LAST__DATE timestamp,v_value  varchar2,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct atc_id,group_id,VARIABLE_ID from table1 where atc_id=v_atc_id and group_id=v_S_SI_group_id and VARIABLE_ID =v_SS_V_ID )loop
--row exists
update table1 set atc_id = v_atc_id, group_id=v_S_SI_group_id,VARIABLE_ID=v_SS_V_ID,last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE   group_id=v_S_SI_group_id and VARIABLE_ID =v_SS_V_ID;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO table1(Ss_ID,atc_id,GROUP_ID,VARIABLE_ID,LAST_DATE,S_VALUE)
    VALUES (p_id.nextval,v_atc_id,v_S_SI_group_id,v_SS_V_ID,V_LAST_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/
Thanks
0
Comment
Question by:vadicherla
  • 21
  • 20
  • 3
44 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35210317
You mean replace 'table1' in all the DML with a variable name?

Yes and no.  You can pass it in but you will need to use dynamic SQL to work with everything.

Once I understand what you are after we can work on a solution.
0
 

Author Comment

by:vadicherla
ID: 35210370
Yes table1 as variable name like p_table_name.

When i execute procedure i will pass table name as input parameter
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35210499
Below is a very simplified example of what I think you are wanting to do.

I'm having a hard time following the logic.  You want to select from a table, loop through the rows then possible 'INSERT' those rows back into that table?

This doesn't make sense to me.

Also: There is an Oracle MERGE command that does an 'upsert' function that could simplify your logic.  Once I understand what you are wanting to do, I can see if that will work better.
drop table tab1 purge;
create table tab1(group_id number, col1 char(1));
insert into tab1 values(1,'a');
commit;


CREATE OR REPLACE procedure myProc(v_S_SI_group_id number, p_table_name in varchar2)
is
	myCursor sys_refcursor;
	myGroupId	number;
begin
	open myCursor for ' select distinct group_id from ' || p_table_name || ' where group_id=:v_S_SI_group_id' using v_S_SI_group_id;

	loop
	fetch myCursor into myGroupId;
		exit when myCursor%NOTFOUND;
		dbms_output.put_line('Got: ' || myGroupId);
	end loop;
	close myCursor;

end;
/

show errors

exec myProc(1,'TAB1');
exec myProc(2,'TAB1');

Open in new window

0
 

Author Comment

by:vadicherla
ID: 35210748
my procedure is very simple.  if record exist in table it will update existing record  uisng update sql statement.  If Record Does not Exist it will insert as new Record into table.

I want to modify existing Stored Procedure to have table name as part of input statement.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35211239
See if this sample will get you started.
drop table tab1 purge;
create table tab1(group_id number, col1 char(1));
insert into tab1 values(1,'a');
commit;


CREATE OR REPLACE procedure myProc(v_S_SI_group_id number, p_table_name in varchar2)
is
	junk number;
begin
	begin
	execute immediate ' select 1 from ' || p_table_name || ' where group_id=:v_S_SI_group_id' into junk using v_S_SI_group_id;
	execute immediate 'update ' || p_table_name || ' set group_id=:v_S_SI_group_id, col1=:newCol1 ' using v_S_SI_group_id, 'W';
	exception when no_data_found then
		execute immediate 'insert into ' || p_table_name || ' values(:v_S_SI_group_id, ''Z'')' using v_S_SI_group_id;
	end;
		

end;
/

show errors

exec myProc(1,'TAB1');
exec myProc(2,'TAB1');

select * from tab1;

Open in new window

0
 

Author Comment

by:vadicherla
ID: 35211394
Getting below error when updating existing Records

SQL> exec myProc(2,'R','TAB1');
BEGIN myProc(2,'R','TAB1'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My Procedure is doing select statement on table to find the record in table and if record found it should update with values what i give in as inpurt parameter. See below

for r in(select distinct atc_id,group_id,VARIABLE_ID from table1 where atc_id=v_atc_id and group_id=v_S_SI_group_id and VARIABLE_ID =v_SS_V_ID )loop
--row exists
update table1 set atc_id = v_atc_id, group_id=v_S_SI_group_id,VARIABLE_ID=v_SS_V_ID,last_modified_date=v_last_modified_date,
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35211428
Why are you still doing a for loop?  It doesn't make sense.
It looks like you added a parameter to my procedure.

Please provide a test case similar to mine that I can run on my end.

It should include sample tables, data and expected results.  Feel free to expand my test case to add additional requirements.
0
 

Author Comment

by:vadicherla
ID: 35211682
update

i treid the your procedure but its updating all records instaed of single record. Based on below output  when we updating gorup id it should only update group id 1 but its updating all records
SQL> select * from TAB1;

  GROUP_ID C
---------- -
         1 Z
         2 Z
         3 Z

SQL> exec myProc(1,'TAB1');

PL/SQL procedure successfully completed.

SQL> select * from TAB1;

  GROUP_ID C
---------- -
         1 W
         1 W
         1 W
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35211725
You need to expand the where clause to include the correct columns based on input parameters.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35211858
Below is a modified procedure using the MERGE I mentioned earlier.

Just like above, you will need to add to the MATCHED section to include all your columns.

If you are unsure how to get it all working, I will need to complete test case also mentioned above.
drop table tab1 purge;
create table tab1(group_id number, col1 char(1));
insert into tab1 values(1,'a');
commit;

CREATE OR REPLACE procedure myProcMerge(v_S_SI_group_id number, p_col1 in char, p_table_name in varchar2)
is
begin


	execute immediate ' MERGE INTO ' || p_table_name || ' b ' ||
	' USING ( ' ||
		' SELECT :myGroupId group_id, :myCol1 col1 from dual ' ||
	' ) e ' ||
	' ON (b.group_id = e.group_id) ' ||
	' WHEN MATCHED THEN ' ||
	' UPDATE SET b.col1 = :myCol1 ' ||
	' WHEN NOT MATCHED THEN ' ||
	' INSERT (b.group_id, b.col1) ' ||
	' VALUES (e.group_id, e.col1) ' using v_S_SI_group_id, p_col1, p_col1
	;

end;
/

show errors

exec myProcMerge(1,'Z','TAB1');
exec myProcMerge(2,'B','TAB1');

select * from tab1;

Open in new window

0
 

Author Comment

by:vadicherla
ID: 35211917
AWESOME
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35212011
Thanks for that!  If you need anything else, just ask.
0
 

Author Comment

by:vadicherla
ID: 35212966
HI

I updated custom procedure with following but some reason its error out at execution

CREATE OR REPLACE procedure myProcMerge1(v_atc_id number,v_SECURE_group_id number,v_SECURE_variable_id number,
V_LAST_MODIFIED_DATE date,v_value  varchar2,encrypt_decrypt_key in varchar2,p_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || p_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id, :my_SECURE_group_id group_id, :my_SECURE_variable_id variable_id,
:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE, from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET  b.atc_id = :my_atc_id, b.group_id = :my_SECURE_group_id,b.VARIABLE_ID = :my_secure_variable_id,
 b.SECURE_VALUE = sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)))

               ,b.last_modified_date =:my_last_modified_date  WHERE b.group_id=v_SECURE_group_id and b.VARIABLE_ID =v_SECURE_variable_id '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.VARIABLE_ID,b.SECURE_VALUE,b.LAST_MODIFIED_DATE) ' ||
      ' VALUES (test_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.VARIABLE_ID,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)),e.LAST_MODIFIED_DATE) ' using v_atc_id,v_SECURE_group_id,v_SECURE_variable_id,
V_LAST_MODIFIED_DATE,v_value,p_table_name;
      end;
/

 exec myProcMerge1(1,2,101,to_date('06/26/2010','MM/DD/YYYY'),'testing','12345678901234567890123456789012','table1');

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "TEST.MYPROCMERGE1", line 5
ORA-06512: at line 1

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35214466
>>:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE, from dual ' ||

Extra comma before FROM:

:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE from dual ' ||
0
 

Author Comment

by:vadicherla
ID: 35215066
thanks.  my bad.  i stuck error yestreday for 2 hours Sorry for so many questions

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "TEST.MYPROCMERGE1", line 5
ORA-06512: at line 1

CREATE OR REPLACE procedure myProcMerge1(v_atc_id number,v_SECURE_group_id number,v_SECURE_variable_id number,
V_LAST_MODIFIED_DATE date,v_value  varchar2,encrypt_decrypt_key in varchar2,p_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || p_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id, :my_SECURE_group_id group_id, :my_SECURE_variable_id variable_id,
:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET  b.atc_id = :my_atc_id, b.group_id = :my_SECURE_group_id,b.VARIABLE_ID = :my_secure_variable_id,
 b.SECURE_VALUE = sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)))

               ,b.last_modified_date =:my_last_modified_date  WHERE b.group_id=v_SECURE_group_id and b.VARIABLE_ID =v_SECURE_variable_id '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.VARIABLE_ID,b.SECURE_VALUE,b.LAST_MODIFIED_DATE) ' ||
      ' VALUES (test_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.VARIABLE_ID,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)),e.LAST_MODIFIED_DATE) ' using v_atc_id,v_SECURE_group_id,v_SECURE_variable_id,
V_LAST_MODIFIED_DATE,v_value,p_table_name;
      end;
/
0
 

Author Comment

by:vadicherla
ID: 35215176
Please see error below .  Not sure if i still miss anything here

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "TEST.MYPROCMERGE1", line 5
ORA-06512: at line 1
0
 
LVL 34

Expert Comment

by:johnsone
ID: 35216026
Not all your variables are converted to bind variables for the statement.

Also, I believe that EXECUTE IMMEDIATE uses bind by position, so you have to repeat the variables and get them all in the right order.

I believe I got most of them here, but please double check.


CREATE OR REPLACE procedure myProcMerge1(v_atc_id number,v_SECURE_group_id number,v_SECURE_variable_id number,
V_LAST_MODIFIED_DATE date,v_value  varchar2,encrypt_decrypt_key in varchar2,p_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || p_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id, :my_SECURE_group_id group_id, :my_SECURE_variable_id variable_id,
:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE, from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET  b.atc_id = :my_atc_id, b.group_id = :my_SECURE_group_id,b.VARIABLE_ID = :my_secure_variable_id,
 b.SECURE_VALUE = sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key)))
               ,b.last_modified_date =:my_last_modified_date  WHERE b.group_id=:v_SECURE_group_id and b.VARIABLE_ID =:v_SECURE_variable_id '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.VARIABLE_ID,b.SECURE_VALUE,b.LAST_MODIFIED_DATE) ' ||
      ' VALUES (test_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.VARIABLE_ID,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key)),e.LAST_MODIFIED_DATE) '
using v_atc_id, v_secure_group_id, v_secure_variable_id, v_last_modified_date, v_atc_id, v_secure_group_id, v_secure_variable_id,
v_value, encrypt_decrypt_key, v_last_modified_date, v_secure_group_id, v_secure_variable_id, v_value, encrypt_decrypt_key;
      end;
/

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35217799
>>I believe that EXECUTE IMMEDIATE uses bind by position

Correct.  I just provide them 'useful' names so I can track them in the USING clause.

I think johnsone got them all.  Thanks for the help.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 35218042
Absolutely.  I use the "useful" names too.  Just helps keep me sane, which is pretty hard some days.
0
 

Author Comment

by:vadicherla
ID: 35218584
Following Procedure working

CREATE OR REPLACE procedure myProcMerg2(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id,:my_SECURE_group_id group_id,:my_SECURE_varible_id variable_id,:my_last_modified_date last_modified_date  from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = :my_SECURE_group_id,b.variable_id = :my_SECURE_varible_id,b.last_modified_date = :my_last_modified_date  '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date) ' ||
      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date)' using             v_atc_id,v_SECURE_group_id,v_SECURE_varible_id,v_last_modified_date,v_SECURE_group_id,
      v_SECURE_varible_id,v_last_modified_date;
      end;
/

exec myProcMerg2(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'test');

but i not below after adding encryption method

CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id,:my_SECURE_group_id group_id,:my_SECURE_varible_id variable_id,:my_last_modified_date last_modified_date from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = :my_SECURE_group_id,b.variable_id = :my_SECURE_varible_id,b.last_modified_date = :my_last_modified_date,b.SECURE_VALUE = sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key))) '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key)))' using             v_atc_id,v_SECURE_group_id,v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key,v_SECURE_group_id,
      v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key;
      end;
/

Please see below error

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1

0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 20 total points
ID: 35218676
At first look, it seem like you have too may )'s.  I see two (, but three ).  I think you have 1 too many.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35218714
I agree.

I modified the proc to do a dbms_output.put_line instead of execute immediate.

This echoed the generated SQL the the screen.  Then it's a simple matter of checking.

try changing:
 UTL_RAW.cast_to_raw(:encrypt_decrypt_key))) '||

to
 UTL_RAW.cast_to_raw(:encrypt_decrypt_key)) '||
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 480 total points
ID: 35218722
should clarify:  On the update side.  Not the insert side
0
 

Author Comment

by:vadicherla
ID: 35218784
CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id,:my_SECURE_group_id group_id,:my_SECURE_varible_id variable_id,:my_last_modified_date last_modified_date from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = :my_SECURE_group_id,b.variable_id = :my_SECURE_varible_id,b.last_modified_date = :my_last_modified_date,b.SECURE_VALUE = sys.DBMS_CRYPTO.encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)) '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key)))' using             v_atc_id,v_SECURE_group_id,v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key,v_SECURE_group_id,
      v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key;
      end;
/


SQL> exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','PROFILE_SMS_SECURE1');
BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','PROFILE_SMS_SECURE1'); END;

*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1
0
 

Author Comment

by:vadicherla
ID: 35219068
Strange  not sure where i missing comma

CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id,:my_SECURE_group_id group_id,:my_SECURE_varible_id variable_id,:my_last_modified_date last_modified_date from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = :my_SECURE_group_id,b.variable_id = :my_SECURE_varible_id,b.last_modified_date = :my_last_modified_date,SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)) '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||

      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key))' using             v_atc_id,v_SECURE_group_id,v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key,v_SECURE_group_id,
      v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key;
      end;
/

BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','PROFILE_SMS_SECURE1'); END;

*
ERROR at line 1:
ORA-00917: missing comma
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1
0
 

Author Comment

by:vadicherla
ID: 35219090
Finally i think we are close. Now i am getting below error

SQL> SQL>  exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','PROFILE_SMS_SECURE1');
BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','PROFILE_SMS_SECURE1'); END;

*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1
0
 

Author Comment

by:vadicherla
ID: 35219162
CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT :my_atc_id atc_id,:my_SECURE_group_id group_id,:my_SECURE_varible_id variable_id,:my_last_modified_date last_modified_date from dual ' ||
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = :my_SECURE_group_id,b.variable_id = :my_SECURE_varible_id,b.last_modified_date = :my_last_modified_date,b.SECURE_VALUE = sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)) '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(:encrypt_decrypt_key)))' using             v_atc_id,v_SECURE_group_id,v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key,v_SECURE_group_id,
      v_SECURE_varible_id,v_last_modified_date,v_value,encrypt_decrypt_key;
      end;
/

SQL> exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012',test1');
BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012',test'); END;

*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35219206
In your last code I count 9 bind variables and 10 variables in the USING clause.

If you really want to end the back and forth guessing, post the table and sample data.

One of us will post 100% working code.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 480 total points
ID: 35219243
I guess my count was off as well.

I created a 'new' version od the procedure.  There is nothing saying you cannot manipulate the strings to logically make more sense on the screen.

I put every bind variable on it's own line.  I added comments to 'count' them.  Then the USING variables on their one line with the same counter.

See if this helps keep everything straight.

CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT ' || 
' :my_atc_id atc_id, ' ||					--1
' :my_SECURE_group_id group_id, ' || 				--2
' :my_SECURE_varible_id variable_id, ' ||			--3
' :my_last_modified_date last_modified_date from dual ' ||	--4
      ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = ' || 
' :my_SECURE_group_id,b.variable_id = || '			--5
' :my_SECURE_varible_id,b.last_modified_date = ' ||		--6
' :my_last_modified_date,b.SECURE_VALUE = sys.DBMS_CRYPTO.' ||	--7
                'encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)) '||
      ' WHEN NOT MATCHED THEN ' ||
      ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
      ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,sys.DBMS_CRYPTO.encrypt( ' ||
                   ' UTL_RAW.cast_to_raw(' || 
' :v_value), ' ||						--7
                   ' 8  + 256 + 4096,
                   UTL_RAW.cast_to_raw( ' ||
' :encrypt_decrypt_key)))'					--9
using
v_atc_id,			--1
v_SECURE_group_id,		--2
v_SECURE_varible_id,		--3
v_last_modified_date,		--4
v_value,			--5
encrypt_decrypt_key,		--6
v_SECURE_group_id,		--7
v_SECURE_varible_id,		--8
v_last_modified_date,		--9
v_value,			--10
encrypt_decrypt_key;		--11
      end;
/

Open in new window

0
 

Author Comment

by:vadicherla
ID: 35219348
See Below. Still getting Same error

Procedure created.

SQL>  exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012',test1');
BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','test1); END;

*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1
0
 

Author Comment

by:vadicherla
ID: 35219355
we  dont have any data yet. i will try to insert using  this procedure
SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SSV_SECURE_ID                                      NUMBER
 ATC_ID                                    NOT NULL NUMBER
 GROUP_ID                                  NOT NULL NUMBER
 VARIABLE_ID                               NOT NULL NUMBER
 LAST_MODIFIED_DATE                                 DATE
0
 

Author Comment

by:vadicherla
ID: 35219596
SQL> DESC TEST1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SSV_SECURE_ID                                      NUMBER
 ATC_ID                                    NOT NULL NUMBER
 GROUP_ID                                  NOT NULL NUMBER
 VARIABLE_ID                               NOT NULL NUMBER
 LAST_MODIFIED_DATE                                 DATE
 SECURE_VALUE                                       VARCHAR2(4000)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35219600
Your test1 table is missing the SECURE_VALUE column.

The 'bad' news is I'm running XE at home and don't have DBMS_CRYPTO installed so I had to set SECURE_VALUE to a varchar2 and remove the ENCRYPT call.

I hope you put that back in.  Just take care to get the syntax right.


Here's the output of the code below:


SQL> @q

Table dropped.


Table created.


Sequence dropped.


Sequence created.


Procedure created.

No errors.

PL/SQL procedure successfully completed.

SQL> select * from test1;

SSV_SECURE_ID     ATC_ID   GROUP_ID VARIABLE_ID
------------- ---------- ---------- -----------
SECURE_VALUE
-----------------------------------------------------

LAST_MODIFIED_DATE
-------------------
            1          1          2         111
testing
02/02/2010 00:00:00


SQL>
drop table test1 purge;
create table test1 (
	SSV_SECURE_ID  NUMBER,
	ATC_ID         NUMBER,
	GROUP_ID       NUMBER,
	VARIABLE_ID    NUMBER,
	SECURE_VALUE	varchar2(100),
	LAST_MODIFIED_DATE DATE
);

drop sequence WEBSITE_IDENTITY_ID_SEQ;
create sequence WEBSITE_IDENTITY_ID_SEQ;


CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
      	' SELECT ' || 
		' :my_atc_id atc_id, ' ||					--1
		' :my_SECURE_group_id group_id, ' || 				--2
		' :my_SECURE_varible_id variable_id, ' ||			--3
		' :my_last_modified_date last_modified_date from dual ' ||	--4
      	' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = ' || 
		' :my_SECURE_group_id,b.variable_id = ' ||			--5
		' :my_SECURE_varible_id,b.last_modified_date = ' ||		--6
		' :my_last_modified_date,b.SECURE_VALUE = ' ||			--7
		' :v_value' ||							--8
      ' WHEN NOT MATCHED THEN ' ||
      		' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
      		' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,' ||
		       ' :v_value) '						--9
using
v_atc_id,			--1
v_SECURE_group_id,		--2
v_SECURE_varible_id,		--3
v_last_modified_date,		--4
v_SECURE_group_id,		--5
v_SECURE_varible_id,		--6
v_last_modified_date,		--7
v_value,			--8
v_value				--9
;
end;
/

show errors

exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012','test1');

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35219605
lol... you caught me typing on your last post...

You do realize ENCRYPT returns a RAW not a VARCHAR2?
0
 

Author Comment

by:vadicherla
ID: 35219926
CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT ' ||
            ' :my_atc_id atc_id, ' ||                              --1
            ' :my_SECURE_group_id group_id, ' ||                         --2
            ' :my_SECURE_varible_id variable_id, ' ||                  --3
            ' :my_last_modified_date last_modified_date from dual ' ||      --4
            ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = ' ||
            ' :my_SECURE_group_id,b.variable_id = ' ||                  --5
            ' :my_SECURE_varible_id,b.last_modified_date = ' ||            --6
            ' :my_last_modified_date,b.SECURE_VALUE = ' ||                  --7
            ' :sys.DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(:v_value),
                   8  + 256 + 4096,UTL_RAW.cast_to_raw(:encrypt_decrypt_key))' ||                                          --8
      ' WHEN NOT MATCHED THEN ' ||
                  ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
                  ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,' ||
                   ' sys.DBMS_CRYPTO.encrypt( ' ||
                   ' UTL_RAW.cast_to_raw(' ||
':v_value), ' ||                                    --7
                   ' 8  + 256 + 4096,
                   UTL_RAW.cast_to_raw( ' ||
':encrypt_decrypt_key))) '                                    --9
using
v_atc_id,                  --1
v_SECURE_group_id,            --2
v_SECURE_varible_id,            --3
v_last_modified_date,            --4
v_SECURE_group_id,            --5
v_SECURE_varible_id,            --6
v_last_modified_date,            --7
v_value,                  --8
v_value                        --9
;
end;
/

SQL> exec myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012',TEST1');

BEGIN myProcMerg3(1,2,111,TO_DATE('02/02/2010 00:00:00','MM/DD/YYYY HH24:MI:SS'),'testing','12345678901234567890123456789012',TEST1'); END;

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "TEST.MYPROCMERG3", line 5
ORA-06512: at line 1

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 480 total points
ID: 35220027
Count the colons.  That error means you have an 'extra' bind variable (word preceded by a colon) that doesn't match a variable in the USING piece.

>>            ' :sys.DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(:v_value),


see the ":sys"?


Also, I added numbered comments that should match 1-to-1 between the bind variables and those in the USING.

You've added more bind variables and didn't add the proper USING variables.  Check out the code below.  Even though I removed ":sys", you are still off.

I show 11 bind variables and only 9 USING variables.

I'm not sure how much more I can help.  We are down to numbers and positions.

For every bind variable, you need a corresponding variable in USING.  

They need to match.

NOTE: the code below does not run.  You need to add two more variables to USING to match the two extra bind variables.

Notice the counters or bind variables total 11.  The variable count is 9.



CREATE OR REPLACE procedure myProcMerg3(v_atc_id number,v_SECURE_group_id number,v_SECURE_varible_id number,v_last_modified_date date,
v_value  varchar2,encrypt_decrypt_key in varchar2,v_table_name in varchar2)
is
begin
      execute immediate ' MERGE INTO ' || v_table_name || ' b ' ||
      ' USING ( ' ||
            ' SELECT ' ||
            ' :my_atc_id atc_id, ' ||                              --1
            ' :my_SECURE_group_id group_id, ' ||                         --2
            ' :my_SECURE_varible_id variable_id, ' ||                  --3
            ' :my_last_modified_date last_modified_date from dual ' ||      --4
            ' ) e ' ||
      ' ON (b.atc_id  = e.atc_id) ' ||
      ' WHEN MATCHED THEN ' ||
      ' UPDATE SET b.group_id = ' ||
            ' :my_SECURE_group_id,b.variable_id = ' ||                  --5
            ' :my_SECURE_varible_id,b.last_modified_date = ' ||            --6
            ' :my_last_modified_date,b.SECURE_VALUE = ' ||                  --7
            ' sys.DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(:v_value), ' || 	--8
                   ' 8  + 256 + 4096,UTL_RAW.cast_to_raw(:encrypt_decrypt_key))' ||                                          --9
      ' WHEN NOT MATCHED THEN ' ||
                  ' INSERT (b.SSV_SECURE_ID,b.atc_id,b.GROUP_ID,b.variable_id,b.last_modified_date,b.SECURE_VALUE) ' ||
                  ' VALUES (WEBSITE_IDENTITY_ID_SEQ.nextval,e.atc_iD,e.GROUP_ID,e.variable_id,e.last_modified_date,' ||
                   ' sys.DBMS_CRYPTO.encrypt( ' ||
                   ' UTL_RAW.cast_to_raw(' ||
':v_value), ' ||                                    --10
                   ' 8  + 256 + 4096,
                   UTL_RAW.cast_to_raw( ' ||
':encrypt_decrypt_key))) '                                    --11
using
v_atc_id,                  --1
v_SECURE_group_id,            --2
v_SECURE_varible_id,            --3
v_last_modified_date,            --4
v_SECURE_group_id,            --5
v_SECURE_varible_id,            --6
v_last_modified_date,            --7
v_value,                  --8
v_value                        --9
;
end;
/

Open in new window

0
 

Author Comment

by:vadicherla
ID: 35220089
Thanks for help.  i was not looking much into it. i will start working on it.

i think we are done here.  i will close this  soon once i make it wo
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 480 total points
ID: 35220099
Close it when you get your answer.

We will stay here until you do.  I'm just not sure how much more I can provide.

If johnsone or another Expert comes by that can run dbms_crypto maybe they can fix the code.

If this still isn't answered before I get back to work and can run crypo, I'll see what I can do.

If you still have minor syntax errors I might be able to help.  Just ask.
0
 

Author Comment

by:vadicherla
ID: 35220100
One last question.  how this stored Procedure perform if i use thia s part of applica.iton. This Procedure called by applicaiton very ofen.  Is this cause any perform issue in Produciton
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35220109
Performance compared to what?

You will need to test old versus new.  If there is no 'old', you will need to monitor and look for problem areas.
0
 

Author Comment

by:vadicherla
ID: 35220244
We have below which is runnign in prod without any issue. i will replace with new procedure so that we can pass table name as input parameter
CREATE OR REPLACE procedure test(v_atc_id number,v_S_SI_group_id number,v_SS_V_ID number,
V_LAST__DATE timestamp,v_value  varchar2,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct atc_id,group_id,VARIABLE_ID from table1 where atc_id=v_atc_id and group_id=v_S_SI_group_id and VARIABLE_ID =v_SS_V_ID )loop
--row exists
update table1 set atc_id = v_atc_id, group_id=v_S_SI_group_id,VARIABLE_ID=v_SS_V_ID,last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE   group_id=v_S_SI_group_id and VARIABLE_ID =v_SS_V_ID;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO table1(Ss_ID,atc_id,GROUP_ID,VARIABLE_ID,LAST_DATE,S_VALUE)
    VALUES (p_id.nextval,v_atc_id,v_S_SI_group_id,v_SS_V_ID,V_LAST_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/
Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35220266
I again don't see the reason for the loop but anyway:

Test/Time the original.  Then test/time the new.

The winner should be obvious.  If not, then the flexibility of the new should win if you have many tables with the exact same structure and business rules.
0
 

Author Comment

by:vadicherla
ID: 35220472
sorry to confuse you and also acting so stupid. i made it work by making some changes to it after seeing the comments by you
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35223966
Never a problem.  I just want to make sure you understand what I've posted.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video teaches viewers about errors in exception handling.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now