OraFAQ Forum: SQL & PL/SQL » How to pass in dynamic variable for calling store procedure

Home » SQL & PL/SQL » SQL & PL/SQL » How to pass in dynamic variable for calling store procedure (Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

How to pass in dynamic variable for calling store procedure [message #550504]

Mon, 09 April 2012 23:27

aimei
Messages: 5
Registered: April 2012

Junior Member

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';

-- Store procedure 'sp_compare_val' is used to compare the
-- before and after image of each column
sp_compare_val ( bef_val.NAME, aft_val.NAME);
sp_compare_val ( bef_val.GENDER, aft_val.GENDER);
sp_compare_val ( bef_val.AGE, aft_val.AGE);
END;
/

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.

Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

Thanks Smile

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550505 is a reply to message #550504]

Mon, 09 April 2012 23:28

BlackSwan
Messages: 20106
Registered: January 2009

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550524 is a reply to message #550505]

Tue, 10 April 2012 01:49

aimei
Messages: 5
Registered: April 2012

Junior Member

More details of the program:

CREATE TABLE CUST ( NAME VARCHAR2(50), GENDER VARHCAR2(1), AGE NUMBER(3) );
INSERT INTO CUST VALUES ( 'ABC', 'F', 20 );

CREATE TABLE CUST_TEMP ( NAME VARCHAR2(50), GENDER VARHCAR2(1), AGE NUMBER(3) );
INSERT INTO CUST_TEMP VALUES ( 'ABC', 'M', 21 );

CREATE OR REPLACE PROCEDURE sp_compare_val ( col_nam IN VARCHAR2, bef_val IN VARCHAR2, aft_val IN VARCHAR2, sql_stmt IN OUT VARCHAR2 ) AS
upd_ind VARCHAR2(1);
BEGIN
IF col_nam IN ( 'NAME', 'GENDER' ) THEN
upd_ind := 'N';
ELSE
upd_ind := 'Y';
END IF;

IF upd_ind = 'Y' THEN
IF sql_stmt IS NOT NULL THEN
sql_stmt := sql_stmt ||', ';
END IF;
sql_stmt := sql_stmt || col_nam ||' = '''|| aft_val || '''';
END IF;
END;
/

-- Main Program
set serveroutput on size unlimited
DECLARE
b_val CUST%ROWTYPE;
a_val CUST_TEMP%ROWTYPE;
sql_stmt VARCHAR2(2000);
upd_stmt VARCHAR2(2000);
c NUMBER;
cnt NUMBER;
BEGIN
SELECT * INTO b_val FROM CUST WHERE name = 'ABC';
SELECT * INTO a_val FROM CUST_TEMP WHERE name = 'ABC';

sp_compare_val ( 'NAME', b_val.name, a_val.name, sql_stmt );
sp_compare_val ( 'GENDER', b_val.gender, a_val.gender, sql_stmt );
sp_compare_val ( 'AGE', b_val.age, a_val.age, sql_stmt );

upd_stmt := 'UPDATE CUST SET '||sql_stmt||' WHERE name = ''ABC''';
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE( c, upd_stmt, DBMS_SQL.NATIVE );
cnt := DBMS_SQL.EXECUTE ( c );
DBMS_SQL.CLOSE_CURSOR( c );
END;
/

Thus, any method that can dynamically pass-in the variable name while calling the sp_compare_val.

Thanks.

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550526 is a reply to message #550504]

Tue, 10 April 2012 02:03

flyboy
Messages: 1670
Registered: November 2006

Senior Member

As far as I know, this is not possible. As both *_VAL records are pretty static (as their base table definitions are), I see no problem in statically naming its fields.

If they are too many tables/fields for typing them (yes, you may make error in this process too), you may generate them from metadata, as shown e.g. in this AskTom thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550528 is a reply to message #550526]

Tue, 10 April 2012 02:31

aimei
Messages: 5
Registered: April 2012

Junior Member

Thanks for your help. There already has a table trigger on "CUST" for doing the audit trail (similar as your suggested program).

So, if i want to write a program insert the record into a table like LOOKUP_TAB ( COL_NAM VARCHAR2(100), COL_VALUE VARCHAR2(1000) ),
For example, the expected result on LOOKUP_TAB is

COL_NAM COL_VAL
-------------------------------------------------- -------------------------------------------------------------------------------------------------NAME ABC
GENDER F
AGE 20

is it possible? If yes, do you have any reference for it?

Thanks a lot!

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550529 is a reply to message #550528]

Tue, 10 April 2012 02:52

flyboy
Messages: 1670
Registered: November 2006

Senior Member

Possible? Probably yes, as it looks like result of unpivotting. You may find its description e.g. in these articles:
http://www.orafaq.com/wiki/UNPIVOT
http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php
(or just search for "Oracle UNPIVOT" online)
Performant? No, it will be probably slower than static approach.

What are you really trying to achieve and why?

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550534 is a reply to message #550529]

Tue, 10 April 2012 03:27

aimei
Messages: 5
Registered: April 2012

Junior Member

If possible, i can make the "horizontal" record changing to "vertical". So that, i can do something look the below to solve the issue.

For example:

DECLARE
TYPE typ_col_val IS TABLE OF lookup_tab.col_val%TYPE INDEX BY VARCHAR2(30);
va_col_val typ_col_val;
va_col_new_val typ_col_val;

CURSOR cur_col IS
SELECT a.column_name, b.col_val
FROM all_tab_columns a, lookup_tab b
WHERE a.table_name = 'CUST'
AND a.column_name = b.col_nam;

CURSOR cur_col_new IS
SELECT a.column_name, b.col_val
FROM all_tab_columns a, lookup_tab_new b
WHERE a.table_name = 'CUST'
AND a.column_name = b.col_nam;

CURSOR cur_col2 IS
SELECT a.column_nam
FROM all_tab_columns a
WHERE table_name = 'CUST';
BEGIN
va_col_val.DELETE;
va_col_new_val.DELETE;
FOR val in cur_col LOOP
va_col_val( val.column_name ) := val.col_val;
END LOOP;

FOR val2 IN cur_col_new LOOP
va_col_new_val ( val2.column_name ) := val2.col_val;
END LOOP;

FOR col IN cur_col2 LOOP
sp_compare_value ( col.column_name, va_col_val( col.column_name), va_col_new_val( col.column_name );
END LOOP;
END;
/

Is it work? any comments?

Thanks.

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550540 is a reply to message #550534]

Tue, 10 April 2012 04:20

flyboy
Messages: 1670
Registered: November 2006

Senior Member

aimei wrote on Tue, 10 April 2012 10:27

Is it work?

You surely tested that code, so you should definitely know it, should not you?
aimei wrote on Tue, 10 April 2012 10:27

any comments?

The question is: why? Is Oracle too fast? Is storing data in relational tables too transparent? Some other reason for creating yet-another-metadata-over-the-Oracle-ones table?
You may find some interesting reading about this kind of design in this article on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

Report message to a moderator

Re: How to pass in dynamic variable for calling store procedure [message #550541 is a reply to message #550534]

Tue, 10 April 2012 04:29

Michel Cadot
Messages: 54206
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Previous Topic:	Error Message
Next Topic:	SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 08:44:24 CDT 2013

Total time taken to generate the page: 0.10509 seconds