[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Stored procedure

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
vadicherla
Asked:
vadicherla
  • 21
  • 20
  • 3
5 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
Yes table1 as variable name like p_table_name.

When i execute procedure i will pass table name as input parameter
0
 
slightwv (䄆 Netminder) Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
You need to expand the where clause to include the correct columns based on input parameters.
0
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
AWESOME
0
 
slightwv (䄆 Netminder) Commented:
Thanks for that!  If you need anything else, just ask.
0
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE, from dual ' ||

Extra comma before FROM:

:my_LAST_MODIFIED_DATE LAST_MODIFIED_DATE from dual ' ||
0
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
johnsoneSenior Oracle DBACommented:
Absolutely.  I use the "useful" names too.  Just helps keep me sane, which is pretty hard some days.
0
 
vadicherlaAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
At first look, it seem like you have too may )'s.  I see two (, but three ).  I think you have 1 too many.
0
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
should clarify:  On the update side.  Not the insert side
0
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
lol... you caught me typing on your last post...

You do realize ENCRYPT returns a RAW not a VARCHAR2?
0
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
vadicherlaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Never a problem.  I just want to make sure you understand what I've posted.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 21
  • 20
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now