Solved

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

Posted on 2004-04-30
8
3,746 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 35

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 35

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

947 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

19 Experts available now in Live!

Get 1:1 Help Now