Solved

Question to sdstuber Stored Procedure from previous qestion

Posted on 2011-09-07
8
403 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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
postgres queries -- need opinions 1 36
how to fix this error 14 45
SQL Retrieve Values 4 40
MySQL left join performance 4 3
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 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

13 Experts available now in Live!

Get 1:1 Help Now