Solved

A simple question..How to test procedure in SQL plus?

Posted on 2004-04-30
8
3,742 Views
Last Modified: 2012-06-21
I have a procedure  this is the def...

CREATE OR REPLACE  PROCEDURE "SA"."RPTJSREPORT" (
JNum  IN NUMBER  DEFAULT NULL,
HearingType  IN NUMBER  DEFAULT NULL,
HearingDateFrom  IN VARCHAR2  DEFAULT NULL,
HearingDateTo  IN VARCHAR2  DEFAULT NULL,
CaseType  IN VARCHAR2  DEFAULT NULL,
TempTableName  IN OUT VARCHAR2)
AS
....

I want to test this in sql plus. In the procedure it creates a global temp table which preserves the data on commit. After execution I would like to see the table...
I am not sure of the exact syntax.. This is what I want to do..

declare variables
begin
execute the procedure with parameters...
Show the table as select ( name  from output variable TempTableName)
End

I may not be able to test any solution right away as server is unavailable...
But It should be a simple task for Oracle users.. let alone Experts!

Thanks in advance
Shailesh


0
Comment
Question by:Shailesh15
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10960963
See if this will get you started:

create or replace procedure junk (myvar in out varchar2) as
begin
      myvar := 'Hello';
end;
/

show errors

var junk varchar2(100)

exec junk(:junk);

print junk
0
 
LVL 10

Author Comment

by:Shailesh15
ID: 10961044
I am looking for solution specific to my procedure... (It's the reason why I posted procedure def & 500 pts!)

May be pl sql block which I can use as 'cut & paste'.

Thanks for your reply though...;-)
0
 

Expert Comment

by:go2guy
ID: 10961238
Keep in mind that for a global temporary table, even if ON COMMIT PRESERVE ROWS is set, that those rows are only visible during the SAME session; the rows go away if the session terminates.  Also, unless the global temporary table is dropped, it would result in an error to try to recreate a same-named table in a future session.

That said, it appears that the table name is dynamic, which means that you could use NDS (native dynamic sql) to execute any SQL against it.  The syntax for that would be similar to:

DECLARE
     vTableName   VARCHAR2(30);
     vCol1             VARCHAR2(30);
BEGIN
     vTableName := 'MyTable';
     DBMS_OUTPUT.PUT_LINE('Using table_name ' || vTableName);
     EXECUTE IMMEDIATE 'SELECT col1 FROM ' || vTableName || ' where pk_col = 123' INTO vCol1;
     DBMS_OUTPUT.PUT_LINE('vCol1='|| vCol1);
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('key pk_col 123 was not found');
     WHEN OTHERS THEN
          RAISE;
END;

HTH:)
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10961282
Why is the procedure CREATING the global temporary table?  The correct way to use GTTs is to create them ONCE like any other table - it is the DATA that is temporary and private to your session, not the table definition.

create global temporary table gtt (...);

CREATE OR REPLACE  PROCEDURE "SA"."RPTJSREPORT" (
JNum  IN NUMBER  DEFAULT NULL,
HearingType  IN NUMBER  DEFAULT NULL,
HearingDateFrom  IN VARCHAR2  DEFAULT NULL,
HearingDateTo  IN VARCHAR2  DEFAULT NULL,
CaseType  IN VARCHAR2  DEFAULT NULL)
AS
....

begin
  RPTJSREPORT( 1, 2, 'A', 'B', 'C' );
End
/

select * from gtt;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 10961362
Here is what you suggested:
1. declare variables
2. begin
3. execute the procedure with parameters...
4. Show the table as select ( name  from output variable TempTableName)

I would suggest a slightly different way to do that that is much simpler in SQL*Plus:
(This does require changing your procedure to remove the "out" parameter.)
1. execute the procedure with parameters you provide when you call it.
2. Show the table as select * from [global temp table]

Here are the two commands you need in SQL*Plus:
exec SA.RPTJSREPORT (&jnum,&HearingType,'&HearingDateFrom','&HearingDateTo','&CaseType');
select *from [global temp table name];

Just provide the values for each parameter when prompted.

Do you need the "out" parameter for an application?  It is possible for PL\SQL procedures to return an array or recordset like that, but that is not the default (nor the easiest) way to do things in PL\SQL.  That looks a SQL Server style approach to coding PL\SQL.
0
 
LVL 6

Accepted Solution

by:
musdu earned 500 total points
ID: 10962424
Hi,

in sqlplus;

var tmptable varchar2(100);

begin
:tmptable:='my_temp_table';
RPTJSREPORT(TempTableName => :tmptable);
end;
/

select * from my_temp_table;


regards
0
 
LVL 10

Author Comment

by:Shailesh15
ID: 10962574
Well as some of you have already noted,  It is a SQL database converted to Oracle. So I would like to keep the procedure definition similar to SQL SP.

@go2guy,
           I was looking for something like that... Is it possible for you to customize to my procedure. (Bit busy with other issues right now!).

@andrewst,
            Since it is a migrated SQL database , I guess I have to keep the def as SQL SP.Thank you for the suggestion.

@markgeer,
             In the procedure temptable is created & also the name is generated Which is sent in output parameter. This table name I have to bind to SQL to see the results. (This is for testing purpose only)


Thanks for all the comments.

Looks like @musdu's post may solve my problem ( as I told you I am looking for a copy paste solution).  I will give it a try later in the evening.

Thanks again.



 
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 10962734
Its your database, so its your decision, but be aware that the best (or easiest) way to get things done in SQL Server is *NOT* always the same as the best (or easiest) way to get things done in Oracle.  Also some parts of the system (especially those  involving null values or manual record locking) *WILL NOT* work in Oracle as they do in SQL Server!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now