Home » SQL & PL/SQL » SQL & PL/SQL » How to pass in dynamic variable for calling store procedure (Oracle 11g)
How to pass in dynamic variable for calling store procedure [message #550504] Mon, 09 April 2012 23:27 Go to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 22781
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/
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
flyboy
Messages: 1770
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
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 Go to previous messageGo to next message
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!
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 Go to previous messageGo to next message
flyboy
Messages: 1770
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?
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
flyboy
Messages: 1770
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
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 Go to previous message
Michel Cadot
Messages: 59087
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
Previous Topic: Error Message
Next Topic: SQL Query
Goto Forum:
  


Current Time: Tue Sep 16 13:28:15 CDT 2014

Total time taken to generate the page: 0.25518 seconds