Home » SQL & PL/SQL » SQL & PL/SQL » Checking values of procedure's parameters
Checking values of procedure's parameters [message #575105] Sat, 19 January 2013 08:49 Go to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

[Split from Logic for procedure by LF]



Hi All,

CREATE TABLE t2
  (
    id     NUMBER,
    ename2 VARCHAR2(20),
    sal2   NUMBER,
    job2   VARCHAR2(20),
    conid  NUMBER
  );
INSERT INTO t2
  (1,'MILLER',5000,'MANAGER',10
  );
INSERT INTO t2
  (2,'JOHN',3000,'SALESMAN',20
  );


CREATE TABLE t3
  (conid NUMBER,verify VARCHAR2(30)
  );
INSERT INTO t3
  (10,'ename2,sal2'
  );
INSERT INTO t3
  (20,'job2'
  );

CREATE OR REPLACE type split_tbl
AS
  TABLE OF VARCHAR2
  (
    32767
  )
  ;
CREATE OR REPLACE
FUNCTION split
  (
    p_list VARCHAR2,
    p_del  VARCHAR2 := ','
  )
  RETURN split_tbl pipelined
IS
  l_idx pls_integer;
  l_list     VARCHAR2(32767) := p_list;
  AA l_value VARCHAR2(32767);
BEGIN
  LOOP
    l_idx   := instr(l_list,p_del);
    IF l_idx > 0 THEN
      pipe row(SUBSTR(l_list,1,l_idx-1));
      l_list := SUBSTR(l_list,l_idx +LENGTH(p_del));
    ELSE
      pipe row(l_list);
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END split;

CREATE OR REPLACE
PROCEDURE p1
  (
    p_id    NUMBER,
    p_ename VARCHAR2,
    p_sal   NUMBER,
    p_job   VARCHAR2
  )
IS
  DECLARE
TYPE array_tab IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
    l_param VARCHAR2(50):= 'ENAME2,'|| 'SAL2,'||'JOB2';
    l_value VARCHAR2(50):= p_ename||','|| p_sal||','||p_job;
    param_tab array_tab ;
    val_tab array_tab ;
    col_tab array_tab ;
    c1 sys_refcursor;
    l_valuer VARCHAR2(10);
    l_verify VARCHAR2(10);
  BEGIN
    param_tab:=split(l_param,',');
    val_tab  :=split(l_value,',');
    select verify into l_verify from t3 ,t2 where t2.conid=t3.conid and t2.id=p_id;
    col_tab:=splIt(l_verify,',');
    FOR i IN col_tab.first .. col_tab.last
    LOOP
      open c1 for 'select '||col_tab(i)||' from t2 where id='||p_id;
      fetch c1 into l_valuer;
      close c1;
      FOR j IN param_tab.first .. param_tab.last
      LOOP
        IF param_tab (j)=col_tab(i)
          THEN
          IF l_valuer=val_tab(j)
            THEN
            dbms_output.put_line('success');
          END IF;
        END IF;
      END LOOP;
    END LOOP;
    NULL;
  END;



My requirement is like when I am calling the procedure P1 with some values
then it should check the table "t2".And table "t2" is linking with table "t3".
So what ever the column "verify" is there, it should check the incoming values against it.
If matches success otherwise reject it.Later the incoming values is stored different tables.

I am doing it in the above way by hard coding some value.So is there any other solution??
  BEGIN
    p1(1,'MILLER',500,'ADMIN');    --REJECT 
    p1(1,'MILLER',5000,'ADMIN');   --ACCEPT
    P1(2,'MILLER',5000,'SALESMAN');--ACCEPT
  END;


Regards,
Nathan

[Updated on: Sun, 20 January 2013 12:46] by Moderator

Report message to a moderator

Re: logic for procedure [message #575107 is a reply to message #575105] Sat, 19 January 2013 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 21964
Registered: January 2009
Senior Member
what relationship does most recent code have to do with original "audit" requirement?

>So is there any other solution??
where is "REJECT" results?
Re: logic for procedure [message #575130 is a reply to message #575107] Sat, 19 January 2013 21:01 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi blackswan

Sorry there is no relation to the original post.
Accept means it should print success. Otherwise it should not print.

Regards.
nathan
Re: logic for procedure [message #575131 is a reply to message #575130] Sat, 19 January 2013 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 21964
Registered: January 2009
Senior Member
It appears to be a nice homework problem that you have solved.
Congratulations!
Re: logic for procedure [message #575177 is a reply to message #575131] Mon, 21 January 2013 00:01 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi BlackSwan ,

It's not homework.
It is possible in some other way because these values are going for storing in a tables(t4,t5). We can compare COLUMNS of t2 with t4 and t5 dynamically.But the problem is how to generate the 'and condition dynamically'.

like
"T2.ENAME2=T4.ENAME4 AND " "T2.JOB2=T4.JOB4 AND" "T2.SAL2=T5.SAL".

because columns are different .If columns are alike the it could be possible like
"T2.ENAME2=T4.ENAME2 AND " "T2.JOB2=T4.JOB2 AND" "T2.SAL2=T5.SAL2".

Is any other way to solve it or Is any problem Because I have literally done a hard code there.By specifying
  l_param VARCHAR2(50):= 'ENAME2,'|| 'SAL2,'||'JOB2';
  l_value VARCHAR2(50):= p_ename||','|| p_sal||','||p_job;



Regards,
Nathan
Re: logic for procedure [message #575178 is a reply to message #575177] Mon, 21 January 2013 00:12 Go to previous message
BlackSwan
Messages: 21964
Registered: January 2009
Senior Member
DYNAMIC SQL is an alternative that exist; but does not scale.

what is your business problem that needs t be solved?
Previous Topic: How to tune query?
Next Topic: Gloabal Temporary Table
Goto Forum:
  


Current Time: Wed Apr 23 04:00:25 CDT 2014

Total time taken to generate the page: 0.13889 seconds