Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need to fetch the table details using stored procedure when we give table name as input

Albert ChaoMay 25 2022
CREATE TABLE test_table (
    col1  NUMBER(10),
    col2  NUMBER(10)
);

INSERT INTO test_table VALUES(1,2);

I am writing one stored procedure wherein if I give a table name as an input, that should give me the table data and column details.
For example : SELECT * FROM <input_table_name>;
But this is giving me the error that the SQL command has not ended properly even though I have taken care of this.
My Attempt:

CREATE OR REPLACE PROCEDURE sp_test(iv_table_name IN VARCHAR2)
AS
lv_count NUMBER(1);
lv_table_name VARCHAR2(255):=UPPER(iv_table_name);
BEGIN
    SELECT COUNT(1) INTO lv_count FROM all_tables WHERE table_name = lv_table_name;
    IF lv_count = 0 THEN
        dbms_output.put_line('Table does not exist');
    ELSE
        EXECUTE IMMEDIATE 'SELECT * FROM '||lv_table_name||';';
    END IF;
END sp_test;

Tool used: SQL developer(18c)
I have also asked this on Stack overflow.
This post has been answered by BluShadow on May 25 2022
Jump to Answer

Comments

Pavan Kumar

Hi,

For the same COL1 and COL2/COL3 , check the select different values from COL4


For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3


Return 20 and 35 as not common values

I didn't get you on this logic, how did you selected 20 and 30, can you elaborate

Update : are you looking for this , just give insert into table  and this query

SQL> with tab1 as
(
select '600' col1, '140' col2, '2' col3, '10' col4,'1300' col5,'500' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual
)
, grp1 as (
select *
from tab1
where col1 = 600
and   col2=140
)
, grp2 as (
select *
from tab1
where col1 = 600
and   col2=150
)
select distinct res.*
from grp1 res , (
select p1.*
from grp1 p1, grp2 p2
where p1.col1 = p2.col1
and p1.col4 = p2.col4
) ex
where res.col1 = ex.col1
and res.col2 = ex.col2
and res.col4 <> ex.col4  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  ;


COL COL C CO COL5 COL C
--- --- - -- ---- --- -
600 140 2 20 1400 340 1
600 140 2 35 1700 120 1

- Pavan Kumar N

Solomon Yakobson

No need for procedure. Use, for example, full outer join:


with t1 as (

            select  *

              from  table1

              where col1 = 600

                and col2 = 140

                and col3 = 2

           ),

     t2 as (

            select  *

              from  table1

              where col1 = 600

                and col2 = 150

                and col3 = 3

           )

select  nvl(t1.col4,t2.col4) col4

  from      t1

        full join

            t2

          on t2.col4 = t1.col4

  where t1.col4 is null

    or t2.col4 is null

/


      COL4
----------
        20
        35

SQL>


SY.

Eight Six

or may be like this?

select col1, col4

from table1

group by col1, col4

having count(distinct col2)=1 and count(distinct col3)=1;

Bufoss

@Pavan Kumar

For the same COL1 and COL2/COL3 , check the select different values from COL4


For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3


Return 20 and 35 as not common values

I mean for source combination 600 , 140, 2  and destination combination 600, 150, 3 check the values of col4.

And bring me the col1, col2 , col3 from destination  col4 from source that does not exist in destination ,  col5 , col6 from souce , and col7 from a sequence.

So, I want to insert on existing table tab1 2 rows :

600 , 150 , 3, 20 , 1400 , 340, 7

600 , 150 , 3, 35 , 1700 , 120, 8

not

600 140 2 20 1400 340 1

600 140 2 35 1700 120 1

-----------------------

@Solomon Yakobson

I need procedure because I will use it in adf (parameters will be bindings)

But this you answered to me I can do it only with minus. I have difficulty to extract all the values I need.

Solomon Yakobson


And what prevents you from useing bind variables in SQL:


with t1 as (

            select  *

              from  table1

              where col1 = :P_COL1

                and col2 = :P_FROM_COL2

                and col3 = :P_FROM_COL3

          ),

    t2 as (

            select  *

              from  table1

              where col1 = :P_COL1

                and col2 = :P_TO_COL2

                and col3 = :P_TO_COL3

          )

select  nvl(t1.col4,t2.col4) col4

  from      t1

        full join

            t2

          on t2.col4 = t1.col4

  where t1.col4 is null

    or t2.col4 is null

/

SY.

P.S. Btw, by looking at your code all you need is MERGE statement.

Bufoss

I don't know how to use merge ..

Is it correct something like the following?

select :P_COL1

         ,:P_FROM_COL2

         ,:P_FROM_COL3

         ,a.col4

         ,a.col5

         ,a.col6

     from :table_name as a

          left outer join :table_name as b

                       on b.col1=a.col1

                      and b.col2=:P_TO_COL2

                      and b.col3=:P_TO_COL3

                      and b.col4=a.col4

    where a.col1=:P_COL1

      and a.col2=:P_from_COL2

      and a.col3=:P_from_col3

      and b.col1 is null;

I think Could I use the code in a function like do_copy returning a temp table and in procedure just write

insert into table1
select * from do_copy(600,140,2,150,3,'table1');


Solomon Yakobson

Sorry, even though MERGE can be used it wouldn't be right tool here. All you need is INSERT. The following replaces your procedure:


SQL> select  *
  2    from  table1
  3  /

      COL1      COL2      COL3      COL4      COL5      COL6      COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      600        140          2        10      1300        500          1
      600        140          2        20      1400        340          4
      600        140          2        15      1400        230          3
      600        140          2        35      1700        120          2
      600        150          3        10      1300        166          6
      600        150          3        15      1400        435          5

6 rows selected.

SQL> insert
  2    into table1
  3    select  :P_COL1,
  4            :P_TO_COL2,
  5            :P_TO_COL3,
  6            col4,
  7            null,
  8            null,
  9            null
10      from  table1
11      where col1 = :P_COL1
12        and col2 = :P_FROM_COL2
13        and col3 = :P_FROM_COL3
14        and :P_FLG1 = 'Y'
15    minus
16      select :P_COL1,
17            :P_TO_COL2,
18            :P_TO_COL3,
19            col4,
20            null,
21            null,
22            null
23      from  table1
24      where col1 = :P_COL1
25        and col2 = :P_TO_COL2
26        and col3 = :P_TO_COL3
27        and :P_FLG1 = 'Y'
28  /

2 rows created.

SQL> select  *
  2    from  table1
  3  /

      COL1      COL2      COL3      COL4      COL5      COL6      COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      600        140          2        10      1300        500          1
      600        140          2        20      1400        340          4
      600        140          2        15      1400        230          3
      600        140          2        35      1700        120          2
      600        150          3        10      1300        166          6
      600        150          3        15      1400        435          5
      600        150          3        20
      600        150          3        35

8 rows selected.

SQL>


SY.

Solomon Yakobson
Answer

And if you want to insert missing COL4 values both ways, use full join, as I already showed (slightly modified):


SQL> insert
  2    into table1
  3    values(600,150,3,70,1500,567,8)
  4  /

1 row created.

SQL> insert
  2    into table1
  3    values(600,150,3,90,1900,789,9)
  4  /

1 row created.

SQL> select  *
  2    from  table1
  3  /

      COL1      COL2      COL3      COL4      COL5      COL6      COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      600        140          2        10      1300        500          1
      600        140          2        20      1400        340          4
      600        140          2        15      1400        230          3
      600        140          2        35      1700        120          2
      600        150          3        10      1300        166          6
      600        150          3        15      1400        435          5
      600        150          3        70      1500        567          8
      600        150          3        90      1900        789          9

8 rows selected.

SQL> insert
  2    into table1
  3    with t1 as (
  4                select  *
  5                  from  table1
  6                  where col1 = :P_COL1
  7                    and col2 = :P_FROM_COL2
  8                    and col3 = :P_FROM_COL3
  9                    and :P_FLG1 = 'Y'
10              ),
11        t2 as (
12                select  *
13                  from  table1
14                  where col1 = :P_COL1
15                    and col2 = :P_TO_COL2
16                  and col3 = :P_TO_COL3
17                    and :P_FLG1 = 'Y'
18              )
19    select  :P_COL1 col1,
20            nvl2(t1.col4,:P_TO_COL2,:P_FROM_COL2) col2,
21            nvl2(t1.col4,:P_TO_COL3,:P_FROM_COL3) col3,
22            nvl(t1.col4,t2.col4) col4,
23            null,
24            null,
25            null
26      from      t1
27            full join
28                t2
29              on t2.col4 = t1.col4
30      where t1.col4 is null
31        or t2.col4 is null
32  /

4 rows created.

SQL> select  *
  2    from  table1
  3  /

      COL1      COL2      COL3      COL4      COL5      COL6      COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      600        140          2        10      1300        500          1
      600        140          2        20      1400        340          4
      600        140          2        15      1400        230          3
      600        140          2        35      1700        120          2
      600        150          3        10      1300        166          6
      600        150          3        15      1400        435          5
      600        150          3        70      1500        567          8
      600        150          3        90      1900        789          9
      600        140          2        70
      600        140          2        90
      600        150          3        35

      COL1      COL2      COL3      COL4      COL5      COL6      COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
      600        150          3        20

12 rows selected.

SQL>

SY.

Marked as Answer by Bufoss · Sep 27 2020
1 person found this helpful
Bufoss

Thanks a lot for your help! It works great!

1 - 9

Post Details

Added on May 25 2022
#oracle, #plsql
4 comments
4,908 views