Skip to Main Content

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!

Retrieving data before insert statement in procedure

BufossJul 19 2015 — edited Jul 20 2015

Hi,
I am new in plsql programming and I would like to make a procedure.I have tables like the following TABLE1

|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 |

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


Then insert in this table TABLE1 the rows
600 , 150 , 3, 20 , 1400 , 340, 7
600 , 150 , 3, 35 , 1700 , 120, 8


I am trying to make the procedure like below but I have problem how to retrieve data in insert statement

PROCEDURE COPY_COLUMNS  ( P_COL1        IN  A.COL1%TYPE,

                          P_FROM_COL2   IN  B.COL2%TYPE,

                          P_FROM_COL3   IN  B.COL3%TYPE,

                          P_TO_COL2     IN  B.COL2%TYPE,

                          P_TO_COL3     IN  B.COL3%TYPE,

                          P_FLG1        IN  VARCHAR2,

                          P_FLG2        IN  VARCHAR2,

                          P_FLG3        IN  VARCHAR2                                     

                                     ) IS

CURSOR CFL1 IS select COL4

    FROM TABLE1

    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3

    MINUS

    select COL4

    FROM TABLE1

    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;

CURSOR CFL2 IS select COL4

    FROM TABLE2

    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3

    MINUS

    select COL4

    FROM TABLE2

    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;

CURSOR CFL3 IS select COL4

    FROM TABLE3

    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3

    MINUS

    select COL4

    FROM TABLE3

    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3; 

V_REC        CFL1%ROWTYPE;

BEGIN

IF P_FLG1='N' OR P_FLG2='N' OR P_FLG3='N' THEN

    GOTO label; --do nothing

END IF;

IF P_FLG1 = 'Y' THEN

    OPEN CFL1;

    FETCH CFL1 INTO V_REC;

    CLOSE C1;

--    SELECT COL5, COL6

--    FROM TABLE1

--    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 AND COL4 = V_REC.COL4;

    FOR REC IN CFL1 LOOP

        INSERT INTO TABLE1

            SELECT P_COL1, P_TO_COL2, P_TO_COL3, CFL1.COL4, -- COL5 ?? , COL6 ?? -- , SEQname2.NEXTVAL)

    END LOOP;

END IF;

<<label>>

END;

Could you help me please do it ?
Thanks in advance

This post has been answered by Solomon Yakobson on Jul 19 2015
Jump to Answer

Comments

Satyaki_De
It is working perfectly in my laptop.

Did you exit from your SQL prompt or How are you checking that?

Regards.

Satyaki De.
Lakmal Rajapakse
Are you connecting as CLASIE and then logging off.
Hoek
What roles are granted to the user?
DBA role perhaps?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1844531724208
Lakmal Rajapakse
Martijn,

The DBA role does not prevent the trigger been fired. So I think this is not the cause of the issue...

rgds
Lakmal...
Hoek
Hi Lakmal,

Yes, you're right, thanks for pointing out, I was a bit too hasty with my reply.
I ran some tests (which I should have done in the first place before posting ).
SQL>  create table loginfo(msg varchar2(1000));

Table created.

SQL> create or replace trigger test
  2  before logoff on hoek.schema
  3  begin
  4  insert into loginfo values( user||' logged off @ '||to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss'));
  5  end;
  6  /

Trigger created.

SQL> disc
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn hoek/hoek@xe
Verbonden.
SQL> select * from loginfo;

MSG
--------------------------------------------------------------------------------------------------------------------------
HOEK logged off @ 03-03-2010 21:37:27

1 row selected.

SQL> disc
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn hr/hr@xe
Verbonden.
SQL> disc
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn hoek/hoek@xe
Verbonden.
SQL> select * from loginfo;

MSG
--------------------------------------------------------------------------------------------------------------------------
HOEK logged off @ 03-03-2010 21:37:27
HOEK logged off @ 03-03-2010 21:37:57

2 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn /@xe as sysdba
Verbonden.
SQL> select * from hoek.loginfo;

MSG
--------------------------------------------------------------------------------------------------------------------------
HOEK logged off @ 03-03-2010 21:37:27
HOEK logged off @ 03-03-2010 21:37:57
HOEK logged off @ 03-03-2010 21:41:17

3 rows selected.

SQL> conn hoek/hoek@xe
Verbonden.
SQL> select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
PLUSTRACE
XDBADMIN
XDBWEBSERVICES

12 rows selected.
Having DBA role doesn't matter.
MichaelS
when I logoff either from the terminal and from the APEX's web igu admin
which terminal?

How is APEXs web gui disconnecting?

I assume no regular disconnect in either case is made so no insert into the target table has taken place.

But starting and ending a regular sql*plus session should verify that the trigger is indeed working as expected.
741904
Hello,

With 'terminal' I meant pl*sql terminal.

In order to be sure I'm well disconnected I have made a new trigger but now on the event on 'logon'. It works fine:

CREATE OR REPLACE TRIGGER Trig7174
AFTER LOGON ON CLASIE.SCHEMA
CALL p7174

where p7174 is a procedure inserting a value into a table:

CREATE OR REPLACE PROCEDURE p7174 IS
BEGIN
INSERT INTO TEST1 VALUES('p7174');
END;

Then if I can logon I suppose it's only possible if I've been previously logoff.

So to be clear, I connect through pl*sql and I disconnect from it by the 'disconnect' command.

Thank for all suggestions.
Regards.
Claude

Edited by: claude_belgique on Mar 3, 2010 2:33 PM
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 17 2015
Added on Jul 19 2015
9 comments
1,179 views