Solved

Question to sdstuber Stored Procedure from previous qestion

Posted on 2011-09-07
8
407 Views
Last Modified: 2012-06-27
CREATE OR REPLACE procedure test_.GEO_PROCEDURE3(p_point in SDO_POINT_TYPE,v_VENUE_CODE IN varchar2)
AS
v_geom SDO_GEOMETRY;
v_sqlstr VARCHAR2(500);
BEGIN
v_geom := SDO_GEOMETRY(2001,8307,p_point, NULL, NULL);
v_sqlstr := 'update CRAFT_TEST1 set CRAFT_GEO_LOCATION = :1 where CODE = :2';
EXECUTE IMMEDIATE v_sqlstr using v_geom, v_VENUE_CODE;
END GEO_PROCEDURE3;
/

Can we modify the procedure to pass below sql output as inpurt parameter to update that particular record. Below query will part of procedure itself using cursor or something

select code, latlng test from craft_test1. this select statement should be  part of procedure
where test_geo_location is null and latlng is not null;

code             test_geo_location  

ATL110645  (33.9581032, -83.373449)

Thanks
0
Comment
Question by:vadicherla
  • 5
  • 2
8 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36499929
I still don't know what you want this to do.The question text above looks like the same thing you wrote last time.


>>>> Can we modify the procedure to pass below sql output as inpurt parameter

in what sort of context?    Do you want to call your procedure from within a sql statement?  If so,  no.  You could call a function though, but not a procedure.


What do you mean by passing the sql output?

Do you want to pass the entire query in as a parameter?  Do you want to pass in a reference cursor?  An object type?   etc?


why are you trying to use execute immediate?


0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36499934
I think you want to do something like this...
but you'll still have to explain what you mean by "passing the output"


CREATE OR REPLACE PROCEDURE test_.geo_procedure3(
    p_point        IN sdo_point_type,
    v_venue_code   IN VARCHAR2
)
AS
BEGIN
    UPDATE craft_test1
       SET craft_geo_location =
               sdo_geometry(
                   2001,
                   8307,
                   p_point,
                   NULL,
                   NULL
               )
     WHERE code = v_venue_code;
END geo_procedure3;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36499950
I should also note that in the future, calling out an Expert by name means other Experts will likely not respond.
0
 

Author Comment

by:vadicherla
ID: 36500872

From followng sql statement the test_geo_location is not geo column. Some process enters the data into test_geo_location(varchary datatype) using  some perl script.  i have to take output  from below slq and insert into SDO_GEOMEY column which is real goe cloumn using stored procedure.  Thei sql statement should part of procedure

select venue_code, latlng from teamsite_craft_test1 where craft_geo_location is null and latlng is not null'

code             test_geo_location  

ATL110645  (33.9581032, -83.373449)

The output of above query is input for stored procedure to insert to SDO_GEOMETRY column

from test_geo_location (varchar data type)  
to
CRAFT_GEO_LOCATION (GEO Column)

based on code column condition


Thanks
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36501849
you would use the sql results just as you would any other values.
nothing special here



declare
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36501850
you would use the sql results just as you would any other values.
nothing special here



declare
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
0
 

Author Comment

by:vadicherla
ID: 36503267
something like this ??


CREATE OR REPLACE PROCEDURE geo_procedured(v_venue_code IN VARCHAR2,v_point IN sdo_point_type)
AS
begin
     select venue_code, latlng
     into v_venue_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;
      geo_procedure3(v_code,v_point);
end geo_procedured;
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36503321
close
your procedure wouldn't have parameters because you're looking up those values
instead it will have local variables
just use the code above but with a procedure name for it


CREATE OR REPLACE PROCEDURE geo_procedured
as
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 R2 Express report problem 2 83
SQL Union 20 44
Copying MS SQL database does not copy default balues or identity specificatio 3 49
Update from TABLE-A to TABLE-B 5 39
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

910 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

18 Experts available now in Live!

Get 1:1 Help Now